In the ideal development world, there would be unlimited computing resources available and creating development and testing environments would be an instantaneous and pain free process. In the real world, resources are limited and constructing environments takes time, so developers often have to share environments. In addition, development teams may evolve during the lifetime of a project, with more than one developer contributing to the same code and having to build and deploy this code.
It is considered good practice to encapsulate data access code into common modules. Service Oriented Architecture (SOA) goes beyond this, encourage business operations to be constructed as reusable services. It is becoming common for much of the “heavy lifting” of data access and the business logic surrounding this to be encapsulated in stored procedures, and these are often referred to as Enterprise Data Services. With more functionality finding its way into stored procedure code, the number of developers working on stored procedure development has increased. It is becoming more common to find groups of developers dedicated to stored procedure development: depending on their background these are often called “database developers” or “application DBAs”.
Supporting these developers effectively in shared environments presents a set of DB2 database security challenges. This assumes that every developer doesn’t just connect as the DB2 instance owner (a very bad idea, and not one which most organisations would allow even against development systems).
This article will explore a methodology for making the support of multi-user development environments easier. It should also form the basis of separation of duties between system, security and database administrators and application developers in support of a production environment.
Issue 1: Privileges to Support Creation of Application Objects
When creating a stored procedure, the privileges required to perform the SQL within the stored procedure must traditionally be given to the user directly rather than through a group. Having to give access directly to ever user involved in development was a huge overhead. Thankfully the introduction of roles provided an answer to that problem. The necessary privileges could be given to the role and then each user could be granted access to the role. This reduced the amount of work required in bringing a new developer onto a team considerably.
Issue 2: Privileges to Perform CREATE OR REPLACE On Application Objects
The introduction of “CREATE OR REPLACE” functionality for stored procedures (also available for many other types of objects) was a huge benefit when working on complex systems. The main benefit of using this functionality is that an individual component within the application can usually be replaced without affecting related components by invalidation of a calling routine when a called routine is dropped and recreated or removing existing execution privileges on the replaced component. However only the owner of an object can replace it, which limits when this could be used in a multi-user environment.
Issue 3: Objects Which Can Only Be Created By SYSADM or SYSCTRL
There are a small number of objects which can only be created with SYSADM or SYSCTRL. The most common of these are buffer pools and tablespaces. Many standards documents, particularly if written by those coming from a DB2 for z/OS background, encourage a policy of “one table per tablespace”. This means that these highly privileged accounts are over-utilised, which tends to mean that they are either in wider circulation than they should be or that the process for creating new application objects is made more difficult than it need be. While there are still some cases where separating out objects into many tablespaces could be beneficial, in most cases a small number of tablespaces and buffer pools is sufficient. Remembering that the tablespace is the unit of recovery, a practical look at realistic recovery groupings, rather than simply trying to allow single recovery of any individual table, should help to narrow the number of tablespaces used. Exploitation of the block based area in buffer pools should limit the need for separation of tables for performance reason, since this is a very effective way of managing sequential and random IO in a single buffer pool.
Introduction to Session Authorization
DB2 for LUW has the concept of a session authorization ID. In many ways this is very similar to the secondary authorization (SET CURRENT SQLID) functionality which has been available in DB2 for z/OS for many years. A user can be given the privilege to “become” another authorization ID, and this session authorization ID then becomes the owner of any objects created.
The privilege to become a session user can be granted to an individual user or to a group –
GRANT SETSESSIONUSER ON USER <sessionid> TO GROUP <devgroup>;
Privileges which have been granted can be viewed in the catalog view SYSCAT.SURROGATEAUTHIDS (note that this view also provides information about trusted context permissions as well).
A user who has been granted access, either directly or through a group, can then become the session authorization ID using the “SET SESSION AUTHORIZATION = <user>” command (note that “SET SESSION_USER” is a synonym for “SET SESSION AUTHORIZATION”).
Setting the session authorization affects the value of the special registers CURRENT_USER and SESSION_USER (and its synonym USER), but leaves the special register SYSTEM_USER unchanged.
The session user does not need to be a system user (e.g. UNIX login) account. In fact, in most cases it will not be. It is recommended that if a session user is being created to assist in creation and maintenance of objects that this should be the same as the object schema. In fact, when explicitly creating a schema there is a syntax specifically designed for making the schema name and owner the same –
CREATE SCHEMA AUTHORIZATION <name>;
Creating the schema explicitly in this way is helpful when setting up new databases, since it automatically gives CREATEIN, DROPIN and ALTERIN privileges to the authorization ID.
If converting an existing database then the TRANSFER OWNERSHIP command can be used to move existing objects under the ownership of the authorization ID. This needs to be done in a hierarchical fashion, starting from the schema and working down through tables, indexes, views, routines and packages.
Setting Session Authorization
The most basic way to use session authorization is, as stated above, to issue the SET SESSION AUTHORIZATION command. This can be done at any point after connecting to a database, and lasts for the lifetime of the connection (or until another SET SESSION AUTHORIZATION command is issued).
The ability to set special registers in Data Studio has been added recently, at Fixpack 5 level on top of Data Studio 188.8.131.52. To configure a database connection in Data Studio to issue a “SET SESSION AUTHORIZATION” at connection time you must go to the “Optional” tab under driver properties, enter a Property of “specialRegisters” and a Value of “SESSION_AUTHORIZATION=<value>” and then hit “Add”. This will then appear in the list of optional properties as -
Using CONNECT_PROC to Set Session Authorization
Using the techniques previously described, you are relying on developers setting this value when they set up their Data Studio connection, and also setting the session authorization manually whenever they connect outside Data Studio. If there is a session authorization ID that should be the default for a database then this can be achieved by setting up a CONNECT_PROC.
CONNECT_PROC is a DBM (instance) level configuration parameter which specifies a stored procedure which should be run whenever a connection is established to the database. Here is a sample CONNECT_PROC for carrying out this task –
CREATE OR REPLACE PROCEDURE DBADMIN.CONNECT_PROC()
-- Allow passthrough if not allowed to set session authorization
-- (SQL0552N "<user>" does not have the privilege to perform operation "SET SESSION AUTHORIZATION". SQLSTATE=28000)
DECLARE AUTH_FAILURE CONDITION FOR '28000';
DECLARE CONTINUE HANDLER FOR AUTH_FAILURE
SET SESSION AUTHORIZATION = 'MYAUTH';
The CONNECT_PROC is very simple. It attempts to set the session authorization ID to MYAUTH. If the user is not permitted to use the session authorization ID (they have not been granted the privilege as described previously) then the connection continues as normal without changing their session authorization ID from their primary (connection) authorization ID.
Note that if you already use CONNECT_PROC for other reasons that there are some restrictions on where in the code the SET SESSION AUTHORIZATION command is allowed to appear (basically it must be the first line of executable code).
As part of the process for bringing on board a developer to work on a database they should be given the privilege to set the session authorization ID.
A Basic Pattern for Shared Development Databases
The details described above form the basis of smoothing the path of development in a shared environment. The following briefly lists a pattern, which could potentially be used to created automated procedures for setting up development databases and bringing new development resources on board.
- Set up a “base database” build which creates a database and populates it with a default schema and session authorization ID. This process needs to be run as SYSADM or SYSCTRL. This process would often also define the SECADM for the database and hand over security administration.
- Set up a “schema definition” build which creates standard objects which must be created by SYSADM and SYSCTRL for a particular schema. This would potentially allocate a buffer pool and tablespace for each page size required, with their names containing the schema name. The schema would then be explicitly created, using the AUTHORIZATION keyword to allocate a session authorization ID with the same name. There would be additional processing if this was identified as the default schema for a database to add the CONNECT_PROC to set the session authorization ID for authorized users on connection.
- Create a group and role for developers, either specific to a particular database or across the whole instance. Grant the privilege to use the default session authorization ID to the developer group. This will mean that all objects created by those in this group will be created with the session authorization ID as owner, meaning that any developer in the group can work on them using CREATE OR REPLACE.
- Add developers to the group and role as appropriate.
- As well as the session authorization privileges, give any other privileges developers might need to the group or role. For example, a developer will commonly need to use LOAD on tables, so this privilege can be given.
There is potential to use the same principles to set up a database implementation ID in production, meaning that most implementations should no longer need to use the instance owner ID. This implementation ID can be released only when needed by the deployment team, or used in automated procedures.
Limitations of this Approach
While this approach is useful in many circumstances there may be one drawback: it does not maintain information about which primary ID was using the session authorization at a particular time. All the tokens related to IDs in the catalog tables and views use the session authorization ID, not the primary (system) user.
Therefore if knowing who created particular objects is important, perhaps from an audit point of view, monitoring or auditing facilities, either within DB2 or at an operating system level, will have to be used to obtain this information.
Nevertheless the techniques described above should be widely applicable to simplify working in shared environments in many cases.