1

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?

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.