Every database object has a particular set of object privileges associated with it. Object privileges allow users to perform certain actions on database objects, for example Executing DML on tables.
Object privileges are the rights given to a user to access and perform some actions on the database objects or the objects owned by some other user in the database.
Read: System Privileges in Oracle Database Introduction
An object’s owner has following advantages:
- The owner has every object privilege on the Object owned by it.
- The privileges enjoyed by the Object owner cannot be revoked.
- The owner can grant any number of object privileges for that particular object to other database users.
If a user has ADMIN privilege then it can either grant or revoke object privileges to and from the users that are not the owners.
We use GRANT data control language (DCL) statement to grant object privilege to the users. And to use GRANT statement you must have either GRANT ANY PRIVILEGE system privilege or you must have been granted the system privilege with the ADMIN OPTION. And if you are a Sysdba then you can simply log on to your database using sys user with sysdba privileges
The syntax for Grant DCL statement for granting Object Privilege is.
GRANT object privilege name(s) ON object name TO user name(s)
Here the statement is started with the keyword GRANT and then followed with the name of the OBJECT PRIVILEGE. In the latter case you can either specify a single object privilege or give the entire list of every valid object privilege. Thereafter we have another keyword ON which is followed by OBJECT NAME which can be assigned the privilege by you. Then you have another keyword TO that is followed by USERNAME to which you can then assign the grant privilege.
The most commonly used object privileges are Select, Insert, Update, Delete and Execute. You can click here to read the full list of object privileges available in oracle database.
Let’s see some examples for the better understanding of this concept.
For the demonstration of this particular topic I will use two users HULK and BATMAN which I created during my “Create user tutorials”. I would suggest you to read my article on how to create user for more insight on the topic. Hulk and batman – both these users are normal user and don’t have any rights or privileges except the CREATE SESSION system privilege.
DML object privilege for the Tables.
Being a database developer, there are several times when we have to select the data from the tables which are owned by some other user.
For example say user Hulk wants to select the data from the employees table owned by HR user. In this case user Hulk can either ask user HR to grant him the SELECT object privilege on employees table or ask the DBA for the same. In both the case query for granting SELECT object privilege will be the same.
GRANT select ON HR. Employees TO hulk;
How to grant multiple object privilege to a user / Grant All data control language statement.
Apart from Select object privilege we can also grant UPDATE, INSERT, DELETE, INDEX and REFERENCES object privileges on database Object Tables. To grant these privileges we can either write separate grant statements for granting individual privileges or we can club all the privileges in a single Grant statement. Such as
GRANT select, update, insert, delete, index, references ON hr.employees TO hulk;
But here we have to take care of a few things such as this list of privileges must only contain object privileges and not any System privileges. This is because object privileges and system privileges cannot be granted together in a single grant command.
Or you can replace this list of Object privileges by ALL keyword for example
GRANT ALL ON hr.employees TO hulk;
Both the queries are the same and perform the same task of granting all the valid object privileges on Employees tables.
Object Privilege on column level
Oracle allows you to grant object privileges on column level also but you can only grant INSERT, UPDATE, and REFERENCES object privilege on column level.
Say you want to grant update object privilege only on First name column of employees table. Then for that the query will be
GRANT update (first_name) ON hr.employees TO hulk;
On executing this query, user Hulk will be able to update only the first name column of the employees table. Similarly you can grant Insert and reference object privileges also.
WITH GRANT OPTION
Just like we use ADMIN OPTION flag with system privileges similarly we use Grant option flag with object privileges. GRANT OPTIONflag allows the user to grant an object privilege to another user. Let’s see how.
Let’s grant update privilege on employees table of user HR to user Hulk.
GRANT update ON hr.employees TO hulk WITH GRANT OPTION;
After executing this query user Hulk not only gets update object privilege but can also grant and revoke the same privilege to and from any user or roles.
That’s it guys! Hope you enjoyed reading and learned something useful in turn. Do be kind as to share this knowledge on Object Privileges with your friends on social network. Thanks & have a great day!