GRANTING AND REVOKING PERMISSIONS

Oracle provides extensive security features in order to safeguard information stored in its tables from unauthorized viewing and damage. Depending on a user's status and responsibility, appropriate rights on Oracle's resources can be assigned to the user by the DBA. The rights that allow the use of some or all of Oracle's resources on the Server are called Privileges.

Objects that are created by a user are owned and controlled by that user. If a user wishes to access any of the objects belonging to another user, the owner of the object will have to give permissions for such access. This is called Granting of Privileges.

Privileges once given can be taken back by the owner of the object. This is called Revoking of Privileges.

Granting Privileges Using the GRANT Statement

The Grant statement provides various types of access to database objects such as tables, views and sequences and so on.

Syntax:

GRANT < Object Privileges > ON TO < User-Name >

[WITH GRANT OPTION];

OBJECT PRIVILEGES

Each object privilege that is granted authorizes the grantee to perform some operation on the object. A user can grant all the privileges or grant only specific object privileges.

The list of object privileges is as follows:

ALTER          Allows the grantee to change the table definition with the ALTER TABLE command.

DELETE       Allows the grantee to remove the records from the table with the DELETE command

INDEX           Allows the grantee to create an index on the table with the CREATE INDEX command

INSERT         Allows the grantee to add records to the table with the INSERT command

SELECT        Allows the grantee to query the table with the SELECT command

UPDATE       Allows the grantee to modify the records in the tables with the UPDATE command

WITH GRANT OPTION

The WITH GRANT OPTION allows the grantee to in turn grant object privileges to other users.

The examples that follow require users to be created prior granting permissions.

Example 1:

Give the user kalpesh all data manipulation permissions on the table EMP_MSTR.

GRANT ALL ON EMP_MSTR TO jnd;

Example 2:

Give the user kalpesh permission to only view and modify records in the table CUST_MSTR.

GRANT SELECT, UPDATE ON CUST_MSTR TO jnd;

Example 3:

Give the user Ivan all data manipulation privileges on the table ACCT_MSTR along with an option to further grant permission on the ACCT_MSTR table to other users.

GRANT ALL ON ACCT_MSTR TO Ivan WITH GRANT OPTION;

Referencing A Table Belonging To Another User

Once a user has privileges to access another user's object(s), the user can access the table by prefixing the table with the name of the owner.

Example 4:

View the contents of the FD_MSTR table that belongs to Ivan.

SELECT * FROM Ivan.FD_MSTR;

Granting Privileges When A Grantee Has Been Given The GRANT Privilege

If the user wants to grant privileges to other users, the user must be the owner of the object or must be given the GRANT option by the owner of the object.

Example 5:

Give the user Chetan permission to view records from the TRANS_MSTR table. The table originally belongs to the user Ketan, who has granted you the privilege to pass on the privileges that you have to others using the GRANT privilege option.

GRANT SELECT ON Ketan.TRANS_MSTR TO Chetan;

REVOKING PRIVILEGES GIVEN

Privileges once given can be denied to a user using the REVOKE command. The object owner can revoke privileges granted to another user.  A user of an object who is not the owner, but has been granted GRANT privilege, has the power to REVOKE the privileges from a grantee.

Revoking Permissions Using The REVOKE Statement The REVOKE statement is used to deny the grant given on an object.

Syntax:

REVOKE

The revoke command is used to revoke object privileges that the user previously granted directly to the grantee.

The REVOKE command cannot be used to revoke the privileges granted through the operating
system.                      

Example 6:

All privileges on the table NOMINEE_MSTR have been granted to Anil. Take back the Delete privilege on the table.

REVOKE DELETE ON NOMINEE_MSTR FROM Anil;

Example 7:

Take back all privileges on the table NOMINEE_MSTR from Anil.

REVOKE ALL ON NOMINEE_MSTR FROM Anil;

Example 8:

Rocky has the permission to view records from FDSLAB_MSTR. Take back this permission. Note that Alex is the original owner of FDSLAB_MSTR table.

REVOKE SELECT ON Alex.FDSLAB_MSTR FROM Rocky;

PRIVILEGES AND ROLES
            In order to access an object to owned by another account the privilage to access that object must first have been granted. Typically,nonowners are granted the privilege to insert,select,update,or delete rows form a table or view.privilage to select values from sequence and execute procedures and functions may also be granted.No privileges are granted on indexes or triggers,since they are accessed by the database during table activity.
            Privilages may be granted to individual  users or PUBLIC, which gives the privilavge to all users in the database.
            Roles,which are groups of privileges,can be used to simply this process, privileges can be granted to multiple users.Adding new users to application then becomes a much easier process to manage since it is simply a matter of granting or revoking roes for the user.

            The relationship between privileges and roles is shown in figure below: