CODEWITHSUNDEEP
oracle-databasesecuritypolicyvpd
TrongDat
TrongDat

Reputation: 1

How to add a policy in Oracle that a user can select all data from table and only can insert,update, delete his data with condition

I have a object name like this.

grant execute on dbms_rls to public;
create table empholiday
(
    EmpNo number(5),
    Name nvarchar2(60),
    Holiday date
);
INSERT INTO empholiday VALUES (1,'HANN','02-JAN-2019');
INSERT INTO empholiday VALUES (2,'ANNU','12-MAY-2019');
INSERT INTO empholiday VALUES (3,'THEOTA','26-AUG-2019');

I created 3 users: HANN, ANNU,THEOTA I want to buid a VPD policy for these users:

This is what I've done so far:

create user HANN identified by 123456;
create user ANNU identified by 123456;
create user THEOTA identified by 123456;
create role emp;
grant connect to emp;
grant create session to emp;
grant emp to HANN;
grant emp to ANNU;
grant emp to THEOTA;
grant select,update,delete,insert on empholiday to ANNU;
drop function Annu_Theota;
--create function
create or replace function Annu_Theota (p_schema varchar2, p_obj varchar2)
return varchar2 as
l_user varchar2(20);
begin
  l_user := SYS_CONTEXT('userenv', 'SESSION_USER'); 
  if (l_user = 'ANNU') then
    return 'Name=' || '''' || l_user || '''';
  elsif (l_user = 'THEOTA') then
    return '1=0';
  end if;
end Annu_Theota;

begin
  dbms_rls.add_policy
    (object_schema=>'trongdat' 
    ,object_name=>'empholiday'  
    ,policy_name=>'HolidayControl' 
    ,policy_function=>'Annu_Theota_Hann' 
    ,statement_Types=>'SELECT,UPDATE,INSERT,DELETE'  
    );
end;

I don't know how to apply a suitable policy for this user HANN. Can anyone advise some idea.

Upvotes: 0

Views: 2786

Answers (1)

pmdba
pmdba

Reputation: 7043

Edit: Something else just jumped out at me, looking over this again - DO NOT GRANT EXECUTE ON DBMS_RLS TO PUBLIC!!! This function is only required by the user creating and updating policies. By granting it to public, you have allowed all users to view and disable or delete any policy and expose any data.

Using VPD policies only limits the rows and sometimes columns that are visible to a user. You can't use them to apply business logic on allowable column contents or limit a user to only updating specific columns. The privileges you are talking about - insert, update, and delete - will always apply to all of the columns in the table. Given the level of granularity of your requirements - column level with specific limitations on values, that is - you should consider not allowing table updated directly, but rather use PL/SQL functions as an API layer to perform the updates.

For example, create a PL/SQL package named empholiday_pkg with the following procedures:

  • get_data: a pipelined function to select rows from the table
  • ins_data: a procedure to insert rows in the table
  • upt_data: a procedure to apply updates to the table
  • del_data: a procedure to delete rows from the table

Instead of granting "select,insert,update,delete" on the table to users directly, grant them execute permissions on the package. Then include whatever business logic you need to - limiting which columns they can modify based on role or other user characteristic for example - within the PL/SQL of the package.

Users would then call the package to get data or make changes:

select * from empholiday_pkg.get_data();

execute empholiday_pkg.ins_data([put your parameters here]);

begin
    empholiday_pkg.upt_data([put your parameters here]);
    commit;
end;

If you want to control access to the procedures individually, create separate singleton packages for each one. Using packages instead of stand-alone procedures allows you to wrap the PL/SQL code in the package body for security. Granting execute permissions on a package only allows users to see the package header in the data dictionary, where stand-alone procedures would be completely exposed.

One other word of advice: don't put "commit" commands in the package procedures themselves - handle that just as you would have previously using stand-alone insert/update/delete commands so that each user can manage their transactions, or so that you could build a larger API on top of the basic table DML package.

A complete write-up on this technique is available in "Guarding Your Data Behind a Hard Shell PL/SQL API" by Bryn Llewellyn.

Upvotes: 0

Related Questions