Recent Posts

Thursday, 30 June 2016

Data Control Language(DCL) Tutorial


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

Next Tutorial  SQL Sub Queries

Previous Tutorial  Constraints Tutorial

No comments:

Post a Comment