Customizing A Broker SQL Pattern File |
![]() ![]() ![]() |
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
2. Create a new Broker Definition using securityBroker element in a configuration file.
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
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
Oracle Version
Adding Extra User Attributes to the Users If you want to maintain extra information for the users you should modify the following SQL Patterns:
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}') }
|