• Arun Kumar

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;


287 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis