CODEWITHSUNDEEP
oracle-databaseoracle11g
peedee
peedee

Reputation: 2031

Oracle security policy

How to Create a FUNCTION that restrict users to seeing only employees with a salary of 1,500 or less ?? As an example: if i enter

SQL> SELECT * FROM employee;

EMPNUMB EMPNAME STARTDATE ENDDATE  SALARY  EMPCITY   DESCRIPTION
---------------------------------------------------------------------------
01      Jason Martin 25-JUL-96 25-JUL-06 1234.56 Geelong   Programmer
08      James Cat 17-SEP-96 15-APR-02 1232.78 Melbourne Tester

Upvotes: 1

Views: 118

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132600

You don't want a function, you want a view:

create view employee_view as
select * from employee where salary <= 1500;

Then only grant users access to the view, not the table. If necessary you can rename so that the view is called employee and the base table is called employee_table or whatever.

Alternatively, noting the title of your question, you could look at Virtual Private Database (VPD) aka "Fine-grained access control". But that sounds like overkill for your stated requirement, and is only available in Enterprise Edition.

Upvotes: 2

Related Questions