DCL Statements

• DCL stands for Data Control Language.
• DCL statements are collection of those sql statements which 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 another words, it allows or restricts the user from accessing data in the database.
• These statement are mainly applied to DDL and DML statements.
• DCL commands are – REVOKE, GRANT, DENY etc.
• GRANT command : 
  • Grant command is used to give privileges to the  users or specified groups to do SELECT, INSERT, UPDATE and DELETE operations on a specific table or multiple tables of a database.
• DENY command :
  • Deny command bans certain permissions from groups/users.
• REVOKE command :
  • The REVOKE command is used to take back a privilege/permission(default) from the  users or specified groups.

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

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

----------- 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.


NB:
 
(Here, system1 is user name)

To create new user-

To create 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)


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 specific table in Oracle 10g/11g.

Revoke INSERT On Emp To System1;(Press Enter)

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

Deny Update On Emp to System1;(Press Enter)