DCL Statements

Introduction

• DCL stands for Data Control Language.
• DCL statements are crucial for managing database security by controlling who has access to what data and what operations they can perform.

Definition

• DCL statements are a collection of those SQL statements that are used to control security and concurrent access to the stored (table) data from the user in a database i.e. it functions like as authorization. 
• In Database Management Systems (DBMS), Data Control Language (DCL) statements are used to control access to data in a database. 

Characteristics

• These statements allow or restrict the user from accessing data in the database.
• These statements are mainly applied to DDL and DML statements.
• These statements especially the use of GRANT and REVOKE help ensure that only authorized users can perform specific actions on the database, thereby protecting sensitive data and maintaining data integrity.

Example

• DCL primarily consists of – REVOKE, GRANT, DENY, etc. commands/statements.
• GRANT 
  • The Grant command is used to give users access privileges to the users or specified groups or databases to do SELECT, INSERT, UPDATE, and DELETE operations on a specific table or multiple tables of a database.
  • The GRANT statement is used to assign privileges to database users. The privileges can be applied to various database objects like tables, views, procedures, etc.
  • Syntax:

GRANT privilege_type ON object_name TO {user | role}

• REVOKE
  • The REVOKE command is used to take back or return a privilege/grant permission(default) from the users or specified groups.
  • In other words, the REVOKE statement is used to remove privileges from database users.
  • Syntax:

REVOKE privilege_type ON object_name FROM {user | role}

• DENY
  • Deny command bans certain permissions from groups/users.

Example : How do you give Administrator power to a newly created or another User in Oracle 10g/11g?

SQL> grant all privileges to system1;(Press Enter)
Grant succeeded.

NB: system1 is the user name.
----------- OR ----------

SQL> grant dba to system1;(Press Enter)
Grant succeeded.

----------- OR ----------

SQL> grant connect, resource, dba to system1;(Press Enter)
Grant succeeded.

----------- OR ----------

SQL> grant resource,create session,create table,create view,create any synonym,create procedure,
create sequence to system1;(Press Enter)
Grant succeeded.

----------- OR ----------

SQL>GRANT SELECT ON Emp TO read_only_user;
(Granting Read-Only Access to a Emp Table)

NB: Here, system1 is user name.

To create new user-

To create a new user go to this link

Example : How to give selective permission to a newly created or another User in Oracle 10g/11g.

- Grant SELECT,INSERT,UPDATE,DELETE on Emp To System1;(Press Enter)

- Grant SELECT on Emp To System1;(Press Enter)


NB:
Here, we gave privileges to a new user System1 to do SELECT, INSERT, UPDATE and DELETE operation on the Emp table.

Example : How to take back or withdraw Insert operation power only from a User on a specific table in Oracle 10g/11g.

- Revoke INSERT On Emp from System1;(Press Enter)

- REVOKE SELECT, INSERT, UPDATE ON Emp FROM System1;(Press Enter)

- REVOKE ALL PRIVILEGES ON employees FROM john;

Example : How to Ban or Stop providing Update privileges or power or permission to a User(System1) on a specific table(Emp) in Oracle 10g/11g.

Deny Update On Emp to System1;(Press Enter)