How To Find Out What Privileges a User Currently Has in Oracle?

Submitted by: Administrator
Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:

>.insqlplus /nolog
SQL> CONNECT DEV/developer

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
<pre>USERNAME PRIVILEGE
------------------------------ ----------------------
DEV SELECT ANY TABLE
DEV INSERT ANY TABLE
DEV CREATE SESSION
DEV CREATE VIEW
DEV DELETE ANY TABLE
DEV CREATE ANY TABLE</pre>
SQL> disconnect
SQL> connect SYSTEM/globalguideline

Submitted by: Administrator

SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.

SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'HR';
<pre>GRANTEE PRIVILEGE
------------------------------ -----------------------
HR CREATE VIEW
HR UNLIMITED TABLESPACE
HR DEBUG CONNECT SESSION
HR CREATE DATABASE LINK
HR CREATE SEQUENCE
HR CREATE SESSION
HR DEBUG ANY PROCEDURE
HR ALTER SESSION
HR CREATE SYNONYM</pre>
Looks like "hr" has move privileges than "dev".
Submitted by: Administrator

Read Online Oracle Database Job Interview Questions And Answers