1

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.....
Please, prompt?

2

Re: privs vs roles

marguren;

marguren wrote:

why does not show  in spite of the fact that to it the role with these  is appropriated

Because the privilege produced through a role belongs to a role.

select username, priv, s
from
(
select u.username, sp.privilege priv, p.granted_role s
from dba_users u
inner join dba_role_privs p on u.username = p.grantee
inner join dba_sys_privs sp on p.granted_role = sp.grantee
where u.username = user
union all
select u.username, sp.privilege priv, ' DIRECT PRIV ' s
from dba_users u
inner join dba_sys_privs sp on u.username = sp.grantee
where u.username = user
)
order by username, s, priv;