Granting and Revoking Privileges - Oracle DBA

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 160 system privileges available in the Oracle 10g 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 considered to be DBA privileges. The following are typical DBA privileges:

System Privileges

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:

System Privileges

System privileges are granted with the GRANT command, which has the following syntax:

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 SCRAWFORD using the GRANT command.

The user SCRAWFORD can now log in and create tables in the database within the SCRAWFORD 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:

Object Privileges

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:

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 numbers and e-mail addresses. The GRANT statement is as follows:

Now employees can update their records if they know their employee IDs. One of the new employees uses the following SQL command to change her e-mail address:

However, trying to update a different column in the table is not permitted:

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:

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.

Creating and Assigning Roles

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:

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:

Next, the privileges on the tables must be added to the roles:

Finally, the role itself is granted to the user OE:

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:

Creating and Assigning Roles

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:

Creating and Assigning Roles

2 rows selected.

The role HR_EMP_DEPT has SELECT privileges against two tables in the HR schema: EMPLOYEES and DEPARTMENTS.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics