Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27301

Implement built-in row-level security

    XMLWordPrintable

    Details

      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/

      https://mariadb.com/resources/blog/protect-your-data-2-a-row-level-security-walkthrough-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.

      https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

      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;  
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.