Data Control Language(DCL)

Data Control Language:

• DCL is used to configure and control database objects. 
• Data Control Language is used to control the kind of a data access to the database and transaction control.
• This language contains two Commands.

             i) Grant.

            ii) Revoke.

i) Grant:

• The GRAnt command is used to allow another schema access to a privilege.
• GRANT command can be issued not only on TABLE OBJECT, but also on VIEWS, SYNONYMS, SEQUENCES Etc..
• The users are GRANTED at time all four(i.e., INSERT,UPDATE,SELECT) by using ALL.

Syntax:

grant <privilege1,[privilege2....] on <object name> to <user1>[user2...| role,PUBLIC...];

Creating Users:

            Login as

            User               :  System

            Password      :  manager

Syntax:

Create user <user name> Identified by <Password>;

Grant connect,resource to <User Name>;

Ex:

SQL>create user netlojava identified by netlojava;

SQL>grant connect,resource to netlojava;

Login as System:

SQL>grant all on emp to netlojava;

SQL>grant select,insert,delete on dept to netlojava;

Grant Columns:

SQL>grant insert(empno,ename,sal,deptno) on emp to netlojava;

Note:

Only select,insert on update permissions are allowed.

SQL>grant select on student to NLO1,NLO2;

SQL>grant delete on employee to public;

Public:

Public keyword represents all users in database. All users can delete on employee table.

Note:

We cannot give permission at time on more than one data base objet.

Steps to be Performed:

·        connect to the required user using the USER Name and Password.

·        Execute the required SQL statement using the object hierarchy.

Login as netlojava:

SQL>select * from system.emp;

SQL>insert into System.emp(empno,sal,deptno) values(1002,2000,10);

SQL>select * from system.student;

Note:

At time DML,DRL OPERATION we have to mention owners name.

ii) REVOKE:

• It used to remove the access allowed GRANT.
• REVOKE privileges is assigned not only on TABLE OBJECT, But also on VIEWS,SYNONYMS,SEQUENCES Etc..

Syntax:

Revoke <privilege1 [,privilege2...]> on <object name> FROM <user1>[,user | role, PUBLIC...];

Ex:

SQL>revoke select on student from NLO1,NLO2;

SQL>revoke all on emp from netlojava;

Types of Privileges:

      i) System Privileges:

They allow a user to perform certain action within the Database.

ii) Object Privileges:

An object privilege allow a user to perform certain actions on database Objects.

Checking the object privileges Granted:

The schema object that stores the information about the privileges granted is 'USER_TAB_PRIVS_MADE'.

The columns of 'USER_TAB_PRIVS_MADE':

SQL>desc USER_TAB_PRIVS_MADE;

SQL>select GRANTEE,TABLE_NAME,GRANTOR,PRIVILEGE from USER_TAB_PRIVS_MADE

            where TABLE_NAME='EMP';

·        The GRANT command is use to assign Object Privileges to users or group of users