Granting roles and privileges in Oracle DBA

This section describes aspects of granting privileges and roles, and includes the below topics:

Granting system privileges and roles:

You can grant system PRIVILEGES AND ROLES to other roles and users using either the grant system privileges/roles dialog box of enterprise manager or the sql command grant.

To grant a system PRIVILEGE or role, you must have the admin option for all system privileges and roles being granted. Also, any user with the grant any role system privilege can grant any role in a database.

The following statement grants the system privilege and the accts_pay role to the user jward:

Sql>grant create session, accts_pay to  jward;

The admin option:

When a user creates a role, the role is automatically granted to the creator with the admin option. A grantee with the admin option has several expanded capabilities:

  • The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. (users cannot revoke a role from themselves.)
  • The grantee can further grant the system privilege or role with the admin option.
  • The grantee of a role can alter or drop the role.

In the following statement, the security administrator grants the new_dba role to michael:

Sql>grant new_dba to michael with admin option;

The user michael cannot only use all of the privileges implicit in the new_dba role, but can grant, revoke, or drop the new_dba role as deemed necessary. Because of these powerful capabilities, exercise caution when granting system privileges or roles with the admin option. Such privileges are usually reserved for a security administrator and rarely granted to other administrators or users of the system.

Granting object privileges and roles:

You can grant object privileges to roles and users using the add privilege to role/user dialog box of enterprise manager or the sql command grant.

To grant an object privilege, you must fulfill one of the following conditions:

  • You own the object specified.
  • You have been granted the object privileges being granted with the grant option.

The following statement grants the select, insert, and delete object privileges for all columns of the emp table to the users jfee and tsmith:

Sql>grant select, insert, delete on emp to jfee, tsmith;

To grant the insert object privilege for only the ename and job columns of the emp table to the users jfee and tsmith, issue the following statement:

Sql>grant insert(ename, job) on emp to jfee, tsmith;

To grant all object privileges on the salary view to the user jfee, use the all shortcut, as shown in the following example:

Sql>grant all on salary to jfee;

The grant option:

The user whose schema contains an object is automatically granted with all associated object privileges with the grant option. This special privilege allows the grantee several expanded privileges:

1. The grantee can grant the object privilege to any user or any role in the database.
2. The grantee can also grant the object privilege to other users, with or without the grant option.
3. If the grantee receives object privileges for a table with the grant option and the grantee has the create view or create any view system privilege, the grantee can create views on the table and grant the corresponding privileges on the view to any user or role in the database.
The grant option is not valid when granting an object privilege to a role. Oracle prevents the propagation of object privileges via roles so that grantees of a role cannot propagate object privileges received by means of roles.

Granting privileges on columns:

You can grant insert, update, or references privileges on individual columns in a table. Grant insert privilege on the acct_no column of the accounts table to scott:

Sql>grant insert (acct_no)  on accounts to scott;


Get Updates on Tech posts, Interview & Certification questions and training schedules