Problem:

When trying to access mainframe locations using SQLWizard or Migrate, users can invalidate their mainframe userid by trying to log on with a bad password 3 times in a row.  

This can be resolved by using Global Security in the SQL Option Server.

Resolution:

Abstract

The Micro Focus SQL Option Server provides access to data residing in z/OS DB2 regions and local SQL Option locations.  A valid z/OS user ID and password are required for access to z/OS DB2 regions.  Ideally, a single user ID and password should be used for access the SQL Option Server whether accessing z/OS DB2 regions or local SQL Option locations.

The maintenance of the security information on the SQL Option Server should be minimized as much as possible.  When the password changes on the z/OS environment, the SQL Option Server should immediately accept the new password.  Users denied access to the z/OS environment should also be denied access to the SQL Option Server.

Introduction

Micro Focus SQL Option Server Global Security allows a single location to be identified for validation of access authorization.  Whenever a user attempts to login to any SQL Option Server configured to use global security, the user ID and password are passed to the global security location for validation.  When the SQL Option Server has been configured to communicate with a z/OS DB2 region, the z/OS DB2 region may be used as the  global security location.  When a z/OS DB2 region is the global security location, all login requests processed by the SQL Option Server will be validated by the z/OS DB2 region.  Any change to security on the z/OS system will be visible on the SQL Option during the next login attempt.

The selection of the global security location depends on the z/OS environment in use with the SQL Option Server.  For the majority of cases, a specific z/OS DB2 region will be configured as the global security location.  However, the SQL Option Server allows for the selection of the "current" location as the global security location.  This "current" location selection is useful for situations where multiple z/OS DB2 regions with multiple security profiles are in effect.  For instance, in the situation where a single SQL Option Server user will connect to DB2 regions on different z/OS platforms, and where the user ID and password combinations are different among the z/OS DB2 regions, the selection of the "current" location allows for the SQL Option Server to validate security criteria against the z/OS DB2 region to which the user is connecting.

Preparation for Enabling Global Security

Prior to configuring the Micro Focus SQL Option Server for global security, at least one z/OS user should be identified as a SQL Option Server "super user".  Super user authority allows a SQL Option Server to perform any operation.  Once global security has been activated, the SQL Option Server "install" user will no longer be able to access the SQL Option Server since the "install" user will fail validation by the z/OS DB2 region.

A user may be created either through the SQLWizard administrative dialogs or with the CREATE USER SQL statement.  To create the user with the SQLWizard, open the "Add/Modify User" dialog by selecting "Admin ? Users" from the SQLWizard menu.  Enter the z/OS user ID in the dialogs "User ID" text box.  If a password will be stored in the SQL Option Server for this user, fill in the "Password" and "Verification" text boxes with the password to be used when global security is not enabled.  If you would like the user to not have a password when global security is not enabled, leave the "Password" and "Verification" text boxes empty and clear the check from the "Password Required" check box.  Ensure that the "Access Authority" check box is checked.  "Access Authority" gives the user the right to access the SQL Option Server.  Finally, select the "Super User" radio button in the "User Type" group.

The user may also be created via SQL syntax.  The syntax for the SQL Option Server CREATE USER statement is as follows:

CREATE USER UserName TYPE [ USER | SUPERUSER ] PASSWORD Password REQUIRED [ YES | NO ] [ options ]

where options may be any of the following:

ACCESS AUTHORITY [ YES | NO ]

COMMENT 'string'

For example, a user named DSNUSER1 may be created without a password as follows:

CREATE USER DSNUSER1 TYPE SUPERUSER PASSWORD NONE REQUIRED NO;

A user named ZOSUSER may be created with the password "mypass1" as follows;

CREATE USER ZOSUSER TYPE SUPERUSER PASSWORD mypass1 REQUIRED YES;

Global Security Configuration

Micro Focus SQL Option global security is enabled using the "General" tab of the server configuration utility.  Global Security requires server side security to be enabled.  

After starting the SQL Option Server configuration dialog, select the "General" tab.  In the "Security" group, check the "Server Security" check box to enable the "Global Security" check box.  Next check the "Global Security" check box to enable the "Location Name" text box.  Within the " Location Name" text box, enter either a valid z/OS DB2 region configured via the SQL Option XDB Link dialog, or enter an asterisk character to indicate that the current location should always be used to validate the security information.

If an asterisk is entered as the global security location, whichever location the client side software connects to will be the location that is used for security validation.  This set up is useful when there are multiple z/OS machines to be used by the SQL Option.  The SQL Option user can select the location to connect to and use whichever user ID and password combination is appropriate.  

Once "Global Security" has been enabled in the configuration dialog, the SQL Option Server will have to be stopped and restarted for the changes to take effect.

Note: Server side security requires the SQL Option Client Options dialog to have "client security" enabled on the SQL Option Client Options dialog's "Security" tab.  Without client side security enabled, a SQL Option server running with server side security enabled will automatically reject a connection request from a client.  Before enabling global security, ensure that all workstations configured to use the SQL Option Server have had their "client security" enabled.

SQL Option User IDs with Global Security Enabled

Global Security eases administration of  the Micro Focus SQL Option Server by mostly eliminating the need to create users on the SQL Option Server.  Users with a valid z/OS user ID and password will be allowed access to the SQL Option Server.  SQL Option Server users will still need to be granted rights to see the locations serviced by the SQL Option Server and the data within those locations.

Users will need rights to the SQL Option Server SYSTEM location in order to see the list of locations available in the SQLWizard Catalog Browser.  The MFSQL\\BIN directory contains the scripts XPGRTSYS.SQL and XUGRTSYS.SQL in order to grant privileges to tables such as SYSTEM.SYSXDB.SYSLOCALS, SYSTEM.SYSIBM.SYSLOCATIONS, and SYSTEM.SYSXDB.SYSGATEWAY which are used by the SQLWizard catalog browser.   XPGRTSYS.SQL grants SELECT rights to PUBLIC while XUGRTSYS.SQL grants SELECT rights to a specific user.  Both scripts need to be run from within SQLWizard since they are parameterized using the SQLWizard syntax.  Without establishing these rights, the user will only see the location to which they connected to in the catalog browser.

When the primary location in use is a z/OS DB2 location, the user will have whatever rights have previously been assigned to the user by the DBA.  Once security has been validated, the SQL Option Server will connect the user to their z/OS DB2 region and the region will take care of all security issues.

When the primary location in use is a local SQL Option Server location, the user may still not have rights to access objects within the location.  In this scenario, users will need rights granted to them.  To replicate the behavior of the SQL Option Server Super User functionality for local locations, users may be granted SYSADM rights as follows:

GRANT SYSADM TO DSNUSR99;

The above grant establishes rights only for the current SQL Option Server local location to user DSNUSR99.  If the user requires rights in multiple SQL Option Server locations, the GRANT statement will need to repeated for each location.  The SQL Option Server also supports the granting of rights at the object level as well.  Therefore, it is possible to limit access to SQL Option Server objects in a similar fashion as z/OS DB2 locations.  However, SQL Option Server users will typically be granted SYSADM authority since this is their test environment.

One exception to the above premise of not having to create users on the SQL Option Server is the emulation of z/OS DB2 secondary IDs when using local SQL Option Server locations.  In this scenario, the user is given the sum of the rights of all of their secondary authorization IDs.  On the SQL Option Server, secondary authorization IDs are implemented using user groups.  Creating users on the SQL Option Server populates the  SYSTEM.SYSXDB.SYSACFUSERS table.  The user groups are created by populating the  SYSTEM.SYSXDB.SYSACFGROUPS table.  The mapping of users into groups is done using the SYSTEM.SYSXDB.SYSACFMEMBERS table.  Populating these tables may be easily done with the SQLWizard Admin ? Users and Admin ? Groups menu entries.

Summary

Micro Focus SQL Option Global Security creates an environment where the user IDs and passwords are maintained on the z/OS platform and validated by the SQL Option Server via a connection to a z/OS DB2 region.  Global Security should minimize the amount of administration required for the SQL Option Server as well as eliminating situations where the z/OS password is out of sync with the SQL Option password.  Using Global Security should reduce the amount of effort required to maintain the development environment and maximize the time spent enhancing applications.