Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Find Out What Privileges a User Currently Has?

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:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
USERNAME                       PRIVILEGE
------------------------------ ----------------------
DEV                            SELECT ANY TABLE
DEV                            INSERT ANY TABLE
DEV                            CREATE SESSION
DEV                            CREATE VIEW
DEV                            DELETE ANY TABLE
DEV                            CREATE ANY TABLE

SQL> disconnect
SQL> connect SYSTEM/fyicenter

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

SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS 
  WHERE GRANTEE = 'HR';
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

Looks like "hr" has move privileges than "dev".

Part: 1 2 3 4 5 6

More...