How to Grant Multiple Object Privileges in One Statement

  • by
You don't have to grant object privileges line by line, they can be merged into one statement under some conditions. Here are some cans and cannots.

You cannot ...

Grant object privileges on multiple target objects to a user.
SQL> grant select on hr.employees,hr.countries to sh;
grant select on hr.employees,hr.countries to sh
                            *
ERROR at line 1:
ORA-00905: missing keyword


You can ...

Grant an object privilege to multiple users.
SQL> grant select on hr.employees to sh,oe;

Grant succeeded.


Grant multiple object privileges on one object to a user.
SQL> grant select,update on hr.employees to sh;

Grant succeeded.


Grant multiple object privileges on one object to multiple users.
SQL> grant select,update on hr.employees to sh,oe;

Grant succeeded.


Now, we can conclude that the rules about granting object privileges are:
  • Multiple target objects in one statement is not allowed.
  • Single target object with any number of privileges or users in one statement is allowed.

Leave a Reply

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