0

I'm looking for a query listing all the users that have access to a specific database. Hopefully similar to this one but to query Oracle database. SQL Server query to find all permissions/access for all users in a database

Luc M
  • 16,630
  • 26
  • 74
  • 89
Wendy
  • 640
  • 1
  • 4
  • 8

2 Answers2

1

A database administrator (DBA) for Oracle can simply execute a query to view the rows in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to retrieve information about user privileges related to the system, tables, and roles, respectively.

For example, a DBA wishing to view all system privileges granted to all users would issue the following query:

SELECT *
  FROM DBA_SYS_PRIVS;

To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view:

SELECT *
  FROM DBA_TAB_PRIVS;

Finally, querying the DBA_ROLE_PRIVS view has much of the same information but applicable to roles instead, where the GRANTED_ROLE column specifies the role in question:

SELECT *
  FROM DBA_ROLE_PRIVS;

See the entire tutorial here.

Diego Souza
  • 548
  • 5
  • 10
1

To query which user has been granted for a system privilege such as DEBUG ANY PROCEDURE, ON COMMIT REFRESH, INSERT ANY TABLE, CREATE ANY JOB, FLASHBACK ANY TABLE ... etc. 

select p.* from dba_sys_privs p where p.grantee = upper('<your_user_name>');

To query DML, EXECUTION, DEBUG privileges on an object such as TABLE, VIEW, PROCEDURE, FUNCTION ... etc.

select p.* from dba_tab_privs p where p.grantee = upper('<your_user_name>');

To see which user granted the object privileges

select p.* from dba_tab_privs p where p.owner = upper('<your_user_name>');

may be used.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55