Oracle7 Server Administrator's Guide
Granting User Privileges and Roles
This section describes aspects of granting privileges and roles, and includes the following 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 Server 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 CREATE SESSION system privilege and the ACCTS_PAY role to the user JWARD:
GRANT create session, accts_pay
TO jward;
Note: Object privileges cannot be granted along with system privileges and roles in the same GRANT statement.
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:
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 Server 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:
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:
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:
GRANT ALL ON salary TO jfee;
Note: System privileges and roles cannot be granted along with object privileges in the same GRANT statement.
The GRANT OPTION
The user whose schema contains an object is automatically granted all associated object privileges with the GRANT OPTION. This special privilege allows the grantee several expanded privileges:
- The grantee can grant the object privilege to any user or any role in the database.
- The grantee can also grant the object privilege to other users, with or without the GRANT OPTION.
- 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.
Warning: Before granting a column-specific INSERT privilege, determine if the table contains any columns on which NOT NULL constraints are defined. Granting selective insert capability without including the NOT NULL columns prevents the user from inserting any rows into the table. To avoid this situation, make sure that each NOT NULL column is either insertable or has a non-NULL default value. Otherwise, the grantee will not be able to insert rows into the table and will receive an error.
Grant INSERT privilege on the ACCT_NO column of the ACCOUNTS table to SCOTT:
GRANT INSERT (acct_no)
ON accounts TO scott;