Pages

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..etcRole: It is a collection of privileges. Roles are classified into two types. 1)Predefined roles 2)Userdefined rolesPredefined 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, dbaSyntax: 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;

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.