Data Control Language

Data Control Language (DCL)

This is the fourth sub language in SQL contains two commands

  1. Grant
  2. Revoke.

Syntax 

CREATE USER<User name> IDENTIFIED BY <Password>
Grant

This command is used to giving the privileges and roles to a normal user.

Privilege

It can be defined as permission or a simple command such as Create table, create any table, alter table, alter any table.

Role

It can be defined as collection of privileges or permissions. It can be classified into two types

  1. System defined roles
  2. User defined roles
1. System defined roles

A role which is already constructed along with any software those roles can be called as system defined roles.

E.g

Connect, resource, dba

2. User defined roles

A role which is defined by user manually those roles can be called as user defined roles.

E.g

display_data

SQL> show user
USER is "SCOTT"

SQL> connect as sysdba
Enter user-name: sysdba
Enter password:
Connected.

SQL> show user
USER is "SYS"

SQL> create user ashok identified by kumar ;
User created.
Grant

grant role1, role2, privilliege1, privilliege2,——–,grant n, privilege n to <user name>

E.g

grant connect to ashok;
grant resource to ashok;

To give permission to access other user data

grant select on scott.emp to ashok;
rant update, delete,insert on scott.emp to ashok;
grant all on scott.emp to ashok;
Revoke

This command is used to take back the permissions from a normal user.

E.g

revoke all on scott.emp from ashok;
Grant privileges create INDEX, VIEW, and SYNONYM:

Grant create any index to SCOTT;
Grant succeeded.

Grant create any view to SCOTT;
Grant succeeded.

grant create materialized view to scott;
Grant Create any Synonym to scott;
Grant succeeded.

Note

Here any means we can create particular database object on any user account.

Syntax for password change
Changing password for Ashok
Old password: ashok
New Password: Ashok143

Syntax to see the list of privileges
Select * from session_privs;

To see the users
Select * from dba_users

To show user_id
Select * from dual;

To lock account
Alter user Ashok account lock/unlock;

Syntax to drop user account
Drop user username cascade;
Drop user Ashok

Note

If user account contains no data we need not to cascade otherwise cascade.

Data Control Language
Scroll to top