How to Show All Privileges from a User in Oracle

How to show all privileges from a user in oracle?

You can try these below views.

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

How to find the privileges and roles granted to a user in Oracle?

Look at http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665

Check USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables with these select statements

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS; 
SELECT * FROM USER_ROLE_PRIVS;

How to show all system privileges for all users in Oracle database

"Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server."

https://chartio.com/resources/tutorials/oracle-user-privileges--how-to-show-all-privileges-for-a-user/

How can I list ALL grants a user received?

If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries:

System privileges for a user:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = <theUser>
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY 1;

Direct grants to tables/views:

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM table_privileges
 WHERE grantee = <theUser>
 ORDER BY owner, table_name;

Indirect grants to tables/views:

SELECT DISTINCT owner, table_name, PRIVILEGE 
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY owner, table_name;

How to list all users with SELECT ANY TABLE permission in Oracle?

SELECT ANY TABLE is a system privilege. So to answer your question you need to query the static data dictionary view DBA_SYS_PRIVS.
You'll need to be a DBA or power user to query this view.

select grantee
from dba_sys_privs
where privilege = 'SELECT ANY TABLE';

Grant all privs of a user to another user in Oracle

Get all privileges from user A

 SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','A') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','A') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','A') FROM DUAL;

Change the DDL commands with the user 'B' and execute.

How to see what privileges are granted to schema of another user

You can use these queries:

select * from all_tab_privs;
select * from dba_sys_privs;
select * from dba_role_privs;

Each of these tables have a grantee column, you can filter on that in the where criteria:

where grantee = 'A'

To query privileges on objects (e.g. tables) in other schema I propose first of all all_tab_privs, it also has a table_schema column.

If you are logged in with the same user whose privileges you want to query, you can use user_tab_privs, user_sys_privs, user_role_privs. They can be queried by a normal non-dba user.

Oracle: How to give all privileges of one user to another?

You can try something like this:

Get all privileges from AAA

 SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AAA') FROM DUAL;

Change the DDL commands with the user 'BBB' and execute.

Or

expdp userid=system directory=DATA_PUMP_DIR dumpfile=AAA.dmp schemas=AAA

impdp userid=system directory=DATA_PUMP_DIR dumpfile=AAA.dmp 

---

Related Topics