Saturday, November 10, 2012

Data Control Language (DCL)

Data Control Language (DCL):
Data Control Language is used to manage user access to an Oracle database.This is the fifth sub language in SQL.Data Control Language Statements are used to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.Which contains two commands.

                       1) Grant
                       2) Revoke

Syntax to create new user:
Syntax   : create user <user_name> identified by <password>;
Example: create user javanib identified by javanib;

GRANT : This command is used for granting the privileges and roles to normal user.

Privilege : Privilege is a simple command such as create table, create any table, alter table, alter any table..etc
Role: It is a collection of privileges. Roles are classified into two types.
                  1)Predefined roles
                  2)Userdefined roles

Predefined roles:
          A role which is already created or constructed along with the software those roles can be called as predefined roles.

Example: connect, resource, all, dba

Syntax: Grant priv1,priv2,rol1,rol2,……..,privn, rolen to <user_name>;

Example: grant connect to javanib;
Example: grant all on scott.emp to javanib;
Example: grant select on scott.emp to javanib;

REVOKE : This command is used to take back all the permissions from a normal user.
Syntax   : Revoke priv1,priv2,rol1,rol2,……..,privn, rolen from <user_name> ;
Example: revoke all on scott.emp from javanib;

User defined Roles:
          A role which is created by user manually those roles can be called as user defined roles.

Syntax to create user defined roles:
Syntax: create role <role_name>;
Example: create role show_data;

Storing privileges inside the role:
SQL> grant select on scott.emp to show_data; 

SQL> create role modify_data;
SQL> grant update on scott.emp to modify_data;

SQL> grant update on scott.dept to modify_data;
SQL> grant update on scott.mytab to modify_data;

SQL> create role store_data;
SQL> grant insert on scott.emp to store_data;

SQL> grant show_data,modify_data,store_data to javanib;

Syntax to merging the Roles:

Syntax: Grant role1,role2,…..,rolen-1 to rolen;

              SQL> grant show_data,modify_data to store_data;
              SQL> grant store_data to javanib;

  • Roles not cyclic. We can merge the roles from top to bottom. It is not possible to merge the roles from bottom to top.
Syntax to see the list of Users:
SQL> select * from dba_users;
SQL> select uid from dual;

Syntax to change the password:
SQL> password

Syntax to lock the user account:
SQL>alter user <user_name> account lock/unlock;
EX: alter user javanib account lock;

Syntax to drop the role:
SQL>drop role <role_name>;
Ex: drop role show_data;

Syntax to see the list of privileges:
SQL>select * from session_privs;

Syntax to drop the User :
Syntax: drop user <user_name> cascade;
SQL> drop user javanib cascade;

  • We can’t drop the user javanib without cascade because there is some privileges and roles are having to javanib account. If there is no privileges and roles in javanib account then we can drop the user without using cascade. So, cascade is optional.

No comments:

Post a Comment

back to top