Oracle row level security setup?

Question

How can I configure a table such that a user can modify a row "owned" by that user, but not modify rows "owned" by other users?

Answer 1

You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details.

Dummy scenario: a list of tasks, only task owners can modify their task.

create table owners(owner_id int primary key
 , owner_name varchar2(10));
create table tasks(task_id int primary key
 , owner_id int
 , description varchar2(20)
 , completion number);
insert into owners(owner_id, owner_name) values (1, 'Mat');
insert into owners(owner_id, owner_name) values (2, 'Mark');
insert into tasks(task_id, owner_id, description, completion)
 values (100, 1, 'Task for Mat', 0);
insert into tasks(task_id, owner_id, description, completion)
 values (200, 2, 'Task for Mark', 0);
commit;

The policy function:

create or replace 
function tasks_update_policy(schema varchar2, tab varchar2)
return varchar2
is
 owner_id number;
begin
 select owner_id into owner_id 
 from owners
 where lower(owner_name) = lower(sys_context('userenv','session_user'));
 return 'owner_id = ' || owner_id;
exception
 when no_data_found then
 return '1=2'; -- deny unregistered users
end;

Implementing the policy:

begin
 dbms_rls.add_policy(object_schema => 'MAT'
 , object_name => 'TASKS'
 , policy_name => 'Tasks_update_policy'
 , policy_function => 'tasks_update_policy'
 , statement_types => 'update,delete,insert' -- policy restriction
 , update_check => true);
end;
/

When logged in as myself:

SQL> select * from mat.tasks;
 TASK_ID OWNER_ID DESCRIPTION COMPLETION
---------- ---------- -------------------- ----------
 100 1 Task for Mat 0
 200 2 Task for Mark 0
SQL> update mat.tasks set completion = 20 where task_id = 100 ;
1 row updated.
SQL> update mat.tasks set completion = 20 where task_id = 200 ;
0 rows updated.

When connected as 'Mark':

SQL> insert into mat.tasks values (101, 1, 'More work for Mat', 0);
insert into mat.tasks values (101, 1, 'More work for Mat', 0)
 *
ERROR at line 1:
ORA-28115: policy with check option violation