Database Row Level Security

Overview

Row level security aims at limiting access of certain users to the data. This feature is set up within the database and also by IJC admin in IJC Desktop. You'll need to use the IJC support for this feature only if you're using Instant JChem with a database account shared by multiple IJC users. For example having one technical DB user and doing the actual authentication through an LDAP server configured within the IJC security policy.

If you're using IJC with a database account per user or per user role, you don't need to configure this feature within IJC.

If you are interested in a different approach in limiting the user's access to the data, please consult documentation here.

Setting the security up - Script Hook for connection

IJC manage a pool of connections. Each time a connection is taken from the pool to run some DML or DDL operation activation script is run. After the operation completes and before connection is returned to the pool deactivation script is run.

The two scripts can be managed via IJC Desktop in Schema Editor in Security tab.

images/download/attachments/42174158/screenWindows.png

Example scripts

For a full tutorial regarding how to implement a policy with a database session-based application context, see here.

In the example below, the activation script takes user name as a parameter. Question mark placeholder is used within the UI for the user name. E.g. for activation script call ijc_ctx_pkg.set_user(?) procedure ijc_ctx_pkg.set_user(?) will be called and IJC will pass it user name as parameter. Each part of the script is explained in comments inside of the script.

Script example for Oracle DB:

set serveroutput on
-- creates the `ijc_ctx' application context
CREATE OR REPLACE CONTEXT ijc_ctx USING ijc_ctx_pkg;
/
-- Creates PL/SQL package.
CREATE OR REPLACE PACKAGE ijc_ctx_pkg IS
PROCEDURE set_user(ijc_username VARCHAR2);
PROCEDURE clear_user;
END;
/
CREATE OR REPLACE PACKAGE BODY ijc_ctx_pkg IS
-- Will be called by Instant JChem, passing logged user as `ijc_username'
-- parameter. The user name is then stored in database session-based
-- application context and might be later picked up by Oracle Virtual Private
-- Database policy.
--
-- See `get_pubchem_mols' below.
PROCEDURE set_user(ijc_username VARCHAR2) IS
user_missing EXCEPTION;
BEGIN
IF ijc_username IS NULL THEN
RAISE user_missing;
ELSE
DBMS_SESSION.SET_CONTEXT('ijc_ctx', 'ijc_username', ijc_username);
END IF;
END set_user;
-- Will be called by Instant JChem before connection is returned to IJC pool.
-- Clears the context set by `set_user' procedure.
PROCEDURE clear_user IS
BEGIN
DBMS_SESSION.CLEAR_CONTEXT('ijc_ctx', 'ijc_username');
END clear_user;
END;
/
-- Trivial policy implementation which creates and returns a WHERE statement
-- for `pubchem_demo' table based on `ijc_username' set in the session context.
--
-- `chemaxon' user has no restrictions. All other users are restricted with
-- 'CD_ID < 5'.
CREATE OR REPLACE FUNCTION get_pubchem_mols(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
AS
pubchem_pred VARCHAR2 (400);
BEGIN
IF LOWER(SYS_CONTEXT('ijc_ctx','ijc_username')) = 'chemaxon' THEN
pubchem_pred := NULL;
ELSE
pubchem_pred := 'CD_ID < 5';
END IF;
RETURN pubchem_pred;
END;
/
-- Ensure no policy is present before proceeding to next statement.
BEGIN
DBMS_RLS.DROP_POLICY('MK', 'pubchem_demo', 'pubchem_policy');
END;
/
-- Set up new security policy called `pubchem_policy' for `pubchem_demo' table.
-- Restriction applies to SELECT statement only.
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'MK',
object_name => 'pubchem_demo',
policy_name => 'pubchem_policy',
function_schema => 'MK',
policy_function => 'get_pubchem_mols',
statement_types => 'select');
END;
/
-- For debugging purposes.
CREATE OR REPLACE PROCEDURE dump_current_ijc_username
AS
BEGIN
dbms_output.put_line(SYS_CONTEXT('ijc_ctx','ijc_username'));
END;
/

Similarly you can emulate such functionality on other databases as well (for example in MySQL)

As an example, for the Activation script hook (based on the example above), in the Schema Security settings in IJC, the admin can call

call ijc_ctx_pkg.set_user(?)

and for the Deactivation script hook, it can be

call ijc_ctx_pkg.clear_user()

Performance

Defining the activation and deactivation script and calling them every time a database connection is retrieved from and returned to the connection pool may put too much stress on your database under certain circumstances. The exact number of calls will depend on the number of users that you have and their activity. However, if you find yourself in a situation where you would like to reduce the number of these calls you can tell IJC to optimize the number of the activation and deactivation calls based on users activity. This is done by using a special JVM property 'db.connection.activation.optimize'. It's detailed description is here. If you set this property the infrastructure behind IJC will stop calling your deactivation script and will only call your activation script when the user using the database connection is different from the user who used that same connection last time.

This property will have effect in both the desktop IJC application and Plexus web application. However, the effect in the multi-user Plexus will not be that large as in the single-user IJC application.