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;