Introduction

Last day, we finished discussing the various programming interfaces for DB2.
We talked about ODBC, JDBC, types of JDBC drivers, interactive tools, and stored procedures.
Then we began talking about DB2 security.
We talked a little about data set security, authentication, and authorization.
We said how primary and secondary authorization ids are obtained via connection exits, and gave some examples.
We mentioned three kinds of authorization related special registers: USER, CURRENT SQLID, and CURRENT SCHEMA
Today, we are going to continue to look at security in DB2, beginning with reviewing the last couple things we did off-slides last day, starting by talking about these special registers in more detail.

Authorization Related Special Registers

We said last day that special registers contain values, maintained by the system, that describe and control the environment in which an SQL statement is executed. Let's look at the three main special registers in turn.
USER -- contains the user id of the user who is connected to DB2 and executing the current application. This is the user id that is used for authorization checking for dynamic SQL statement. Authorization checking of static SQL statements, on the other hand, is performed against the user id who bound the application, which is not in general the same as the content of the USER special register. The value of USER is obtained from the system and cannot be manually set. USER is set to the primary authid.
CURRENT SQLID -- This is set to either your primary authid or one of your secondary authids. (Except for SYSADM / SYSCTRL). It has three purposes: It acts as an implicit qualifier on unqualified objects in SQL statements, it is used for authorization checking for CREATE, GRANT, REVOKE statements, it also become the owner of an object after a CREATE statement for that object.
CURRENT SCHEMA -- is used to provide an implicit qualifier for unqualified objects.

Example

SET CURRENT SCHEMA = "ABC"; 
SET CURRENT SQLID = "XYZ"; 
SELECT * FROM T1;

T1 is interpreted as ABC.T1
But user XYZ will be checked for authorization to SELECT from this table

Querying the values of Special Registers

You can use this query to show the values of USER, CURRENT SQLID and CURRENT SCHEMA:

SELECT 'PRIMARY AUTHID = '||USER, 
  'CURRENT SQLID = '||CURRENT SQLID, 
  'CURRENT SCHEMA = '||CURRENT SCHEMA 
FROM SYSIBM.SYSDUMMY1

This will result in: PRIMARY AUTHID = primary authid; CURRENT SQLID = current sqlid; CURRENT SCHEMA = current schema
There is no standard way within DB2 for you to retrieve a list of all your SECONDARY AUTHIDs.

Schema and Ownership of Objects

When an object is created without a qualifier, the CURRENT SCHEMA becomes the qualifier, and CURRENT SQLID becomes the owner.
When an object is created with an explicit qualifier, the qualifier of the object is what the creator specified.
The owner of the object depends on the type of the object.
For tables, views, and indexes, the qualifier becomes the owner of the objects.
For functions, procedures, and sequences, the SQLID becomes the owner of the objects.

Access to Data for AUTHID

You can control access within DB2 by granting or revoking privileges and related authorities that you assign to authorization IDs or roles.
A privilege allows the capability to perform a specific operation, sometimes on a specific object.
Privileges can be explicit or implicit
An explicit privilege will have a name and is the results of a GRANT statement.
An implicit privilege comes from the ownership of objects.
As an example of an implicit privilege, when one owns a table object, one automatically has the privileges to SELECT/UPDATE/INSERT/DELETE the table.
An implicit privilege can also be acquired via the execution privilege of a plan or a package. A user, who is granted the execution privilege of a package, has implicit privilege over the operations of the tables which are included in the package. Outside the execution of the package, the user may not have the privileges over the same tables.

Administrative Authority

An administrative authority is a set of privileges, often covering a related set of objects.
Authorities often include privileges that are not explicit, have no name, and cannot be specifically granted.
For example, when an ID is granted the SYSOPR administrative authority, the ID is implicitly granted the ability to terminate any utility job.

Explicit Privileges

Different DB2 object have different available explicit privileges. We next list some of these by object

TABLE -- SELECT, UPDATE, INSERT, DELETE...
PLAN, PACKAGE -- BIND, EXECUTE
SCHEMA -- CREATEIN
DATABASE -- CREATETAB, CREATETS, LOAD...
SYSTEM -- BINDADD, CREATEDBA, CREATEDBC
USE -- USE OF BUFFERPOOL, USE OF STOGROUP in CREATE/ALTER TABLESPACE and INDEX

Granting Privileges to an AUTHID

A DB2 GRANT statement gives a specific privilege to an authorization ID. Here are some examples of this command:

GRANT SELECT ON TABLE TABLE1 TO MARY;

GRANT BIND ON PACKAGE DBRM1 TO PAYROLL;

GRANT SELECT ON TABLE TABLE1 TO ACCNTG;

GRANT CREATDB, LOAD ON DATABASE DBMANUF 
  TO MANUF WITH GRANT OPTION;

How Privileges Work

A process which is connected to DB2 and has several authorization IDs will be able to use the privileges granted to each one of them.
Depending on the process type, DB2 will look at either:
1. All composite privileges of the user, or
2. Only at the privileges granted to your CURRENT SQLID.
All DML, ALTER, and DROP statements check for composite privileges.
All CREATE statements check only the privileges of CURRENT SQLID.
The motivation for why only CURRENT SQLID is used for create statements can be explained by an example: When you create a view, there is a dependency record generated in SYSVIEWDEP table for the view owner and the view. It is possible that multiple ID's all have the privileges to create the view. But we don't want to drop the view when one of the ID's privileges is revoked.

Privilege Example

An example of how privilege affect the catalog

In the above, for Mary to create a table in table space TS0001 in the database DB11, Mary needs the CREATETAB privilege in the database DB11. She also needs the USE privilege of the table space TS00001.
The CREATETAB privilege is recorded in the SYSDBAUTH catalog table. The USE privilege is recorded in the SYSRESAUTH catalog table.

Revoking Privileges

The syntax for revoking privileges is:

REVOKE privileges ON resource FROM authid 
(BY authid [SYSADM/SYSCNTL only])

REVOKE must be issued with GRANTOR's CURRENT SQLID except in the "REVOKE by SYSADM/SYSCTRL" case.
REVOKE SELECT can DROP VIEWS
Revoke has a timestamp-based cascading effect -- if A grants privilege to B and B grants the same privilege to C. Then if A revokes the privilege from B, C will also lose the privilege.
For this reason, you should avoid using the WITH GRANT OPTION whenever possible.

Hierarchical Administrative Authorities

DB2 come with some pre-defined authid's which in turn have pre-defined privileges associated to them. These are the so-called administrative authorities shown in the graph above.
Each authority includes the privileges in its box plus all the privileges of all authorities beneath it.
When you grant one of the administrative authorities to a person's ID, that person has all of the privileges that are associated with that administrative authority.
You can efficiently grant many privileges by granting one administrative authority.
The left hand side authorities are for the scope of a DB2 subsystem. The right hand side authorities are for the scope of a database.

Here are some examples of granting these authorities:

GRANT SYSOPR TO user01; 
  --- Grants user01 the privilege 
  ---to have system operator authority.

GRANT DBADM ON DATABASE dbm1 TO user01;  
 --- Grants user01 the database administrator authority.

Ownership of a Table

When you create a table, you establish its ownership.
The owner implicitly holds certain privileges, such as ALTER/DROP/LOCK table, create an index, view, or trigger, SELECT/UPDATE/INSERT/DELETE/LOAD.
As the owner, you can also grant explicit privileges of this table to other users.
Privileges implied by the ownership cannot be revoked. Ownership cannot be changed.

Static SQL -- Privileges for Plan and Package

The owner of a plan or package can BIND the plan or package.
A user of a plan or package can EXECUTE the plan or package.
The user must have been granted the EXECUTE privilege for this to work.
In order to bind a plan, the owner needs:
- Applicable privileges required by the statements
- BIND privilege and CREATEIN privilege for the collection of packages
- EXECUTE privilege for all the packages in the BIND plan list

Example -- Static SQL

In the above, at BIND PACKAGE time, security checks were done to make sure that the binder has the required privileges to create the package.
When the program was put into production, the EXECUTE privilege of the plan/package is granted to the users that needed to execute it.
When the package is run...
1. User "x" enters a transaction code on his terminal (transid) which triggers the execution of the load module LMODA in CICS (obviously, CICS made sure that "x" had the right to execute "transid").
2. The application invokes the first SQL statement.
3. At this first SQL statement, DB2 checks whether "x" has the EXECUTE privilege on PLANA. (LMODA was associated with PLANA in CICS.)
4. If so, it will load the plan into storage and execute it. The plan will invoke the necessary packages.
5. The "thread" is created. From now on, no more security checks will be made.
Subsequent SQL calls will be executed over the thread.

Dynamic SQL -- Privilege Checking

For a GRANT issued dynamically, the CURRENT SQLID is checked for: the privilege with the grant option, or an authority that includes the privilege with the grant option, or ownership that implicitly includes the privilege.
For a REVOKE issued dynamically, the CURRENT SQLID is check to make sure that it has the privilege that is being revoked or it has SYSCTRL or SYSADM authority

Composite Privileges

An SQL statement can reference more than one object. For example, a SELECT can join two or more tables. An INSERT/UPDATE/DELETE can use a subquery. These operations require privileges on all the tables.
With DYNAMICRULES (RUN), you can issue such a statement dynamically, even if one of your authids alone does not have all the required privileges. The statement is validated if your primary authid and all your secondary authids have all the necessary privileges among them.

Multi-level Security with Row-Level Granularity

Multi-level security is a security policy that allows you to classify objects and users based on a system of hierarchical security levels and a system of non-hierarchical security categories.
Multi-level security provides the capability to prevent unauthorized users from accessing information at a higher classification than their authorization, and prevents users from declassifying information.
In the RACF, the security administrator needs to set up the security hierarchy and assign the appropriate security label to individual users. For example, Alan's SECLABEL is assigned to "HIGH", Beth's is "MEDIUM", and Carlos' is "LOW".
Suppose an EMP table is created with a "security" column and rows are populated with the security classification of the row. This could be done with a create table statement with a column:
```
SECURITY CHAR(8) NOT NULL WITH DEFAULT AS SECURITY LABEL,
```
When running a query that selects from the EMP table, the user's SECLABEL (from RACF) is compared to the SECURITY column of the table. The rows are selected only if the user's SECLABEL is higher or equal to the security classification of the row.

Challenges with Security in a Distributed Setting

DB2 receives requests from many external entities.
Applications running on a remote application server talk to DB2 in a three tier architecture.
Most existing application servers connect to DB2 using a single user ID to initiate requests to DB2.
This ID must have all administrative privileges and the privileges required to execute the business transactions.
If someone steals that user ID and password, that person can exercise those privileges from another place in the network. This is a significant exposure.
It also means diminished accountability. For example, there is no ability to separate access performed by the middleware server for its own purposes from those performed on behalf of users
Trusted contexts will be used to solve this.
Before trusted contexts and roles, privileges held by an authid were universal, irrespective of context. This can weaken security because a privilege can be used for purposes other than those originally intended. For example, if an authid is granted SELECT on a payroll table, the authid can exercise that privilege regardless of how it gains access to the table.

Trusted Context

Trusted context addresses the problem of establishing a trusted relationship between DB2 and an external entity, such as a middleware server.
A series of trust attributes are evaluated at connect time to determine if a specific context is to be trusted.
The relationship between a connection and a trusted context is established when a connection to the server is first created.
Once established, a trusted connection provides the ability to:
- Use the trusted connection for a different user without authentication
- Acquire special set of privileges by an authorization ID, that are not available to it outside the trusted context. This is accomplished by associating a role with the trusted context
- Allow a role to own objects, if objects are created in a trusted context with role defined as the owner.
- Acquire security label (RACF SECLABEL) to be used for multi-level security verification. Multi-level security restricts access to an object or a row based on the security label of the object or row and the security label of the user.

Trusted Context Characteristics

Trusted contexts are created and manipulated with the statements: CREATE TRUSTED CONTEXT, ALTER TRUSTED CONTEXT, DROP TRUSTED CONTEXT. For example,
```
CREATE TRUSTED CONTEXT CTX1 
BASED UPON CONNECTION 
USING SYSTEM AUTHID ADMIN1 
ATTRIBUTES (ADDRESS '9.67.40.219') 
ENABLE;
```
Notice a unique SYSTEM AUTHID is specified with respect to the context.
The trusted context as a whole is stored as a database entity.
This entity also includes information about when the connection should be trusted.
What this connection information consists of depends on whether the connection is a local or a remote connection.
For a remote connection you can specify: the connection ADDRESS; SERVAUTH, where SERVAUTH is a resource in the RACF SERVAUTH class; and ENCRYPTION which can be NONE, LOW, HIGH.
For a local connection, you can specify the JOBNAME. For the trusted context to be allowed, then the SYSTEM AUTHID and JOBNAME have to match.

Database Role

A database role is another database entity.
A role is established only through a trusted connection
A User is assigned at most one role: either a default role or user-specific role.
User-specific role takes precedence.
Privileges can be granted to a role and revoked from a role
Roles are allowed to be the owner's of DB objects.

Here are some examples of creating roles and trusted contexts:


CREATE ROLE CTXROLE; 

GRANT DBADM ... TO CTXROLE;

CREATE TRUSTED CONTEXT CTX1 
BASED UPON CONNECTION 
USING SYSTEM AUTHID ADMIN1 
DEFAULT ROLE CTXROLE 
ATTRIBUTES (ADDRESS '9.67.40.219') 
ENABLE;

Trusted Context Authorization ID Switching

The administrator can add specific users to the list of authorization IDs that can use the trusted context.

For example,

CREATE TRUSTED CONTEXT CTX1 
BASED UPON CONNECTION 
USING SYSTEM AUTHID ADMIN1 
ATTRIBUTES (ADDRESS '9.67.40.219') 
ENABLE 
WITH USE FOR JOE ROLE JROLE;

If PUBLIC is specified for the user, then it allows the trusted connection to be used by any authorization ID.
The administrator can specify if authentication is required by a WITH AUTHENTICATION clause. The default is WITHOUT AUTHENTICATION.
The administrator can also specify a different role for the user, other than the default.

Securing an Application Server

Typically, in a three tiered architecture an application server's system authorization ID is used to grant the needed privileges for all actions performed by end users
A Trusted context and role can be used to limit the exposure to risk that this entail.
The privileges that are needed can be granted instead to a role.
Access is then restricted because the privileges are valid only when a user is connected from a list of pre-defined IP addresses of application servers
No change needed to the code in the application server.

Auditing

Role and Trusted Context also enable customers to improve DB2 system auditing.
Many customers use a "system" user id to access DB2 so that they don't have to grant dynamic SQL privileges to their end users.
With the trusted contexts, customers are able to grant dynamic SQL table privileges to a ROLE, and specify that the end user can only use that ROLE when the end user is running on an approved application server.
A ROLE can be used as a single database authid that can be used to simplify administration of dynamic SQL privileges.
The end user's authid can be used to run database transactions, so that the DB2 audit is able to identify the end users individually. This is an important capability for meeting some regulatory compliance requirements.

Secure DBA Activities

An auditable DBA process can be implemented with trusted contexts and roles:

Grant DBA privileges to a role, AuditRole
When a DBA needs to perform a system change:
- The DBA connects using a trusted context which has been assigned AuditRole to that DBA's Authorization ID
- This trusted context and role has access to the sensitive objects, so the DBA can do what is needed
- At the end of the day, one can disable the trusted context if one wants to protect the sensitive objects.
An auditor can review the audit trace who was using the trusted context.

More DB2 Security

Outline