User Roles in Oracle

When you create a new user, you must at least assign CREATE SESSIONS privilege so the user can connect to the database.

SQL> grant create session to usr1;

When you work in real-time, there are more than one permission which must be assigned to a user. Sometimes the list might be very big. For example, there is a manager who must be able to perform:

  • Insert into EMP & DEPT table
  • Update DEPT table
  • Delete from BONUS table

Instead of giving above privileges to the user one by one, we can create a role inside the database. We then assign all privileges to the role and then assign the role to a user. It makes your life easy!

Create new role

Use below command to create new role inside the database:

SQL> CREATE ROLE SALES_MANAGER;

Grant privileges to role

Assign all the privileges to the role NOT THE USER

SQL> GRANT INSERT ON SCOTT.EMP TO SALES_MANAGER;
SQL> GRANT INSERT ON SCOTT.DEPT TO SALES_MANAGER;
SQL> GRANT UPDATE ON SCOTT.DEPT TO SALES_MANAGER;
SQL> GRANT DELETE ON SCOTT.BONUS TO SALES_MANAGER;

Grant role to a user

Now that you have assigned all the necessary privileges to a role, its time to assign the role to a user:

SQL> GRANT SALES_MANAGER TO USR1;

Was this article helpful?

Related Articles

Leave a Comment