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.

Erroneous scripts

If a script contains errors and as such fails during the running the error is logged via JavaTM 2 platform logging API with SEVERE level. This means that:

IJC Desktop shows dialog with error message Connect Backend will log error into a log file

{info} WARNING Even if this error happens, operations being performed on the connection are not affected. It's up to an Oracle admin to ensure that database behaves in desired way - e.g. no operations are allowed when activation script was not run.

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()

Sample Virtual Private Database script

Oracle - Can be found in here.

A similar feature can be emulated within other databases as well - see here.

IJC meta data

The tables prefixed with IJCmust not be affected by the security policy. Since at some stage during IJC schema-init activation and deactivation scripts begin to be called for each connection as described above it must be ensured that invocation of scripts themself does not have any impact on table prefixed with IJC. I.e. this must be ensured by DBA on the database level.

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.

Limitation

In IJC which connects directly to the database, a potential attacker can escalate his privileges to do anything the database user can do. How to perform this is not immediately obvious, so this feature can work well for most end users.