Today we are going to have look at Role in Oracle database. It is the most important part of Oracle database. User Privileges and Roles is the most common task that is performed by Oracle DBA. With roles and privileges, we can easily point out which user has what responsibilities in databases.

 

 

Let’s start with roles in Oracle.

Creating a Role:

create role OracleHelp;

Assign Privilege to a role:

grant create session,create table to OracleHelp;

Assign More Privilege to the role:

SQL> Create table test(id number);

SQL> grant select,insert,update on test to OracleHelp;

Add Another Layer To The Heirarchy:

SQL> CREATE ROLE manager;

SQL> GRANT OracleHelp TO manager;

SQL> GRANT DELETE ON test TO manager;

Assigning Role to user:

GRANT OracleHelp TO scott;

GRANT manager TO Allen;

Granting System Priviledge:

GRANT CREATE SESSION to managee WITH ADMIN OPTION;

Revoke Role From a User:

REVOKE manager FROM Tallen;

Drop a Role:

DROP ROLE manager;

Obtaining Role Information:

  • DBA__ROLES
  • DBA_ROLES_PRIVS
  • ROLE_ROL_PRIVS
  • DBA_SYS_PRIVS
  • ROLE_SYS_PRIVS
  • ROLE_TAB_PRIVS
  • SESSION_ROLES

Thanks for giving valuable time to add new gems to Oracle’s treasure.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.