Topic: privs vs roles
My dear, again I to you with nonsense, but itself in any way something not in those temporal frames that to me are led out (type, there were hours ten tasks on the decision, but not , it was necessary to show result yesterday, and at me only yes ...)
More shortly, the task on audit, it is necessary in one bottle (well that is ) to show users who have two privileges: "CREATE DATABASE LINK" or "RESTRICTED SESSION". These two privileges can be or as direct grant or through a role. It is known that there are roles which both these privileges can give: DBA role, IMP_FULL_DATABASE, RECOVERY_CATALOG_OWNER. But is also custom roles, created by users whom these privileges too can...
At me in writing of such request that it produced all together: and the privilege and that wrote whence it came: through the direct grant (grant RESTRICTED SESSION TO spongebob;) or through a role, and if yes through a role, through which? Tried here so:
select dsp.grantee, dsp.privilege, u.account_status from DBA_SYS_PRIVS dsp, dba_users u where dsp. PRIVILEGE IN (' RESTRICTED SESSION ', ' CREATE DATABASE LINK ') and dsp.grantee = u.username and dsp.grantee not in (' SYS') SELECT drp.grantee, drp.granted_role, drp.admin_option, drp.default_role, u.account_status FROM dba_role_privs drp, dba_users u WHERE drp.grantee IN (SELECT username FROM dba_users) and granted_role in (' DBA ', ' IMP_FULL_DATABASE ') AND grantee NOT LIKE ' %SYS %' AND grantee NOT IN (' DBSNMP ', ' OUTLN ') and drp.grantee=u.username ORDER BY grantee;
But as them in one digestible to connect - at me brains do not suffice. How many did not try join' to do - it produces roles even when directly . Or on the contrary, does not show users by whom the role is appropriated, here look:
create role masha_test_role1; role MASHA_TEST_ROLE1 created. grant RESTRICTED SESSION to masha_test_role1; grant succeeded. grant CREATE DATABASE LINK to masha_test_role1; grant succeeded. create user spongebob identified by patric; user SPONGEBOB created. grant masha_test_role1 to spongebob; grant succeeded. select dsp.grantee, dsp.privilege, u.account_status from DBA_SYS_PRIVS dsp, dba_users u where dsp. PRIVILEGE IN (' RESTRICTED SESSION ', ' CREATE DATABASE LINK ') and dsp.grantee = u.username and dsp.grantee not in (' SYS') BANINST1 RESTRICTED SESSION OPEN DBEU_OWNER CREATE DATABASE LINK OPEN DBEU_OWNER RESTRICTED SESSION OPEN MODSMGR RESTRICTED SESSION OPEN SCTCVT CREATE DATABASE LINK OPEN SCTCVT RESTRICTED SESSION OPEN STREAMSAD RESTRICTED SESSION OPEN UPGRADE1 RESTRICTED SESSION OPEN select username from dba_users where username like (' SPONGE % ') SPONGEBOB
Why does not show in spite of the fact that to it the role with these is appropriated?
I SQL did not write years ten, please, do not beat , and prompt how to make? I even am assured that two separate query too approach, but I will not understand how to catch those users, which custom roles which these too can appropriate.....