CODEWITHSUNDEEP
sqlsql-grantoracle12c
paarandika
paarandika

Reputation: 1439

Grant privilege update with restrictions in Oracle

Im new in Oracle. My db has two tables employee and department.

Employee(Empid, Name, Designation, Date_Joined, Salary, Qualification, Dcode)
Department (Dcode, Dname, Location)

I need to grant privilege update to user c##Amila on Employee table's column salary. And he shouldn't be ale to update salaries which are more than 50000 and he should not be able to insert a value more than 50000. I can grant the privilege with 

GRANT UPDATE("salary") ON "Employee" TO c##Amila;

But is there a way to add above constraints to this grant?

Upvotes: 0

Views: 144

Answers (1)

Multisync
Multisync

Reputation: 8787

I've never heard about such privileges in Oracle, but in your case you can create an updatable view with CHECK option: 

create view EmployeeV as select * from Employee 
where salary <= 50000 with check option;

and grant what you need on this view:

GRANT UPDATE(salary) ON EmployeeV TO c##Amila; 
GRANT INSERT ON EmployeeV TO c##Amila;

Upvotes: 1

Related Questions