Skip to content
Home » Oracle » Oracle Grant Privilege Correctly

Oracle Grant Privilege Correctly

Granting vs Revoking

Granting a valid privilege to a grantee is easy and can be repeated over and over again, even though the privilege has been granted. No error is thrown if you have used the correct syntax with valid privileges.

On the other side, revoking a valid privilege from a user is different and rather strict, it fails with ORA-01952 for system privileges or ORA-01927 for object privileges if the privilege has never been granted to the user.

Another difference in practice is that, we do granting a lot whenever user needs them, but we seldom do revoking.

In this post, we'll see some formal granting patterns which remind us to do it correctly and efficiently.

  1. Grant System Privilege
  2. Grant Object Privilege
  3. Grant All Privileges
  4. Revoke All Privileges

Grant System Privilege

System privileges may be required to be granted whenever an user has been created.

Grant Single Privilege to Single Grantee

The syntax is as:

GRANT <ROLE or SYSTEM_PRIVILEGE> TO <GRANTEE>;

We grant a system privilege to a user. This is one to one type, the simplest pattern of GRANT syntax.

SQL> grant create procedure to hr;

Grant succeeded.

Grant Single Privilege to Multiple Grantees

The syntax is as:

GRANT <ROLE or SYSTEM_PRIVILEGE> TO <GRANTEE>, <GRANTEE>;

We grant a system privilege to some users. This is one to many type.

SQL> grant create procedure to hr, oe, sh;

Grant succeeded.

Grant Multiple Privileges to Single Grantee

The syntax is as:

GRANT <ROLE or SYSTEM_PRIVILEGE>, <ROLE or SYSTEM_PRIVILEGE> TO <GRANTEE>;

We can even mix a role in the system privilege list. This is many to one type.

SQL> grant create procedure, create table, create view, connect to hr;

Grant succeeded.

In this example, CONNECT is a role.

Grant Mixed Privileges to Multiple Grantees

The syntax is as:

GRANT <ROLE or SYSTEM_PRIVILEGE>, <ROLE or SYSTEM_PRIVILEGE> TO <GRANTEE>, <GRANTEE>;

We can see multiple privileges product multiple users in a single statement. This is many to many type.

SQL> grant create procedure, create table, create view, connect to hr, oe, sh;

Grant succeeded.

It's very efficient way to grant privileges at a time if they are the same type of user.

Grant Object Privilege

The syntax is as:

GRANT <OBJECT_PRIVILEGE>, <OBJECT_PRIVILEGE> ON <SCHEMA>.<OBJECT> TO <GRANTEE>;

The rules applied to granting system privilege also apply to granting object privilege. However, we have an extra ON object clause in GRANT statement.

SQL> grant select, insert, update, delete on hr.employees to oe, sh, pm;

Grant succeeded.

The object name should be full qualified if the grantor is not the owner of the object.

Multiple Objects?

Can we grant object privileges on multiple objects? No, we can't. Target objects cannot be multiplied. Let's see an incorrect statement.

SQL> grant select, insert, update, delete on hr.employees, hr.countries to oe, sh, pm;
grant select, insert, update, delete on hr.employees, hr.countries to oe, sh, pm
                                                    *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Here we granted on two tables in a statement, but we saw ORA-00990 eventually, which means that multiple objects is not acceptable when granting object privileges.

Grant All Privileges

To grant all granted privileges to an user, we have some simple ways to do it.

Revoke All Privileges

To withdraw all granted privileges from an user, the steps might be a little tricky.

Leave a Reply

Your email address will not be published. Required fields are marked *