| |
|
Create a Role in SQL and assign to user with syntax Revoke Drop Grant?
Question Posted on 23 Apr 2022
Home >> DataBase >> SQL Query >> Create a Role in SQL and assign to user with syntax Revoke Drop Grant? |
Create a Role in SQL and assign to user with syntax Revoke Drop Grant?
Basically role is created in SQL to ease setup and maintenance of security Model. To grant user some role we will create a related privileges and create a group. But if we have multiple database then it is becomes very difficult to grant or revoke some privileges to users. There are some important points about roles:-
(1)We can grant or revoke privileges to users and we can automatcially grant or revoke privileges.
(2)We can either create roles or we can use the system roles which are pre-defined.
Below are the some of privileges granted to the system roles which are given below:-
System Roles:-(1)Connect:-
Privileges granted to the Role:-
Create table,Create view,Create synonym,Create sequence,create session etc.
System Roles:-(2)Resource:-
Privileges granted to the Role:-
Create Procedure, Create Sequence, Create Table, Create Trigger etc.
Primary usage of Resoucr role is to restrict access to database objects.
System Roles:-(3)DBA:-
Privileges granted to the Role:-
All system privileges
Create and Assign Role:-
In first step to create tole DBA must create role. Then later DBA can assign privileges to role and then users to role.
Syntax:-
Create ROLE TestRoleManager;
Role created.
in above sysntax "TestRoleManager" is the name of role to be create.
(i)Once we have created the role now DBA can use GRANT statement to assign users to role as well as assign privileges to the role.
(ii)We can easily GRANT or REVOKE privileges to users with the help of roles rather then assign privilege directly to every user.
(iii)If we have set password to role to identify then GRANT or REVOKE privileges have to be identified by the password.
Grant privileges to a role(TestRoleManager):-
GRANT create table, create view
To TestRoleManager;
Grant succeeded.
Grant role to users:–
GRANT TestRoleManager TO USER1, USER2;
Grant succeeded.
Revoke privilege from a Role:-
REVOKE create table FROM TestRoleManager;
Drop a Role:-
DROP ROLE TestRoleManager;
In above code we have creates a role TestRoleManager and then allow TestRoleManager to create tables and views. After the grants USER1 and USER2 the role TestRoleManager. Now USER1 and USER2 can create tables and views. And if multiple roles assigned to users then they receive all the privileges associated with all of roles. After that we remove create table privilege from role TestRoleManager using revoke. And at last role is dropped from database using drop. | |
|
|
|
|