DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: privileges

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    please help me to understand the problem here.

    CREATE OR REPLACE VIEW MDSYS.USER_SDO_GEOM_METADATA
    (
    TABLE_NAME,
    COLUMN_NAME,
    DIMINFO,
    SRID
    )
    AS
    SELECT SDO_TABLE_NAME TABLE_NAME,
    SDO_COLUMN_NAME COLUMN_NAME,
    SDO_DIMINFO DIMINFO,
    SDO_SRID SRID
    FROM SDO_GEOM_METADATA_TABLE,
    (select username from user_users)
    WHERE sdo_owner = username
    /
    GRANT DELETE ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC
    /
    GRANT INSERT ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC
    /
    GRANT SELECT ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC
    /
    GRANT UPDATE ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC
    /


    I have three users with DBA privileges. one of them can access the data in this view but the others two are not.

    Can someone explain to me what was the problem here ???
    and what I should do to make the other two users access the data on this view.


    Thanks


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you have synonyms for this view... :-?

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    The one which is getting proper access must be having a synonym SDO_GEOM_METADATA_TABLE created for MDSYS.SDO_GEOM_METADATA_TABLE. Others do not.

    If you change the FROM clause of you view def from "FROM SDO_GEOM_METADATA_TABLE" to "FROM MDSYS.SDO_GEOM_METADATA_TABLE", it should work for others as well.
    svk

  4. #4
    Join Date
    Jan 2002
    Posts
    474
    pando

    Thanks for your reply.

    I did have sunonyms for MDSYS.USER_SDO_GEOM_METADATA as USER_SDO_GEOM_METADATA and this synonym is granted to public as well.

    Please advise



  5. #5
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    If the user who is having access is MDSYS itself then it can be concluded that since he is owner so he can access the object.
    An elephant is a mouse with an operating system.

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    SKV,

    I can access to the table SDO_GEOM_METADATA with and without the MDSYS, but I really need to access to the USER_SDO_GEOM_METEDATA.

    please advise


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by ashley75
    pando

    Thanks for your reply.

    I did have sunonyms for MDSYS.USER_SDO_GEOM_METADATA as USER_SDO_GEOM_METADATA and this synonym is granted to public as well.

    Please advise


    hey that doesnt mean everyone can select it without putting user.view_name as long as your synonym is not created as PUBLIC synonym

  8. #8
    Join Date
    Jul 2000
    Posts
    521
    If it is still a problem, can you post the error code/message that you are getting ?
    svk

  9. #9
    Join Date
    Aug 2001
    Posts
    390
    Obvously, you are running Oracle Spatial, MDSYS.USER_SDO_GEOM_METADATA is the view, MDSYS in Oracle SPatial is like SYS and SYSTEM so you should not touch this schema at all. Look at the view below created by MDSYS.

    SELECT SDO_TABLE_NAME TABLE_NAME,
    SDO_COLUMN_NAME COLUMN_NAME,
    SDO_DIMINFO DIMINFO,
    SDO_SRID SRID
    FROM SDO_GEOM_METADATA_TABLE,
    (select username from user_users)
    WHERE sdo_owner = username


    basically, noone can see the metadata except the SDO_OWNER(owner), nothing wrong with this at all.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width