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
Best Answer
Pros
Row level security (RLS) reduces your application development complexity. Without RLS, you'd generally create filters with your queries or through ORM, or pass on filtering criteria to your procs. Once you have RLS in place, user1 sees what you desire user1 to see without changing your application much.
With RLS, security is at the DB level. If another application was reading data in a different language from a different platform, RLS still allows user1 to see what you want user1 to see. If someone is trying to run/test SQL queries to SSMS, RLS shows data according to your setup.
Your DB backup and restore will keep RLS intact.
Cons
The disadvantage is more DB-centric work and small amount of performance penalty. If you have web developers who aren't strong on the DB side, troubleshooting RLS might take them a bit of time. On the contrary, if application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.
As far as the overheads of row level security go, here's an article that details some challenges: https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ and https://www.mssqltips.com/sqlservertip/4778/performance-impact-of-sql-server-2016-rowlevel-security/.
To use RLS or not?
If you are comfortable with RLS, I'd recommend you try it out. Microsoft has listed some RLS-related best practices here: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017.
If your DB is well protected and access to data is through application layer only, you can opt to build filtering within your application layer. It's not a lot of work to do security checks at the application layer AND use RLS. There isn't a one size fits all answer. For most simpler applications, I tend to use the application layer for filtering since it has served me well with auditing/logging/debugging.