Skip to content

Grant Privilege Correctly

  • by

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.

Grant System Privilege

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

1. Grant Single Privilege to Single 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.

2. Grant Single Privilege to Multiple Grantees

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

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

Grant succeeded.

3. Grant Multiple Privileges to Single Grantee

We grant some system privileges to a user. This is many to one type.

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

Grant succeeded.

4. Grant Mixed Privileges to Single Grantee

We can even mix a role in the system privilege list.

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

Grant succeeded.

In this example, CONNECT is a role.

5. Grant Mixed Privileges to Multiple Grantees

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 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.

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.

Leave a Reply

Your email address will not be published.