A role is created to ease 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|
Creating and Assigning a Role –
First, the (Database Administrator)DBA must create the role. Then the DBA can assign privileges to the role and users to the role.
CREATE ROLE manager; Role created.
In the syntax:
‘manager’ is the name of the role to be created.
- Now that the role is created, the DBA can use the GRANT statement to assign users to the role as well as assign privileges to the role.
- It’s easier to GRANT or REVOKE privileges to the users through a role rather than assigning a privilege directly to every user.
- If a role is identified by a password, then GRANT or REVOKE privileges have to be identified by the password.
Grant privileges to a role –
GRANT create table, create view TO manager; Grant succeeded.
Grant a role to users
GRANT manager TO SAM, STARK; Grant succeeded.
Revoke privilege from a Role :
REVOKE create table FROM manager;
Drop a Role :
DROP ROLE manager;
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 role ‘manager’ using Revoke.The role is dropped from the database using drop.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Database Roles in CQL (Cassandra Query Language)
- Granting Permissions to Roles in Cassandra
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- PHP | MySQL ( Creating Database )
- PHP | MySQL ( Creating Table )
- MySQL | Creating stored function
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL | SELECT Query
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | AND and OR operators
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement
- SQL | SELECT TOP Clause
- SQL | ORDER BY
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : sunny94