Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show Privileges of the Current User

By: FYIcenter.com

(Continued from previous topic...)

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".

  1. What Is a User Account?
  2. What Is the Relation of a User Account and a Schema?
  3. What Is a User Role?
  4. What Are the System Predefined User Roles?
  5. What Are Internal User Account?
  6. How To Connect to the Server with User Account: SYS?
  7. How To Use Windows User to Connect to the Server?
  8. How To List All User Accounts?
  9. How To Create a New User Account?
  10. How To Change User Password?
  11. How To Delete a User Account?
  12. What Privilege Is Needed for a User to Connect to Oracle Server?
  13. How To Grant CREATE SESSION Privilege to a User?
  14. How To Revoke CREATE SESSION Privilege from a User?
  15. How To Lock and Unlock a User Account?
  16. What Privilege Is Needed for a User to Create Tables?
  17. How To Assign a Tablespace to a Users?
  18. What Privilege Is Needed for a User to Create Views?
  19. What Privilege Is Needed for a User to Create Indexes?
  20. What Privilege Is Needed for a User to Query Tables in Another Schema?
  21. What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
  22. What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
  23. How To Find Out What Privileges a User Currently Has?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...