Applying Row Security for a Database

Row level security on a database table can be achieved in two ways:

  • Use the MI_User or MI_Roles functions in a Named View to filter out the rows that a user can see.
  • Use the PreSQL feature to pass user information to the session context in the database. Row filtering is then applied in the database with the user information in the session context.

To apply row security on a database the tables queried must have a column for the Spectrum users who can access each row. A database query refers to the column to determine if a user or role has access to a row of information. A table would have a column of Spectrum user names, or a column of role names, or have both columns. Users and roles are created in the Management Console and given access to the database (named resource) in Spectrum Spatial™ Manager.

We recommend creating a separate database connection in Spectrum Spatial™ Manager for row security. To setup row security for a database, in Spectrum Spatial™ Manager:

  1. Create a view in the database (called a database view).
  2. Create a connection to the database with a PreSQL property that adds user information in the session context.
  3. Create a named table to the database view (created in step 1).

Creating a Database View

To create a view in the database, you must be the database administrator or have database admin rights and use a SELECT clause to filter the user or role column. The following statements are examples. The syntax may vary depending on the database version.

MS SQLServer

SELECT CONVERT(varchar(100), SESSION_CONTEXT(N'loginuser')) as UserName

Where

  • loginuser is a database token used to create the database connection (in the next step).

Oracle

SELECT SYS_CONTEXT('userenv', 'client_identifier') AS UserName FROM dual

Where

  • client_identifier is a database token used to create the database connection (in the next step).

PostGIS

select current_setting('spectrum.loginuser') as UserName

Where

  • spectrum.loginuser is a database token used to create the database connection (in the next step).

Example

The following is an Oracle database example that creates a database view:

CREATE OR REPLACE VIEW PREPOST_VIEW_TEST AS
SELECT *
FROM "YOURDATABASE"."TESTTABLE"
WHERE OWNER =
(SELECT SYS_CONTEXT('userenv','client_identifier') FROM DUAL)
OR 
(SELECT SYS_CONTEXT('userenv','client_identifier') FROM DUAL) = 'admin'

Where:

  • OWNER is the column to filter. This column has the user names or the role names that have permission to each row of information.
  • YOURDATABASE is your database name.
  • TESTTABLE is a table with a user name or a role name column to filter against.

The first SELECT clause filters the user or role column (called OWNER).

The second SELECT clause returns all the rows when the query is performed by an administrator (admin).

Creating a Connection to the Database

You must be an administrator (admin or spatial-admin) to create a connection to the database in Spectrum Spatial™ Manager.

To create a database connection if one does not already exist:

  1. In Spectrum Spatial™ Manager, go to the folder where you wish to store the named connection for the database.
  2. Click the Create button and then click Connection from the drop-down menu.
  3. On the Create Connection screen:
    1. In the Connection Name field, type a name for the new connection.
    2. Select Database, as the connection type.
    3. In the Connection URL field, type the URL to connect to the JDBC database.
    4. Optionally enter the database username and password for the connection. If left blank, the username and password are supplied when making database requests and queries.
    5. Under Properties, in the Key field type PreSQL.
    6. In the Value field, paste the statement provided below for your database.
  4. Click Test to ensure the connection is successful.
  5. Click Save.

Value Field Entries by Database Type

There are two types of tokens for the Value field statement. Before sending the statement to the database, the token gets replaced with a value. Tokens are case sensitive and must be as shown here:

  • {{SPECTRUM_USER}} this token gets replaced with the spectrum login user name.
  • {{SPECTRUM_ROLES}} this token gets replaced with the spectrum role list that the login user has.

The following statements are examples. The syntax may vary depending on the database version.

MS SQLServer

EXEC sys.sp_set_session_context @key=N'loginuser', @value='{{SPECTRUM_USER}}';

Where

  • loginuser is the database token set when creating the database view.

Oracle

CALL DBMS_SESSION.set_identifier('{{SPECTRUM_USER}}');

PostGIS

select set_config('spectrum.loginuser', '{{SPECTRUM_USER}}', false);

Where

  • spectrum.loginuser is the database token that was set when creating the database view.

Creating a Named Table to the Database View

The view created displays in the list of available database views in Spectrum Spatial™ Manager. For information about how to create a database view, see the section titled Creating a Table.