Customizing A Broker SQL Pattern File

Top  Previous  Next

If there is no Broker SQL Pattern file available for your database system yet or you want to maintain additional information for the users the you can develop your own bsp file.

Here are the steps:

 

1. Copy one of the existing bsp files as an example

Go to folder {APPLICATION_PATH}/WEB-INF/MM-INF/config/security/bsp
Copy, e.g. "mysql.bsp" file and rename to e.g. "new.bsp". 

 

2. Create a new Broker Definition using securityBroker element in a configuration file.

  <securityBroker name="NewBroker">
    <class>org.moremotion.mmcomp.security.RelDBSecurityBroker</class>
    <rootUserName>sa</rootUserName>
    <adminRoleName>SYS_ADM</adminRoleName>
    <param name="sqlPatternFile">newbsp.bsp</param>
    <param name="debug">false</param>
  </securityBroker>

3.Open the "new.bsp" file with a text editor and modify the SQL Patterns as required.
 
4.Specify the name of the new broker in the securityDomain configuration element as follows

  <securityDomain name="main">
    <broker>NewBroker</broker>
    ...
  </securityDomain>

 

 

Modifying the SQL Patterns in the bsp File

Although the functionality provided by the SQL Patterns are obvious and therefore can easily be customized to add extra user attributes or changed to create a new bsp file for a new database system, it still worth to mention about some points:

Checking the existence of the security tables

 

  #Check Tables

  {

    SHOW TABLES LIKE 'users';

  }

 

The SQL Defined with this pattern is executed to check whether the security tables already exist. If the SQL used here returns a result with at least one record, the broker assumes that the security tables exist; Otherwise it will use the '#Create Tables' SQL to create the tables and afterwards will use '#Fill In Initial Data' SQL to fill in the initial data to the security tables.

 

Auto Increment IDs vs. Sequence tables

Most of the database systems support automatically incremented ID columns but some of them, e.g. Oracle not. See below the two different implementations of the '#Insert CheckPoint' SQL Pattern for two database systems.

 

MySQL Version

 

#Insert CheckPoint

{

  INSERT INTO cps (NAME, DESCR)

    VALUES('@{cpname}', '@{cpdescr}')

}

 

 

Oracle Version

 

#Insert CheckPoint

{

  INSERT INTO scott.cps (ID, NAME, DESCR)

    VALUES( scott.cps_id_seq.NEXTVAL, '@{cpname}', '@{cpdescr}')

}

 

 

Adding Extra User Attributes to the Users

If you want to maintain extra information for the users you should modify the following SQL Patterns:

 

1.'#Create Tables' SQL (CREATE TABLE users statement)
2.'#Fill In Initial Data' SQL (INSERT INTO users statement)
3.'#Update User' SQL
4.'#Insert User' SQL
5.'#Get User Info By Name' SQL
6.'#Get User Info By Email' SQL
7.'#Get All Users' SQL
8.'#Search Users' SQL

 

Example:

Assume that we want to maintain an additional field called 'DEPT'. The modified SQL Patterns would be as follows.

 

  #Create Tables

  {

    ...

    ...

    CREATE TABLE users (

      ID int(11) NOT NULL auto_increment,

      NAME varchar(50) NOT NULL default '',

      PASSWORD varchar(50) NOT NULL default '',

      FULLNAME varchar(80) NOT NULL default '',

      EMAIL varchar(128) NOT NULL default '',

      DISABLED varchar(5) NOT NULL default '',

      DEPT int(8) default 0,

      PRIMARY KEY (ID),

      KEY NAME (NAME)

    );

    ...

  }

 

  #Fill In Initial Data

  {

    ...

    INSERT INTO users (NAME, PASSWORD, FULLNAME, DEPT

      VALUES('sa', '6D81B7AA5F5C76AC475E', 'System Administrator', 0);

    ...

  }

 

  #Update User

  {

    UPDATE users SET

      PASSWORD = '@{password}',

      FULLNAME = '@{fullname}',

      EMAIL    = '@{email}',

      DISABLED = '@{disabled}',

      DEPT     = '@{DEPT}'

    WHERE NAME = '@{username}'

  }

 

  #Insert User

  {

    INSERT INTO users

      (NAME, PASSWORD, FULLNAME, EMAIL, DISABLED, DEPT)

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

  }

 

  #Get User Info By Name

  {

    SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

           EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

      FROM users WHERE NAME = '@{username}'

  }

 

  #Get User Info By Email

  {

    SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

           EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

      FROM users WHERE EMAIL = '@{email}'

  }

 

  #Get All Users

  {

    SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

           EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

      FROM users

  }

 

  #Search Users

  {

    SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME, 

           EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

    FROM users

    WHERE

      (NAME     = '' OR NAME     LIKE '%@{username}%') AND

      (FULLNAME = '' OR FULLNAME LIKE '%@{fullname}%') AND

      (EMAIL    = '' OR EMAIL    LIKE '%@{email}%'   ) AND

      (DISABLED = '' OR DISABLED LIKE '%@{disabled}%') AND

      (DEPT     = '' OR DEPT     =    '@{DEPT}')

  }