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 *