Why does oracle DBA_SYS_PRIVS have more grantees than are in USER_SYS_PRIVS and ROLE_SYS_PRIVS?

user_2305614 0
Viewed 315

So I was trying to figure out whether a grantee in DBA_SYS_PRIVS was a user or a role.

When I search for it in DBA_SYS_PRIVS, it's there:

select grantee, privilege from DBA_SYS_PRIVS
where grantee = 'GRANTEE1'

Which gives me:

 GRANTEE|PRIVILEGE
 -----------------
GRANTEE1|CREATE SESSION
GRANTEE1|UNLIMITED TABLESPACE

So then I wanted to know whether GRANTEE1 was a user or a role, so I did these two queries:

select * from USER_SYS_PRIVS
where username like 'GRANTEE1'

and

select * from ROLE_SYS_PRIVS
where role like 'GRANTEE1'

and neither of them showed any results.

So then I did a comparison of the counts, and found that there are many grantees in DBA_SYS_PRIVS that are not in USER_SYS_PRIVS or ROLE_SYS_PRIVS?

select count(username) from (select distinct username from user_sys_privs)

Result: 1

select count(role) from (select distinct role from role_sys_privs)

Result: 9

select count(grantee) from (select distinct grantee from dba_sys_privs)

Result: 61

9 + 1 does not equal 61...so what are all these other grantees with privileges in DBA_SYS_PRIVS, and are they users, roles, or something else?

oracle sql database
1 Answers

user_sys_privs does not contain system privileges for all users; only the current one. You'd have to log into each user and execute that query to get the total for that user. The sum of all those counts should equal 61.

And don't forget privileges granted to PUBLIC.

Then can I assume that ROLE_SYS_PRIVS does contain all roles? And therefore any grantee in DBA_SYS_PRIVS that is not in ROLE_SYS_PRIVS is a user?

That depends on your definition of "all roles". If it means all roles which the current user has access, then yes, it contains all roles. If not, then, again, you have to log into each user and query that view.

do DBA_USERS and DBA_ROLES contain all (as in ALL) users and roles? Can I use those to determine what is a user and what is a role in DBA_SYS_PRIVS?

Yes, they are defined as containing all. But, again, don't forget about public.

are you saying that PUBLIC is not shown in DBA_ROLES? I thought PUBLIC was the default role that all users are part of?

It's not in DBA_USERS and it won't show up in DBA_ROLES. Run a simple test to prove it one way or the other. You'll find zero rows where DBA_USERS.USERNAME = 'PUBLIC' or DBA_ROLES.ROLE = 'PUBLIC', even if you grant something to PUBLIC.

user_17073530
Related