Drop a Role
DROP ROLE manager;
Explanation
Firstly it creates a manager role and then allows managers to create tables and views. It then grants Sam and Stark the role of managers. Now Sam and Stark can create tables and views. If users have multiple roles granted to them, they receive all of the privileges associated with all of the roles. Then create table privilege is removed from the role ‘manager’ using Revoke. The role is dropped from the database using drop.
SQL | Creating Roles
A role is created to ease the setup and maintenance of the security model. It is a named group of related privileges that can be granted to the user. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if you define roles:
- You can grant or revoke privileges to users, thereby automatically granting or revoking privileges.
- You can either create Roles or use the system roles pre-defined.
Some of the privileges granted to the system roles are as given below:
System Roles | Privileges Granted to the Role |
---|---|
Connect | Create table, Create view, Create synonym, Create sequence, Create session, etc. |
Resource | Create Procedure, Create Sequence, Create Table, Create Trigger etc. The primary usage of the Resource role is to restrict access to database objects. |
DBA | All system privileges |