1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle DCL Data Control Language

Discussion in 'SQL PL/SQL' started by SBH, Nov 8, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Data Control Statements are one of the SQL statement types which enforce database security at database and object level. It deals with the access permissions and rights of a user. The article explains the data control statement commands and more about privileges.

    2. Introduction

    DCL commands are used to assign security levels in database, which involves multiple user setups. They are used to grant defined role and access privileges to the users. There are two types of DCL commands in Oracle:
    1. GRANT
    2. REVOKE

    [​IMG]

    [​IMG]

    3. GRANT

    A DBA or user can grant access permission on owned database objects to other user or roles using GRANT command.

    Syntax:

    Code (Text):
    GRANT [privilege]
    ON [object]
    TO {user |PUBLIC |role}
    [WITH ADMIN | GRANT OPTION];
    Example

    1. U1 is a newly created user in the database. DBA shall grant below privileges to user in order to connect to the database.

    Code (SQL):
    SQL> GRANT CREATE SESSION TO U1;
    Above privilege will enable U1 to create a session in the database.

    Code (SQL):
    SQL> GRANT CONNECT, RESOURCE TO U1;
    Above privilege will enable U1 to connect to the database.

    2. U1 is the owner of table T1 and Procedure P1. U2 is another user in same database seeking access on T1 and P1. Grantor U1 can issue below DCL commands

    Code (SQL):
    SQL> GRANT SELECT ON T1 TO U2;
    Above privilege will give only SELLECT access on T1 to U2

    Code (SQL):
    SQL> GRANT EXECUTE ON P1 TO U2;
    Above privilege will give only EXECUTE privilege on P1 to U2


    4. Privileges

    Privilege is the permission name, which is granted to the user. It may be SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALL and many others, on a database object.

    Privileges are of two types

    System privilege – Schema level privilege which is granted by DBA to the users is known as System privilege. They include privilege to issue commands like CREATE (cluster, database link, directory, job, procedure, role, synonym, table, trigger, tablespace, types, view, database), ALTER, DROP, DEBUG, FLASHBACK, LOCK, CONNECT, RESOURCE etc. Please note that these privileges are not limited to an object access, but applicable at User level.

    Object privilege – Object level privilege can be granted from object owning user (Grantor) to other user (GRANTEE). Grantor permits access on a specific object and grantee enjoys the access privilege only on the database object concerned. It may be DELETE, SELECT, INSERT, UPDATE, EXECUTE, INDEX, READ, WRITE, ALTER (table, sequence).

    Privileges can be granted to a specific user, assigned to a specific Role or made PUBLIC. Privileges which are made public are enjoyed by all the users.

    5. ADMIN/GRANT option

    ADMIN option allows the Grantee to grant a System privilege to other user. Only DBA or user with GRANT ANY PRIVILEGE system privilege can grant a system privilege to other users. Users which possess a system privilege with ADMIN option can pass only that system privilege.
    Example:
    DBA grants CREATE INDEX privilege to user U1 with admin option

    Code (SQL):
    SQL> GRANT CREATE INDEX TO U1 WITH ADMIN OPTION
    Now user U1 can grant CREATE INDEX system privilege to another user U2

    Code (SQL):
    SQL> GRANT CREATE INDEX TO U2
    Note that U2 has been granted the CREATE INDEX system privilege without ADMIN OPTION. So U2 cannot pass this privilege further to its peers.

    GRANT option allows the Grantor (owner) to grant an Object privilege on an object to other user. To grant an object privilege, grantor must satisfy any of the below criteria:
    1. He must own the object
    2. He must have access on object with GRANT OPTION from the owner of the object
    3. He must have GRANT ANY OBJECT PRIVILEGE system privilege and an object privilege on the object

    Example,
    User U1 owns a Procedure P1. U2 seeks access privilege on P1 from U1. U1 would grant EXECUTE privilege on the table with GRANT OPTION as below

    Code (SQL):
    SQL> GRANT EXECUTE ON P1 TO U2 WITH GRANT OPTION
    Now U2 can grant EXECUTE on P1 to other users too. In the below command, U2 grants EXECUTE ON P1 privilege to U3 but not with GRANT OPTION

    Code (SQL):
    SQL> GRANT EXECUTE ON P1 TO U3
    5. REVOKE

    The DCL command is used to revoke an existing privilege from a user. It can revoke a system privilege, object privilege or a role from a user. Only DBA or a user with ADMIN OPTION can revoke system privilege.

    Syntax:
    Code (SQL):
    REVOKE [privilege]
    ON [object]
    FROM {USER |PUBLIC | ROLE}
    Example

    Below command will REVOKE a SELECT privilege on T1 table from U1.

    Code (SQL):
    SQL> REVOKE SELECT ON T1 FROM U1;
     

    Attached Files: