• 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;


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback