Search This Blog

Friday, September 7, 2012

Grant

Grant is part of Data Control Language (DCL) that allows us to grant privileges to other users.

We have two types of Grant privileges:

  1. Granting permissions to users.
  2. Granting privileges on database objects to the users.

To demonstrate DCL lets create users first.

Creating user-
 
SQL>Create user user1 identified by user1;
 
--User created
 
SQL> Create user user2 identified by user2;
 
--User created

Granting permissions to users:-

For every new user created in the database, we need to explicitly provide connection and resource privileges to the user so that user can connect and create objects in the database.

Syntax:- 
 
Grant connect, resource to ;

Let’s grant the Connect and resource privileges to the users created:-

SQL>Grant Connect, resource to user1;
 
-- Grant succeeded
 
SQL>Grant Connect, resource to user2;
 
-- Grant succeeded

User1 and user2 can now connect and create database objects in the database.

Granting privileges on database objects to the users.

Objects created in the database are always owned by a specific user. If a different user has to access the object then necessary privileges should be given.

Syntax:- 
Grant  on  to ;  
 
  
      
  • Privileges          -       Type of privilege     
    •   
  • Object      name     -      database object name like EMP.
    •   
  • User      name         -      User to whom we wanted to provide      the privilege.
    •  
  
 
  
Example: If user2 wants to access the table created by user1 then user1 should provide the privileges to user2 as user1 owns the object.
  
We can provide privileges either INDIVIDUALLY or SET OF or ALL privileges.
  
 
  
Individual privileges:-
  
SQL> conn user1/pwd
 
Let’s assume EMP table is owned by user1. Grant select privileges to user2.
 
SQL> grant select on EMP to user2;
 
-- Grant succeeded
  
 
  
If User2 has to access the ‘emp’ table of user1 then user2 has to use dot operator to access table.
  
Select * from user1.emp;
  
 
  
SET OF privileges:-
  
SQL> grant select, insert,update,delete on EMP to user2;
 
-- Grant succeeded
  
 
  
ALL Privileges:-
  
SQL>grant all on EMP to user2;
-- Grant succeeded
  
 
  
 
  
 
  
 
  
Privileges with ‘GRANT OPTION’:-
  
We use ‘With Grant Option’ if we want user2 to grant privileges to other users. i.e. User2 will be having admin rights to provide privileges.
  
SQL>grant all on EMP to user2 with grant option;
-- Grant succeeded
  
 
  
Now user2 can provide grants to user3.
  
Connect to user2:
 
SQL>conn user2/pwd;
 
Grant ‘select’ privileges to user3:
 
SQL>grant select on EMP to user3;
-- Grant succeeded
My Profile
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)