Oracle Concepts - grant user privileges Oracle Tips by Burleson Consulting

By Robert Freeman

Once we have created a user, we probably want that user to be able to do something in our database. A created user has no privileges, they can not even connect to the database. In this section we will address this problem by learning how to give users the privileges they need to actually get some work done. First we will look at system privileges, followed by object privileges. We will then look at how you can use roles to make user administration much easier.

Oracle System Privileges

System privileges allow the user to perform system level activities. This might include such things as being able to actually connect to the system, or it might include the ability to do things like create objects in schemas other than your own schema. In the next sections we will discuss the grant command, which you use to grant system privileges. Also the revoke command is used to revoke privileges from users.

Granting Oracle System Level Privileges

The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can:

* Connect to the database (create session)

* Create objects (create table, create index)

* Perform DBA activities, like backup the database (SYSDBA, SYSOPER)

* Alter session related parameters (alter session)

Basically, even if you create a user account, it will not be able to do anything until you have granted it a basic set of privileges. Typically you would always grant the create session privilege so that the user can connect to the database.

The grant command is pretty simple to use, you just need to know what privilege you want to grant and who you want to grant that privilege to.

GRANT INDEX ON <My Table> TO <Other User>;

If you want the user to be able to create an index, you would grant that user the create any index privilege with the grant command as seen here:

GRANT CREATE ANY INDEX TO Robert;

There are a number of different privileges that you can assign to a given user, way to many to list here. You can find a list of the different privileges that can be assigned to a given user in the Oracle SQL Reference Guide under the grant command documentation.

You can also grant multiple privileges in the same grant command by simply separating the privileges by a comma as seen in this example:

GRANT CREATE ANY INDEX, SELECT ANY TABLE TO Robert;

Note the use of the word �ANY� in �SELECT ANY TABLE.�  Using the ANY keyword in reference to a system privilege means that the user can perform the privilege on any objects owned by any user except for SYS.  By default, if you are granted a privilege, you cannot assign your privilege to others.  You can�t grant or revoke that privilege to or from anyone else. Sometime you want to grant privileges to users and have them be able to grant those privileges to other users. When this is the case, we include the with admin keyword in the grant command. When this keyword is used, it will allow the user granted the privilege to grant that privilege to other users. Here is an example of the usage of the with admin option keyword.

GRANT CREATE ANY INDEX TO Robert WITH ADMIN OPTION;

Revoking System Level Privileges

The revoke command is used to revoke system level privileges that were previously granted with the grant command. Simply enter the privilege you wish to revoke in the body of the revoke command as seen in this example:

REVOKE CREATE ANY INDEX FROM Robert;

To be able to revoke a privilege from another user, you must have been granted that privilege with the admin option, as demonstrated earlier in this section. Revoking system level privileges will only impact the user that you are revoking the privileges from. Any user that was granted system privileges by that user will still continue to have those privileges.

You can revoke all privileges from a user with the all privileges option of the revoke command as seen in this example:

REVOKE ALL PRIVILEGES FROM Robert;

Oracle Object Privileges

Once you have created users and given them system privileges, they will start creating objects (and as DBA you too will be creating objects no doubt!). Once objects are created, only the user who created those objects will be able to actually do anything with them. This is not particularly useful since you don�t want to be giving out your user id and password to everyone in the world. Oracle gives us the grant and revoke commands so that we can give other users access to objects in the database. Let�s look at these commands in more detail.

Granting Object Level Privileges

In order to allow other users to access your objects you can use the grant command (yes, the very same grant command used to grant system privileges) to allow different kinds of access to your objects. For example, we can grant the user TOM access to the EMP table in the SCOTT account (or schema) with this command:

GRANT SELECT ON emp TO tom;

Only the schema that owns the object can grant privileges to that object unless the with grant option is included in the command. The with grant option allows you to give the user you are assigning the privilege to the right to grant that privilege to other users. Here is an example of the use of the with grant option:

GRANT SELECT ON emp TO scott WITH GRANT OPTION

Revoking Object Level Privileges

When we no longer wish a user to have access rights to an object we can use the revoke command to remove those rights as seen in this example:

REVOKE SELECT on emp FROM scott;

There is one major difference in the revocation of object privileges and system privileges. With objects, if you revoke a privilege from a user who had been granted it previously with the with grant option,

Oracle acts a bit different. In this case, the revoke operation will affect not only the user that you are revoking the privilege from, but all other users who that user had granted privileges to will have those privileges revoked as well. Hence, be careful revoking object privileges, you might find that you end up breaking something!

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It�s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.