Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Do we want to implement built-in row-level security? Currently, users have to define views and use stored functions. See here for example:
https://mariadb.com/resources/blog/protect-your-data-row-level-security-in-mariadb-10-0/
Competitors' Row-level Security Functionality
The competitor's row-level security options are outlined below.
PostgreSQL
PostgreSQL has had built-in row-level security since PostgreSQL 9.5. It is implemented via ALTER TABLE ... ENABLE ROW LEVEL SECURITY and the CREATE POLICY, ALTER POLICY, and DROP POLICY statements.
https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html
i.e.:
CREATE TABLE accounts (manager text, company text, contact_email text);
|
|
|
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
|
|
|
CREATE POLICY account_managers ON accounts TO managers
|
USING (manager = current_user);
|
Oracle Database
Oracle has also had built-in row-level security since Oracle Database 9i. It appears to be implemented as a set of built-in functions.
https://www.oracle.com/database/technologies/security/label-security.html
https://docs.oracle.com/database/121/OLSAG/intro.htm#OLSAG001
http://mohamednabeel.blogspot.com/2014/08/oracle-label-security-12c-quick-start.html
i.e.:
SQL> conn lbacsys/password@cdb1_pdb1
|
Connected.
|
SQL> BEGIN
|
2 -- Create OLS Policy
|
3 -- Notice that the default_options is set to no_control to disable the policy
|
4 -- in order add labels to the existing data items
|
5 SA_SYSDBA.CREATE_POLICY(
|
6 policy_name => 'ols_pol1',
|
7 column_name => 'lb_col',
|
8 default_options => 'no_control'
|
9 );
|
10
|
11 -- Create label component levels
|
12 -- TOP_SECRET has the highest level of access
|
13 SA_COMPONENTS.CREATE_LEVEL(
|
14 policy_name => 'ols_pol1',
|
15 level_num => 4,
|
16 short_name => 'TS',
|
17 long_name => 'top_secret'
|
18 );
|
19
|
20
|
21 SA_COMPONENTS.CREATE_LEVEL(
|
22 policy_name => 'ols_pol1',
|
23 level_num => 3,
|
24 short_name => 'S',
|
25 long_name => 'secret'
|
26 );
|
27
|
28 SA_COMPONENTS.CREATE_LEVEL(
|
29 policy_name => 'ols_pol1',
|
30 level_num => 2,
|
31 short_name => 'C',
|
32 long_name => 'confidential'
|
33 );
|
34
|
35 SA_COMPONENTS.CREATE_LEVEL(
|
36 policy_name => 'ols_pol1',
|
37 level_num => 1,
|
38 short_name => 'UC',
|
39 long_name => 'unclassified'
|
40 );
|
41
|
42 -- Create data labels
|
43 SA_LABEL_ADMIN.CREATE_LABEL(
|
44 policy_name => 'ols_pol1',
|
45 label_tag => 40,
|
46 label_value => 'TS',
|
47 data_label => TRUE
|
48 );
|
49
|
50 SA_LABEL_ADMIN.CREATE_LABEL(
|
51 policy_name => 'ols_pol1',
|
52 label_tag => 30,
|
53 label_value => 'S',
|
54 data_label => TRUE
|
55 );
|
56
|
57 SA_LABEL_ADMIN.CREATE_LABEL(
|
58 policy_name => 'ols_pol1',
|
59 label_tag => 20,
|
60 label_value => 'C',
|
61 data_label => TRUE
|
62 );
|
63
|
64 SA_LABEL_ADMIN.CREATE_LABEL(
|
65 policy_name => 'ols_pol1',
|
66 label_tag => 10,
|
67 label_value => 'UC',
|
68 data_label => TRUE
|
69 );
|
70
|
71 -- Apply access_pol policy on table gov.flight
|
72 SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
|
73 policy_name => 'ols_pol1',
|
74 schema_name => 'gov',
|
75 table_name => 'flight',
|
76 table_options => null,
|
77 label_function => null,
|
78 predicate => null
|
79 );
|
80
|
81 -- Add user authorizations (i.e. clearance levels)
|
82 SA_USER_ADMIN.SET_LEVELS(
|
83 policy_name => 'ols_pol1',
|
84 user_name => 'bob',
|
85 max_level => 'S',
|
86 min_level => 'UC',
|
87 def_level => 'S',
|
88 row_level => 'S'
|
89 );
|
90
|
91 SA_USER_ADMIN.SET_LEVELS(
|
92 policy_name => 'ols_pol1',
|
93 user_name => 'tim',
|
94 max_level => 'UC',
|
95 min_level => 'UC',
|
96 def_level => 'UC',
|
97 row_level => 'UC'
|
98 );
|
99 END;
|
100 /
|
Microsoft SQL Server
Microsoft SQL Server has had built-in row-level security since SQL Server 2016. It is implemented via CREATE SECURITY POLICY, ALTER SECURITY POLICY, and the DROP SECURITY POLICY statements.
i.e.:
CREATE SECURITY POLICY rls.SecPol
|
ADD FILTER PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales,
|
ADD BLOCK PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT;
|