Granting and Revoking Privileges
Privileges are rights to execute specific SQL statements. The DBA grants privileges to user accounts to control what users can do in the database. There are two kinds of privileges: system privileges and object privileges. The GRANT command allocates system and object privileges to a user. The REVOKE command removes privileges from a user.
privileges
The right to perform a specific action in the database, granted by the DBA or other database users.
Roles provide an easy way to group privileges together and assign them to one or more users in the database.
System Privileges
System privileges allow users to perform a specific action on one or more database objects or users in the database. There are more than 100 system privileges available in the Oracle9i database. Typically, system privileges will fall into two general categories: DBA privileges and user privileges. There is no distinction at the database level between these two types of system privileges.
system privileges
Privileges that allow users to perform a specific action on one or more database objects or users in the database.
In general, system privileges that can affect the database as a whole are generally considered to be DBA privileges. The following are typical DBA privileges:
| Privilege | Description |
|---|---|
| CREATE USER | Create a new database user |
| DROP USER | Remove a database user |
| CREATE ANY TABLE | Create a new table in any schema |
| CREATE TABLESPACE | Create a new tablespace |
| AUDIT ANY | Turn on or off database auditing |
| DROP ANY INDEX | Drop an index in any schema |
System privileges that allow users to perform specific tasks within a single schema are considered to be user privileges. The typical user privileges are generally a bit more innocuous than the DBA privileges, as you can see by the following examples:
| Privilege | Description |
|---|---|
| CREATE SESSION | Establish a connection to the database |
| CREATE TABLE | Create a table in the user’s schema |
| CREATE PROCEDURE | Create a stored function or procedure |
System privileges are granted with the GRANT command, which has the following syntax:
GRANT sys_privilege [, sys_privilege ...] TO user [, user, role, PUBLIC ...];
Notice that the syntax makes it easy to grant a group of privileges all at once to one user or to many users. Also, a privilege may be granted to a special class of users called PUBLIC. When a privilege is granted to PUBLIC, all current and future users will have that privilege.
The CREATE SESSION privilege is important because a user cannot log in to the database without this privilege. Janice, the DBA, realizes that the new user account she created did not have this privilege. In addition, the new user will be creating new tables, so she needs the CREATE TABLE privilege. Janice applies both of these privileges to JSMITH using the GRANT command.
grant create session, create table to jsmith; Grant succeeded.
The user JSMITH can now log in and create tables in the database within the JSMITH schema.
The questions you may be asking are, “Why isn’t the CREATE SESSION privilege automatic? Don’t we want everyone to be able to log in? Why would we create a user who couldn’t log in?"
In some database application environments, it is beneficial to keep all of the tables within a single schema for ease of maintenance, quota, and backups. You might not, however, allow the schema owner to log in. In this way, the application users can be tracked to know who used what table in the application’s schema. If only the application’s username were used, you would not know which user performed what action against the database. The DBA can set up the proper permissions and synonyms for other users to access this new schema, without the need for the application schema’s owner to ever log in to the database.
Object Privileges
Object privileges allow users to manipulate the contents of database objects in other schemas. Object privileges are granted on schema objects such as tables, directories, and stored procedures. They are granted to a username in a different schema. In other words, the owner of an object in a schema has all privileges on the object and can grant privileges on the object to another user.
object privileges
Privileges that allow users to manipulate the contents of database objects in other schemas.
Typical object privileges include the following:
| Privilege | Description |
|---|---|
| SELECT | Read (query) access on a table |
| UPDATE | Update (change) rows in a table or view |
| DELETE | Delete rows from a table or view |
| INSERT | Add rows to a table or view |
| EXECUTE | Run (execute) a stored procedure or function |
| INDEX | Create an index on a table |
In addition to the ability of the user to grant privileges on objects to other users, a user can grant the privilege for the grantee to subsequently grant the same privilege to yet another user.
Object privileges are granted with a GRANT statement similar to that for granting system privileges:
GRANT obj_privilege [(column_list)] [, obj_privilege ...] ON object TO user [, user, role, PUBLIC ...] [WITH GRANT OPTION];
The column_list parameter is used if the object is a table and only certain columns of the table are made available for updating by other users. The WITH GRANT OPTION clause allows the grantee to pass the privilege on to yet another user.
The HR department at Scott’s widget company frequently receives requests to update the EMPLOYEES table. The department asks Janice, the DBA, to make some of the columns of the table available to all employees, so that they can make changes to their phone number and e-mail address. The GRANT statement is as follows:
grant update (email, phone_number) on employees to public;
Now employees can update their records if they know their employee ID. One of the new employees uses the following SQL command to change his e-mail address:
update hr.employees set email=’RSMITH’ where employee_id = 502; 1 row updated.
However, trying to update a different column in the table is not permitted:
update hr.employees set salary=25000 where employee_id = 502; update hr.employees set salary=25000 * ERROR at line 1: ORA-01031: insufficient privileges
In fact, even selecting rows from the table is disallowed:
select * from hr.employees where employee_id = 502; select * from hr.employees * ERROR at line 1: ORA-01031: insufficient privileges
Any user other than HR has only the object privilege on EMPLOYEES to update the EMAIL and PHONE_NUMBER columns.
After a month or so, the HR department has decided that granting the privileges on the two columns in the EMPLOYEES table was not a very good idea. Employees were using the wrong employee number to update the EMPLOYEES table, and they inadvertently updated the wrong e-mail and phone number information. To solve the problem, Janice revokes the privileges on the EMPLOYEES table, as follows:
revoke update on employees from public; Revoke succeeded.
Notice that the REVOKE statement did not specify any columns in the EMPLOYEES table. When revoking UPDATE privileges on a table, columns cannot be specified. If the HR department wanted to continue to allow access to one of the columns, a new GRANT statement specifying the desired column would be issued after the REVOKE statement.
Creating and Assigning Roles
A role is a named group of privileges. Using roles makes it easy for the DBA to grant groups of privileges to users. Granting a role takes a lot fewer steps than granting individual privileges. For example, if several users all require the same 15 privileges, it’s a lot easier to assign those 15 privileges to a role first, and then assign the role to each user who needs it.
role
A named group of privileges created to ease the administration of system and object privileges.
The privileges granted to the role can be a combination of system and object privileges. A user may be granted more than one role in addition to any system or object privileges granted directly. Roles are created with the CREATE ROLE statement. The basic syntax for CREATE ROLE is as follows:
CREATE ROLE <rolename> [IDENTIFIED BY <role_password>];
As the syntax indicates, a role may have a password. If a role requires a password, a user granted this role must use the SET ROLE command to use the privileges granted to the role.
The Order Entry department at Scott’s widget company wants to give employees in certain departments an additional discount on orders placed. To identify a customer as an employee, the Order Entry department will need access to the EMPLOYEES and DEPARTMENTS tables in the HR schema. Janice, the DBA, decides that using a role might be the best way to provide this access, since other departments may be asking for this same functionality in the future.
The first step is to create a role to hold the privileges. Janice creates the role as follows:
create role hr_emp_dept; Role created.
Next, the privileges on the tables must be added to the roles:
grant select on hr.employees to hr_emp_dept; Grant succeeded. grant select on hr.departments to hr_emp_dept; Grant succeeded.
Finally, the role itself is granted to the user OE:
grant hr_emp_dept to oe; Grant succeeded.
Now the user OE can read the contents of the EMPLOYEES and DEPARTMENTS tables in the HR schema. In the future, to provide the same access to the HR tables to other departments, only the last GRANT statement needs to be executed.
To check the roles granted to the OE user, Janice runs the following query against the DBA_ROLE_PRIVS data dictionary view:
select grantee, granted_role from dba_role_privs where grantee = ‘OE’; GRANTEE GRANTED_ROLE ------------------------- ------------ OE CONNECT OE RESOURCE OE HR_EMP_DEPT 3 rows selected.
To find out which privileges are assigned to the role HR_EMP_DEPT, Janice runs another query against the ROLE_TAB_PRIVS data dictionary view:
select role, owner, table_name, privilege from role_tab_privs where role=’HR_EMP_DEPT’; ROLE OWNER TABLE_NAME PRIVILEGE ----------------- -------- -------------------- --------- HR_EMP_DEPT HR EMPLOYEES SELECT HR_EMP_DEPT HR DEPARTMENTS SELECT 2 rows selected.
The role HR_EMP_DEPT has SELECT privileges against two tables in the HR schema: EMPLOYEES and DEPARTMENTS.
