StarQuest Technical Documents

Using the DYNAMICRULES Bind Option With StarSQL

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ034

Abstract

StarSQL supports the use of the DYNAMICRULES Bind option for use with DB2 on OS/390 or z/OS systems. The DYNAMICRULES Bind option can be used in situations where dynamic SQL is being used from the client application but enhanced security is required on the DB2 host. With DYNAMICRULES, all incoming dynamic SQL can assume a different security profile before passing through to DB2 for processing.Using this facility, it is possible to restrict the accessibility of tables or restrict the commands used on certain tables by granting privileges to one user ID and then having all dynamic SQL assume that user ID. Any static SQL would continue to use the owner's original user ID.

For example: A user requires update access to DB2 tables through a Visual Basic application and read access through an ad-hoc query tool. Using the DYNAMICRULES Bind option, the user can be prevented from updating the DB2 tables using dynamic SQL statements while still being permitted to update DB2 tables using static SQL statements. The Visual Basic application can perform updates subject to the authorization allowed by the users signon ID, using static SQL. The ad-hoc query tool can perform dynamic SQL statements but can be prevented from modifying the data in DB2.

Solution

Restrictions on the use of DYNAMICRULES

When using the DYNAMICRULES Bind option there are restrictions to the SQL statements that can be used dynamically from the client workstation. The following SQL statements cannot be used: SET CURRENT SQLID, GRANT, REVOKE, ALTER, CREATE, DROP, SET CURRENT PACKAGESET, CONNECT, EXECUTE, PREPARE.

How to Set Up the DYNAMICRULES option

There are three steps to activating the DYNAMICRULES option.

1. Create a special user ID on the DB2 host.
2. Use StarAdmin to create the DynamicRules packages and then manually rebind on the host.
3. Modify the StarSQL data source configuration.

Details

1. Create a user ID on the DB2 host with the restricted set of DB2 privileges that will be used when running dynamic SQL. This userid will be used to run the REBIND command and will need BIND authority on the StarSQL packages.
2. Rebind the StarSQL Packages using StarAdmin with the "Bind DYNAMICRULES Packages" checkbox enabled.

To list the packages used by StarSQL run the following SQL statement:

SELECT NAME, OWNER, DYNAMICRULES FROM SYSIBM.SYSPACKAGE WHERE COLLID='xxxxxxx'

where 'xxxxxxx' is the Package Collection Name from the StarSQL data source name configuration.

The returned data will include packages with names of the format:SWxx0000.

These are the packages that need to be rebound.

The REBIND statement must be run from the DB2 system. Sign-on to ISPF and go to the DB2I screens and select option 5 (BIND/REBIND) and then option 5 on the next screen (REBIND PACKAGES). From this screen enter the Collection ID and Package name, other identifying fields can be left blank, and YES for CHANGE CURRENT DEFAULTS. On the next screen change the DYNAMIC RULES option from SAME to BIND. Hitting Enter will initiate the Rebind process and on screen messages will indicate the success or failure of the process.

The REBIND can also be run as a batch job with the appropriate JCL.

3. Modify the StarSQL data source configuration .

In the Expert Page of the StarSQL data source configuration, set the UseDSCRDBTBL parameter value to No. Click Summary and then OK to save the changes.