Determining a users's roles and what privileges are in a role

[george_hamilton]

I am trying to determine for audit purposes what users have certain roles assigned to them. I have found the roles (and I think what privileges they have DBA_PRIV_ROLES and DBA_ROLES).

I can't find anywhere how to determine what roles have been to a user. None of my Oracle books or google searches provide any info (they tell me how to create a role, assign a role to a user, and remove a role from a user) but not how to determine what roles a user has. Any help is greatly appreciated!!!!

[Former Member · Accepted solution]

Hi George,

I think if you 'select grantee, granted_role from dba_role_privs' with either a 'where = <user>' or not should get you the roles that have been granted to a users and who granted the access.

Or there is a 'role_role_privs' in Oracle 10 - which gives you the roles that have been granted to other roles.

But remember a user can be granted access to a table in two ways - via a direct privilege on the table (dba_tab_privs or role_tab_privs) or via a role (dba_role_privs or role_role_privs).

Good luck,

Sheryl.

[george_hamilton · Accepted solution]

What's strange is that SAP has new role SAPCONN. But is seems to be treated as a user tool. I guess I can write a query using DBA_ROLES, DBA_PRIV_ROLES, and DBA_SYS_PRIVS. Does that sound right to you?

[fidel_vales · Accepted solution]

Hi,

> What's strange is that SAP has new role SAPCONN

No, it is not strange.

Oracle 10g R2 has limited the CONNECT and RESOURCE roles.

If "nothing" is done when you upgrade your SAP installation to 10g you will be getting ORA-1031 errors.

To avoid that, SAP has replaced those two roles by another one made "in house" the SAPCONN.

This is explained on the SAP note 834917.

In this note you can also find the queries you are looking for to find out the user roles, privileges, role definition and so on.