Grant privilege update with restrictions in Oracle

Question

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?

Answer 1

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;