Relational Database Security Broker

 Top  Previous  Next

The Relational Database Security Broker can be used to access and maintain the security information stored in all types of relational databases as long as there is proper JDBC driver for the database system.

A RelDB Security Broker is introduced to the system with a securityBroker configuration element in a global configuration file as follows.

  <securityBroker name="OracleBroker">
    <class>org.moremotion.mmcomp.security.RelDBSecurityBroker</class>
    <rootUserName>sa</rootUserName>
    <adminRoleName>SYS_ADM</adminRoleName>
    <param name="sqlPatternFile"></param>
    <param name="conn"></param>
    <param name="schema"></param>
    <param name="tablespace"></param>
    <param name="debug"></param>
  </securityBroker>

Nested Elements

class

The class name of the broker must be org.moremotion.security.broker.SecurityBroker.

rootUserName

The name of the root user.

adminRoleName

The name of the administrator role.

sqlPatternFile

The name of the SQL Pattern File. See "Broker SQL Pattern Files" below.

conn

The name of a database connection definition made with jdbcConnection element.

schema

The name of the database schema if applicable. Usually applicable for Oracle.

tablespace

The name of the table space if applicable. Usually applicable for Oracle.

debug

The debugging level of the broker class. Give a value between 0 and 3;

 

Although database systems mostly conform to SQL'92 standards still there exists vendor specific implementations that makes it difficult to process them in a standard way. Therefore the RelDB Security Broker uses SQL Patterns to solve the differences between the database systems.

Using SQL patterns provides a flexible way of accessing the stored security information.

Broker SQL Pattern Files

The available SQL Pattern files are located under {APPLICATION_PATH}/WEB-INF/MM-INF/config/security/bsp directory.

derby.bsp

Broker SQL Pattern file for Apache Derby Database

mysql.bsp

Broker SQL Pattern file for MySQL Database

sqlserver.bsp

Broker SQL Pattern file for SQL Server Database

oracle.bsp

Broker SQL Pattern file for Oracle Database

postgresql.bsp

Broker SQL Pattern file for Postgre SQL Database

 

How a BSP File Structured?

BSP files contains a standard set of SQL Patterns that are identified by Pattern Names.

For example when the RelDB Security Broker wants to retrieve the information of a check point from the database it uses the "#Get CheckPoint Info By Name" pattern. The @{} symbols in the SQL patterns are resolved with their values before the SQL is executed through the JDBC driver.

 

  #Get CheckPoint Info By Name

  {

    SELECT ID AS ID, NAME AS NAME, DESCR AS DESCR

      FROM cps WHERE NAME = '@{cpname}'

  }

 

 

  #Insert User

  {

    INSERT INTO users

      (NAME, PASSWORD, FULLNAME, EMAIL, DISABLED)

      VALUES('@{username}', '@{password}', '@{fullname}', '@{email}', '@{disabled}')

  }

 

The RelDB Security Broker has no idea of which tables and which columns are accessed. It only knows the type of the SQL query; It is either an update query that updates the database or a select query that produces a result set.

The @{} Symbols that can be used in SQL Patterns

@{} Symbol

Description

@{subdomain}

The name of the sub domain set with SetSubdomain() SML instruction.

 

 

@{userid}

The Id of the user (generally the row id of the users table)

@{username}

The name of the user

@{password}

The password of the user

@{fullname}

The full name of the user

@{email}

The e-mail of the user

@{disabled}

True if the user account disabled, false otherwise

 

 

@{roleid}

The id of the role (generally the row id of the roles table)

@{rolename}

The name of the role

@{roledescr}

The description of the role

 

 

@{cpid}

The id of the check point (generally the row id of the check points table)

@{cpname}

The name of the check point

@{cpdescr}

The description of the check point

@{broker-parameter}

Any parameter defined with param sub-element of the securityBroker element

 

Example:

  <securityBroker name="NewSequrityBroker">
    <class>org.moremotion.mmcomp.security.RelDBSecurityBroker</class>

    <param name="myprm">myprm value</param>

  </securityBroker>

 

Usage:

@{myprm}
 

@{broker-variable}

The broker variables set with SetVar() SML instruction

 

See Customizing a Broker SQL Pattern File