hi friends,
Sometimes i am not able to find the exact reason why a particular user has access to a particular object. There are so many roles granted to a user that i get confused in reaching the correct role through the schema browser of TOAD which enables that user to do a select or any other operation on a particular operation on given object.
Can you please guide me in writing a simple query which will tell me why a particular user has a particular access to a particular object?
I tried to use the below script but no success in getting to my goal:
SELECT DBMS_METADATA.GET_DDL('USER', '&&a')|| '/' DDL FROM DBA_USERS WHERE ROWNUM <2
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&a' )|| '/' DDL FROM DBA_USERS where rownum <2
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&a') || '/' DDL FROM DBA_USERS where rownum <2
I am using oracle 11.1.0.7.0.
regds,
Kunwar