LAB CCIT$-513$

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

Student need to perform following task using Oracle Express SQL developer commands

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

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;

LAB CCIT$-513$

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.

can you write the code for this homework $?$