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.
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
. 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
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
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.
1. 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;
2. 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
privileges on database Object Tables. To grant these privileges we can either write separate grant statements
for granting individual privilege 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.
3. 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
4. WITH GRANT OPTION
Just like we use ADMIN OPTION
flag with system privileges similarly we use Grant option flag with object privileges. GRANT OPTION
flag 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 on your social networking so that I may reach more people. Take Care & Have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.