DCL STATEMENTS

EX NO: 9                   DATA CONTROL LANGUAGE STATEMENTS


PRIVILEGES:
SYSTEM PRIVILEGES
OBJECT PRIVILEGES

SYSTEM PRIVILEGES:
                 
                  Given by DBA and user has access to database. More than 80 privileges are available in the DBA and has a high level system privileges.
Create new users
Remote users
Remote tables

 OBJECT PRIVILEGES:
                   Owner of object has all object privileges on system object in schema (select, Insert, Delete, Update)

Creating User by DBA:

Create user <user> identified by <password>

Create user Scott identified by tiger

USER SYSTEM PRIVILEGES:

Once a user is created, the DBA can grant specific system privileges to a user
Create table
Create sequence
Create new
Create procedure

    Grant privilege [, privilege...] to user [, user];

GRANTING SYSTEM PRIVILEGES:

 Grant create table, create sequence, create view to Scott;

Role
A Role is a named group of related privileges. (group of system privileges with a labeled name) that can be granted to user.
Create role and grant role

Several users can be assigned the same role.

Creating Roles:

SQL> create role manager;

SQL> Grant create table, Create new to manager;

SQL>Grant manager to BLAKE, CLERK;

Changing your password:

User can change their password by using alter use statement

SQL> alter user Scott identified by <new pwd>

Object Privilege:
Set of options that can be performed on schema objects like tables,
sequence, views etc…

Owner has all privilege on object and owner can give specific all privileges on that object to other users

OBJECT PRIVILEGES:

Grant Object-priv [column] on object to {User/role, Public}[with GRANT option]

SQL> Grant Select, insert on department to scott, rich.;
     Grant Succeeded.

SQL>Grant update (dname, dloc) on department to scott, manager;

GRANT KEYWORDS

Give a user authority to pass along the privilege

SQL> Grant select, insert on department to Scott with grant option;

SQL>Grant select on Alice. department to public.

   An owner of a table can grant access by using public keyword.

REVOKE OBJECT PRIVILEGES:
                 
You use the revoke statement to revoke privileges granted to other users[IT includes with Grant option]
Revoke {Privileges t, privileges}/All} on object from {user/role/public}[cascade. constraint];

SQL>revoke select, insert on department from scott;

          Revoke Succeeded.