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
This is quite old tread but I was facing similar issue, so if someone encountered it, hope this might help.
I had RLS implemented on SQL Server on IaaS and it was based on Active Directory Groups checks by IS_MEMBER() function. (I have sync with AAD, but here used config with local AD). It worked well until I migrated to PaaS (Azure SQL DB Single instance).
First thing was that while on IaaS I used to refer to AD groups from local AD domain controler:
domain\groupname
CREATE LOGIN [domain\groupname] FROM WINDOWS
CREATE USER [domain\groupname] FOR LOGIN [domain\groupname]
select is_member('domain\groupname')
In PaaS I needed to create users for groups and reference to them without domain, as I had to switch to use integrated Azure Active Directory:
groupname
CREATE USER [groupname] FROM EXTERNAL PROVIDER
select is_member('groupname')
Second thing was something that looks like a bug (currently have open ticket with MS)
While IS_MEMBER worked correctly for test users in PaaS, I had problem with Admin Accounts. Taking for example user1 who was in [adminGroup] on DataDB IS_MEMBER always returned 0, no matter which AAD group I was checking. What was more strange, when changing to Master db IS_MEMBER worked fine there for any AAD group!
(offtopic: on master db users where also created to avoid problem with SSMS default option to reach to master DB first, and without addition there non-admin users would get connection error (so either users needed to specify in SSMS connection options destination DB, or be added with connect rights to master DB))
It turned out that having [adminGroup] being set up as Azure Active Directory Admin on Azure SQL Server was reason for this strange behavior. I changed in our code to use [adminGroup2] for RLS, and removed admins (users) in AAD from [adminGroup] which was kept as SQL Server Azure Active Directory Admin. And with just this change IS_MEMEBER started to work just as expected!
Other workaround I have found here, would be to use IS_MEMBER('db_owner') for admin group (as those default roles checks are working fine, but this needs additional changes to security config and would not solve problems for other cases then admins, but actually looks like this is the only problem here.
Waiting to hear response from MS about this bug, as I doubt that that would be implemented by design...
Best Answer
So, if user1 has group in common with user2, user2 should be able to see that row, yes?
One way would be to enumerate all the groups that user1 is in, and all the groups user2 is in, and see if there's a union
See How to get all roles that a user is a member of (including inherited roles)? to get all groups a user belongs to
See also pg_has_role https://www.postgresql.org/docs/9.6/static/functions-info.html