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

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user.

i also granted sysdba and sysoper roles to the same users.

Now i want to display all the privileges and roles granted to the user. I found following query but it shows only create session and select dictionary privileges.

select privilege 
from dba_sys_privs 
where grantee='SAMPLE' 
order by 1;

please help to resolve the issue.

Thanks

This question is related to oracle security oracle10g user-accounts

The answer is


select * 
from ROLE_TAB_PRIVS 
where role in (
    select granted_role
    from dba_role_privs 
    where granted_role in ('ROLE1','ROLE2')
)

always make SQL re-usuable: -:)

-- ===================================================
-- &role_name will be "enter value for 'role_name'".
-- Date:  2015 NOV 11.

-- sample code:   define role_name=&role_name
-- sample code:   where role like '%&&role_name%'
-- ===================================================


define role_name=&role_name

select * from ROLE_ROLE_PRIVS where ROLE = '&&role_name';
select * from ROLE_SYS_PRIVS  where ROLE = '&&role_name';


select role, privilege,count(*)
 from ROLE_TAB_PRIVS
where ROLE = '&&role_name'
group by role, privilege
order by role, privilege asc
;

IF privileges are given to a user through some roles, then below SQL can be used

select * from ROLE_ROLE_PRIVS where ROLE = 'ROLE_NAME';
select * from ROLE_TAB_PRIVS  where ROLE = 'ROLE_NAME';
select * from ROLE_SYS_PRIVS  where ROLE = 'ROLE_NAME';

SELECT * 
FROM DBA_ROLE_PRIVS 
WHERE UPPER(GRANTEE) LIKE '%XYZ%';

None of the other answers worked for me so I wrote my own solution:

As of Oracle 11g.

Replace USER with the desired username

Granted Roles:

SELECT * 
  FROM DBA_ROLE_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted Directly To User:

SELECT * 
  FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted to Role Granted to User:

SELECT * 
  FROM DBA_TAB_PRIVS  
 WHERE GRANTEE IN (SELECT granted_role 
                     FROM DBA_ROLE_PRIVS 
                    WHERE GRANTEE = 'USER');

Granted System Privileges:

SELECT * 
  FROM DBA_SYS_PRIVS 
 WHERE GRANTEE = 'USER';

If you want to lookup for the user you are currently connected as, you can replace DBA in the table name with USER and remove the WHERE clause.


The only visible result I was able to understand was first to connect with the user I wanted to get the rights, then with the following query:

SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM USER_TAB_PRIVS;

In addition to VAV's answer, The first one was most useful in my environment

select * from USER_ROLE_PRIVS where USERNAME='SAMPLE';
select * from USER_TAB_PRIVS where Grantee = 'SAMPLE';
select * from USER_SYS_PRIVS where USERNAME = 'SAMPLE';

Combining the earlier suggestions to determine your personal permissions (ie 'USER' permissions), then use this:

-- your permissions
select * from USER_ROLE_PRIVS where USERNAME= USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;

-- granted role permissions
select * from ROLE_ROLE_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);
select * from ROLE_TAB_PRIVS  where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);
select * from ROLE_SYS_PRIVS  where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to security

Monitoring the Full Disclosure mailinglist Two Page Login with Spring Security 3.2.x How to prevent a browser from storing passwords JWT authentication for ASP.NET Web API How to use a client certificate to authenticate and authorize in a Web API Disable-web-security in Chrome 48+ When you use 'badidea' or 'thisisunsafe' to bypass a Chrome certificate/HSTS error, does it only apply for the current site? How does Content Security Policy (CSP) work? How to prevent Screen Capture in Android Default SecurityProtocol in .NET 4.5

Examples related to oracle10g

Query to display all tablespaces in a database and datafiles How to insert date values into table Why do I get PLS-00302: component must be declared when it exists? ORA-28000: the account is locked error getting frequently Oracle Trigger ORA-04098: trigger is invalid and failed re-validation Inserting Image Into BLOB Oracle 10g Error System.Data.OracleClient requires Oracle client software version 8.1.7 or greater when installs setup Different CURRENT_TIMESTAMP and SYSDATE in oracle Search for a particular string in Oracle clob column ORA-00984: column not allowed here

Examples related to user-accounts

PHP and MySQL Select a Single Value How to find the privileges and roles granted to a user in Oracle? How do I completely remove root password What are all the user accounts for IIS/ASP.NET and how do they differ? powershell - list local users and their groups Start / Stop a Windows Service from a non-Administrator user account How do you run CMD.exe under the Local System Account?