DCL
• The data control language commands are related to the security of database. They
perform tasks of assigning privileges, so users can access certain objects in the
database. This section deals with DCL commands.
How to Create a User
• Once connected as SYSTEM, simply issue the CREATE USER command to generate a new account.
• CREATE USER books_admin IDENTIFIED BY MyPassword;
• Here we’re simply creating a books_admin account that is IDENTIFIED or authenticated by the
specified password.
authenticated by the specified password.
GRANT Command
The objects created by one user are not accessible by another user unless the
owner of those objects gives such permissions to other users. These permissions
can be given by using the GRANT statement. One user can grant permission to
another user if he is the owner of the object or has the permission to grant access
to other users. The grant statement provides various types of access to database
objects such as tables, views and sequences.
Syntax :GRANT {object privilages}ON object name To user name[with GRANT
OPTION]
Object privilages
Each object privilege that is granted authorizes the grantee to perform some operations on
the object. The user can grant all the privileges or grant only specific object privileges. The
list of object privileges is as follows :
• Alter- allows the grantee to change the table definition with the ALTER TABLE command.
• Delete - allows the grantee to remove the records from the table with the DELETE
command.
• Index -allows the grantee to create an index on table with the CREATE INDEX command.
• Insert - allows the grantee to add records to the table with the INSERT command.
• Select - allows the grantee to query the tables with SELECT command.
• Update - allows the grantee to modify the records in tables with UPDATE command.
With grant option : It allows the grantee to grant object privileges to other users
Example 1 :
Grant all privileges on student table to user Pradeep.
GRANT ALL ON student
To Pradeep;
Example 2 :
Grant select and update privileges on student table to mita
GRANT SELECT, UPDATE ON student
To Mita;
Example 3 : Grant all privileges on student table to user Sachin
with grant option.
GRANT ALLON student
To Sachin WITH GRANT OPTION;
REVOKE Command
The REVOKE statement is used to deny the grant given on an
object.
Syntax :
REVOKE {object privileges}
ON object name
FROM user name;
The list of object privileges is :
Alter- allows the grantee to change the table definition with the ALTER TABLE
command.
Delete - allows the grantee to remove the records from the table with the
DELETE command.
Index -allows the grantee to create an index on table with the CREATE INDEX
command.
Insert - allows the grantee to add records to the table with the INSERT
command.
Select - allows the grantee to query the tables with SELECT command.
Update - allows the grantee to modify the records in tables with UPDATE
command
You cannot use REVOKE command to perform following
operations :
[Link] the object privilages that you didn't grant to the
revokee.
[Link] the object privilages granted through the operating
system.
Example 1 : Revoke Delete privilege on student table from
Pradeep.
REVOKE DELETE
ON student
From Pradeep;
Example 2 : Revoke the remaining privileges on student that
were granted to Pradeep.
Revoke ALL
ON student
FROM Pradeep