Exasol already offers a Github Project to implement Row Level Security using Virtual Schemas. This is the recommended method of creating Row Level Security. To learn more about this, see here: https://github.com/exasol/row-level-security
If you want to implement Column Level Security in addition to Row Level Security, this example shows you how this can work. Please note that the example below is not the officially support row level security package from Github. Instead, this is a custom example showing how you could create your own Row and Column Level Security. It consists of 4 parts:
Please note that these scripts will require adaptation to work with your exact use case. This is just an example of how it can be done.
In this example, let's pretend that we work for a bank that has several different institutes. An employee of one institute should not be able to see the data in other institutes. This data is stored in the column INSTITUTE. This is called Row Level Security as the employee of one institute can only see the rows that correspond to his institute.
At the same time, there are also bank employees that are not working for a particular institute, such as in IT or HR. However, these users should not be able to see secured fields, like account balances, etc. This is called Column Level Security because the users should only be able to see information on certain columns. Our three users are summarized below:
To prepare for this demo, you will need to download these 2 files:
Open the SQL File and change the IMPORT statement so that it matches your path. Within this file, we will create our users, table, virtual schema adapter script, roles, and import our data. (Lines 1-80)
We are using a Virtual Schema Adapter called "RLS_ADAPTER". This adapter is specifically created to work with the tables which we are creating, namely ADAPTER_SCHEMA.RESTRICTION_ROWS and ADAPTER_SCHEMA.RESTRICTION_COLUMNS. All of our RLS and CLS Information is stored in this table. When querying the virtual schema, this information is read to determine which data to return. Once we create our Adapter script (found in the SQL file above) and connection back to the database, we can create our virtual schema. This information is also in lines 81-230 of our SQL file.
CREATE VIRTUAL SCHEMA SECURED_BANK USING adapter_schema.rls_adapter with table_schema='OUR_BANK' META_CONNECTION='SELF_CONNECTION';
Once our Virtual Schema is created, we need to assign the users roles which match their privileges. We've created these Roles to match their permission policies:
Based on the permissions from before, we have this alignment:
To complete the Setup, we just need to create these roles, assign them to the users, and insert the details of the permissions into our tables RESTRICTION_ROWS and RESTRICTION_COLUMNS. Note: This table includes the information that the user SHOULD NOT see. Finally, we have to grant the SELECT privileges on the Virtual Schema to our 3 users. To create this, you can run lines 240-303 of the SQL file.
Now that the Virtual Schema is set up, our 3 users can begin running queries against it. Behind the scenes, the database is connecting back to itself and modifying the query that is being sent to the base table. As a user, you don't have to worry about these details. They are done automatically.
To test this, you can impersonate any of these users to see what information they see. Steve, for example, will only see limited information in Institute 1, while the other 2 will see limited information for the entire company.
-- Regional manager impersonate MONICA; select current_user; select count(*) from SECURED_BANK.customer; select * from SECURED_BANK.customer where last_name like 'St%'; -- Institute_1 impersonate STEVE; select count(*) from SECURED_BANK.customer; select * from SECURED_BANK.customer where last_name like 'St%'; -- IT Department impersonate JAKE; select count(*) from SECURED_BANK.customer; select * from SECURED_BANK.customer where last_name like 'St%';
Since everything is role-based, this framework is extremely flexible. It is very easy to add further restrictions or new users to this concept. For example, if I want to add a new user to this concept, I only need to grant the accompanying roles to our new user:
----------------- -- new Intern ----------------- impersonate SYS; create user "JESSICA" identified by "my_password"; grant create session to JESSICA; -- setup RLS and CLS grant RLS_INSTITUTE_1 to JESSICA; grant CLS_DETAILS_SECURED to JESSICA; grant select on SECURED_BANK to JESSICA; -- impersonation (only for demo purposes) grant impersonation on JESSICA to IMP; grant IMP to JESSICA; -- Try it out impersonate JESSICA; select count(*) from SECURED_BANK.customer; select * from SECURED_BANK.customer where last_name like 'St%' ;
Or if I need to add more restrictions, such as limiting the visibility of e-mail addresses, I can just add a new entry into our RESTRICTIONS_COLUMNS table:
INSERT INTO ADAPTER_SCHEMA.RESTRICTIONS_COLUMNS VALUES ('CLS_DETAILS_SECURED','CUSTOMER','EMAIL');
Now, the E-mail column is also hidden for all users with the CLS_DETAILS_SECURED role.
With this framework, you can specify as many restrictions on as many tables as you want. You can even expand the adapter to script to fit your exact use case.