privileges

[ashley75]

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

[pando]

do you have synonyms for this view... :-?

[svk]

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.

[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

[anujdutt]

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.

[ashley75]

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

[pando]

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

[svk]

If it is still a problem, can you post the error code/message that you are getting ?

[mike73]

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.