1. Implementing Siebel Business Applications on DB2 for z/OS
  2. What’s New in This Release

3Security Concepts for a DB2 for z/OS Environment

Security Concepts for a DB2 for z/OS Environment

This chapter discusses the security concepts that are important in running Siebel Business Applications on DB2 for z/OS. This chapter also describes the roles and permissions you require to implement Siebel Business Application on DB2 for z/OS and to set up a connection between the Siebel Server and the Siebel database.

This chapter includes the following topics:

For a general discussion of Siebel CRM architecture, including a discussion of supported client types, refer to Siebel Deployment Planning Guide and Siebel Installation Guide for the operating system you are using.

About Siebel Application Data Security

All users must provide a user ID and password to connect to Siebel Business Applications, regardless of whether they access them through the Siebel Developer Web Client, the Siebel Mobile Web Client, or the Siebel Web Client.

Each user must be preregistered within the Siebel application and associated with a unique record. This unique record determines a user’s access to data by association with Positions and Responsibilities:

  • Responsibilities determine the screens and views that are available to an employee.

  • Positions determine the data rows that appear in those views, and the data rows that are synchronized to a Mobile Web Client’s local database.

Siebel Business Applications allow you to control user access to information from within the application. Because the need to know specific information is usually related to a particular responsibility, data access is defined by responsibility rather than by user.

Attachments that are not stored in the database are stored in a compressed, encrypted format on the Siebel File Server. Attachments are linked to data rows, and access to attachments is therefore restricted by a user’s responsibilities and position. For more information on these topics, refer to Siebel Security Guide.

Operating System Security

The DB2 Connect middleware passes the user ID and password used to access Siebel Business Applications to DB2 to establish a connection. The user ID and password can be user credentials supplied by the external security adapter, if you are using an external security adapter.

The Security Administrator on z/OS must preregister all user IDs and passwords passed through DB2 Connect with the z/OS security package in use, for example, Resource Access Control Facility (RACF), ACF/2, or TOP SECRET. The z/OS Security Administrator can associate the user ID with a secondary authorization ID (also known as the Security Group ID on the Deployment Planning Worksheet) within the z/OS security package to simplify database security privilege administration.

DB2 Connect supports password encryption on the DB2 for z/OS platform.

Note: The password rules for Siebel user IDs for DB2 are determined by the operating system security interface (RACF, ACF/2, or TOP SECRET).

User Password Change and Expiration

Because of security constraints, if you are using Siebel Business Applications on z/OS, user passwords might be set up to periodically expire. User passwords can be changed as follows:

  • When a password has expired. In Siebel Business Applications, expired user passwords can be changed from the login window without administrative intervention.

  • When a password has not yet expired. If a user password has not yet expired, a user can change the password by amending the user preferences in the client application. For information on amending user preferences, see Siebel Fundamentals.

    Note: A user’s access to the functionality in the User Preferences screen depends on how the Siebel application is configured. For more information, contact your Siebel administrator.

About Changing z/OS Passwords from Client Computers

To change z/OS passwords from client computers, set the DB2 extended security option (EXTSEC) to YES. The default DB2 value for extended security is NO. There are two ways to set the extended security option to YES:

  • In the DSNTIPR installation panel, change EXTENDED SECURITY to YES.

  • In the DSN6SYSP macro within the DSNZPARM creation job, set option EXTSEC to YES.

Setting the extended security option to YES enables the following two functions:

  • Users on client workstations can change their z/OS passwords without signing onto TSO.

  • Siebel Business Applications receive descriptive error codes generated by DB2 when security violations occur.

DB2 z/OS Authorization IDs

By default, DB2 restricts access to database resources unless privileges are specifically granted. A complete description of DB2 security is available in the vendor documentation on the IBM Web site. Access to database resources is established using one or more of the following authorization IDs:

  • Primary authorization ID. The user ID used to log into DB2. All users have a primary authorization ID.

  • Secondary authorization ID. Generally known as a group ID. Siebel Business Applications effectively use secondary authorization IDs that are enabled through the DB2 installation exit, DSN3@ATH. IBM provides a sample exit for setting secondary authorization IDs. For information on using secondary authorization IDs, see About Using a Secondary Authorization ID.

  • Package owners. The ID of the owner of a DB2 Connect package. The package owner ID is used for authorization purposes if the package is bound with DYNAMICRULES(BIND) on the BIND command. For more information on DB2 Connect packages, see an IBM Corporation Redbook on the subject.

    About Using a Secondary Authorization ID

    Using a secondary authorization ID significantly reduces the administrative tasks associated with database security. The administrator grants privileges only once to a secondary authorization ID rather than to each Siebel Business Applications user.

    Note: When you install the Siebel Schema, you are prompted to enter a Security Group ID/Grantee. This is the same as a secondary authorization ID.

    During the Siebel Schema installation process, you can specify a secondary authorization ID for client access with the default group of SSEROLE. The installation process generates the appropriate SQL grant statements for that group to allow INSERT, UPDATE, SELECT, and DELETE authority to application tables. Furthermore, that same group is specified in a SET CURRENT SQLID statement so that reuse of the statement cache is maximized. Therefore, it is important that the selected group is among the list of secondary authorization IDs for all users of the applications.

    Grant statements for additional secondary authorization IDs

    You must create secondary authorization IDs separately. Siebel Business Applications include the grantstat.sql script; this script generates grant statements which allow access to interface tables. For a discussion of the grantstat.sql script, see Granting Table Privileges.

    Either the table owner, or users with DBADM or SYSADM privileges, must execute the grant statements. To disable a grant, issue a revoke statement.

      About Using an External Security Adapter on z/OS

      An external security adapter is an interface that lets you use an external system to authenticate users. For example, you might employ an LDAP repository, a protocol for storing and retrieving directory-related information that includes authentication services. LDAP can reside on the mainframe.

      When users log in to the Siebel application, the external security adapter validates user names, passwords, user roles, and database credentials against the information in the external system. If the external security adapter finds a match, it retrieves a generic set of user credentials (user name and password) that supply access to the database. For LDAP, the generic set of user credentials can be the same for every user, if desired. For more information on implementing an external security adapter, refer to Siebel Security Guide.

        Data Transmission Security for Siebel Clients

        Siebel Web Clients and Mobile Web Clients access Siebel Business Applications over the Internet and Mobile Web Clients can also synchronize with the corporate database over the Internet. To provide data transmission security for these situations, Siebel Business Applications support compression and encryption of data between these clients and the Siebel Application Object Manager and Remote Manager processes. For more information about this topic, refer to Siebel Installation Guide for the operating system you are using.

        DB2 Connect also supports password encryption on the DB2 for z/OS platform. For more information about this topic, refer to the IBM documentation.

          Roles and Permissions Used to Connect to DB2

          The following roles and permissions are used to connect to DB2 and to install Siebel Business Applications on a DB2 database:

          • SYSADM

          • DBADM

          • CREATEDBA

            SYSADM Privileges Used for Connecting to DB2

            A DB2 subsystem is a prerequisite for installing Siebel Business Applications. Although you do not need to use an ID with SYSADM privileges to install Siebel Business Applications, you might need such an ID to create underlying DB2 resources. For detailed information on setting up a DB2 subsystem for Siebel Business Applications, see Preparing for Implementation on the DB2 Host.

            Functions that require SYSADM authority and that are necessary when you install Siebel Business Applications on DB2 for z/OS include:

            • Allocating and accessing buffer pools

            • Allocating and accessing storage groups

            • Granting CREATEDBA or DBADM authority to the Siebel user ID used for the Siebel database installation

            • Creating user-defined functions and stored procedures.

              DBADM/CREATEDBA Privileges Used for Connecting to DB2

              To run the Database Configuration Wizard requires access similar to that of DBADM; you must be able to create Siebel objects and access the necessary utilities. Therefore, it is recommended that you grant CREATEDBA privileges to the primary or secondary authorization IDs that will be used to run the Database Configuration Wizard to perform the database installation and configuration.

                Granting SELECT Authority to Access the DB2 Catalog

                Siebel Business Applications access the DB2 catalog to validate installation inputs. To grant appropriate users access privileges to the DB2 catalog, the system administrator must grant SELECT authority on certain catalog tables to users of the Siebel database and to users of the database installation utility, as follows:

                • Siebel database users (also known as privileged users who make changes to the Siebel database) require SELECT authority for SYSIBM.SYSTABLES.

                • Database installation users require SELECT authority for the following tables:

                  • SYSIBM.SYSAUXRELS

                  • SYSIBM.SYSCOLUMNS

                  • SYSIBM.SYSDATABASE

                  • SYSIBM.SYSINDEXES

                  • SYSIBM.SYSINDEXPART

                  • SYSIBM.SYSKEYS

                  • SYSIBM.SYSROUTINES

                  • SYSIBM.SYSSTOGROUP

                  • SYSIBM.SYSTABLESPACE

                  • SYSIBM.SYSTABLES

                  • SYSIBM.SYSTABLEPART

                  • SYSIBM.SYSTRIGGERS

                  The following procedure describes how to grant SELECT authority to access the DB2 catalog.

                To grant SELECT authority to access the DB2 Catalog

                • Use this command: 

                  GRANTAUTHORITY_TYPE ON TABLETABLENAME TOUSER;

                  For example, to grant SELECT authority on the table SYSIBM.SYSTABLES to SSEROLE, use the following command:

                  GRANT SELECT ON TABLE SYSIBM.SYSTABLES TO SSEROLE;

                  Granting Authorization to Views in DB2

                  GRANT VIEW statements can fail on DB2 for z/OS if you use an external security manager, such as RACF, to protect DB2 resources, and if internal security authorizations are not also in place. Such failures might occur because when a GRANT VIEW statement is issued on DB2 for z/OS, DB2 only carries out internal database security checks before giving authorization to the view. If an internal security mechanism defining privileges to views does not exist, because these privileges are defined using an external security manager, the GRANT VIEW statement fails.

                  GRANT VIEW statements are issued only in the ddlview.sql file; this contains the DDL to create the Siebel Schema.

                    Required Authorizations

                    This topic lists the DB2 authorizations required to install and configure the Siebel database on DB2 for z/OS. It also lists the authorizations that are required for Siebel database accounts when implementing and using DB2 for z/OS.

                      DB2 Authorizations Required

                      The following table lists the authorizations that are necessary to implement Siebel Business Applications on DB2 for z/OS.

                      Table DB2 Authorizations Required to Implement Siebel Business Applications

                      Task Authorization Required Task Command Example

                      Alter a buffer pool.

                      SYSADM, SYSCTRL, SYSOPR

                      ALTER BUFFERPOOL

                      (BP32K1) VPSIZE(4000);

                      Grant use of a buffer pool.

                      SYSADM, SYSCTRL

                      GRANT USE OF BUFFERPOOL BP32K1 TO PUBLIC;

                      Grant CREATEIN for triggers.

                      SYSADM, SYSCTRL

                      GRANT CREATEIN ON SCHEMA SIEBTO;

                      Create a storage group.

                      SYSADM, SYSCTRL, CREATESG

                      CREATE STOGROUP SIEBEL VOLUMES('*') VCAT SIEBEL;

                      Grant use of a storage group.

                      SYSADM, SYSCTRL

                      GRANT USE OF STOGROUP SIEBEL TO PUBLIC;

                      Grant CREATEDBA and DBADM authority.

                      SYSADM, SYSCTRL

                      GRANT CREATEDBA TO SIEBTO;

                      Create a database.

                      SYSADM, SYSCTRL, CREATEDBA, CREATEDBC

                      SET CURRENT SQLID='SIEBTO'; CREATE DATABASE SIDB0001 CCSID ASCII BUFFERPOOL BP1;

                      Alter a table space.

                      DBADM, SYSADM, SYSCTRL

                      ALTER TABLESPACE D0010002.H0677000 BUFFERPOOL BP16K0;

                      Create a table space.

                      SYSADM, SYSCTRL, DBADM, DBCTRL, DBMAINT, CREATETS

                      SET CURRENT SQLID='SIEBTO'; CREATE TABLESPACE H1004000 IN SIDB1004 USING STOGROUP SIEBEL PRIQTY 720 SECQTY 720 BUFFERPOOL BP32K1 SEGSIZE 64 COMPRESS YES LOCKSIZE PAGE;

                      Modify DB2 Connect package (if package already exists).

                      DBADM, SYSADM, BIND privilege on the package, ALTERIN privilege on the schema

                      BIND

                      c:\sqllib\bnd\@ddcsmvs.lst COLLECTION SIEBEL BLOCKING ALL DYNAMICRULES BIND OWNER SIEBTO QUALIFIER SIEBTO SQLERROR CONTINUE

                      Add DB2 Connect package (if a package does not already exist).

                      DBADM, SYSADM, BINDADD privilege, and

                      IMPLICIT_SCHEMA authority on the database if the schema name does not exist

                      CREATIN privilege on the schema if the schema name of the package exists

                      BIND

                      c:\sqllib\bnd\@ddcsmvs.lst COLLECTION SIEBEL BLOCKING ALL DYNAMICRULES BIND OWNER SIEBTO QUALIFIER SIEBTO SQLERROR CONTINUE

                      Alter a table.

                      DBADM, SYSADM, SYSCTRL

                      ALTER TABLE SIEBTO.S_CONTACT

                      ALTER COLUMN COMMENTS SET DATA TYPE VARCHAR (100);

                      Create a table.

                      SYSADM, SYSCTRL, DBADM, DBCTRL, DBMAINT, CREATETAB

                      SET CURRENT SQLID='SIEBTO'; CREATE TABLE S_SSA_ID,(…)IN SIDB1932.H1932000;

                      Alter an index.

                      DBADM, SYSADM, SYSCTRL

                      ALTER INDEX SIEBTO.S_CONTACT_U1 BUFFERPOOL BP30;

                      Create an index.

                      SYSADM, SYSCTRL, DBADM, DBCTRL

                      SET CURRENT SQLID='SIEBTO';CREATE UNIQUE INDEX S_SSA_ID_P1 ON S_SSA_ID(ROW_ID)USING STOGROUP SIEBEL PRIQTY 720 SECQTY 720 DEFINE NO CLOSE YES PCTFREE 17 BUFFERPOOL BP2;

                      Grant CREATE or PACKADM for stored procedures.

                      SYSADM, SYSCTRL

                      GRANT CREATE ON COLLECTION SIEBINST TO SIEBDBA;

                      Grant BINDADD.

                      SYSADM, SYSCTRL

                      GRANT BINDADD TO SIEBDBA;

                      Grant SELECT on catalog tables.

                      SYSADM, SYSCTRL

                      GRANT SELECT ON SYSIBM.SYSTABLES TO SIEBTO;

                      Create User-Defined Functions

                      SYSADM, DBADM

                      CREATE FUNCTION SIEBEL.NEXTSEQ (INTEGER) RETURNS SYSIBM.INTEGER SPECIFIC SIEBEL.NEXTSEQ EXTERNAL NAME 'NEXTSEQ' LANGUAGE C PARAMETER STYLE DB2SQL NOT DETERMINISTIC FENCED RETURNS NULL ON NULL INPUT NO SQL NO EXTERNAL ACTION SCRATCHPAD 100 NO FINAL CALL DISALLOW PARALLEL NO DBINFO WLM ENVIRONMENT DB27WLM STAY RESIDENT NO PROGRAM TYPE SUB SECURITY DB2 INHERIT SPECIAL REGISTERS

                        Siebel Database Account Authorizations

                        Before installing and configuring the Siebel database, the DBA must create the following database accounts:

                        • Table owner (Siebel schema owner) account

                          The table owner is the Siebel schema owner, that is, the user account assigned to the schema that owns the Siebel database objects. Privileges required for this account include DBA administration (DBADM) privileges.

                        • Siebel security group authorization account

                          Specify a security group ID, for example, SSEROLE, for client access to the Siebel database. The security group ID is also referred to as the secondary authorization ID.

                        • Siebel administrator account

                          The Siebel administrator account, for example, SADMIN, must be added as a member of the Siebel security group.

                        Note: The password assigned to the table owner and Siebel administrator accounts must not be the same as the user name specified for these accounts. To increase the security of your Siebel implementation, it is also recommended that you change the password of the Siebel administrator account regularly. For information on this task, see Siebel Security Guide.

                        The following table lists the authorizations that the database accounts created for Siebel Business Applications might need. Your enterprise might have unique role names that it assigns with the authorities listed in this table. Therefore, the role names in the following table are examples only.

                        Table Authorizations Required by Siebel Database Accounts

                        Task Role Authorization Required Task Command Example

                        Performing the following actions on Siebel tables:

                        • Delete

                        • Insert

                        • Select

                        • Update

                        Siebel group ID (for example, SSEROLE group).

                        Table privileges are granted automatically during the installation of the Siebel database.

                        GRANT ALL ON TABLE S_SSA_ID TO SSEROLE;

                        Setting the current SQL ID

                        Schema qualifier group or individual ID, for example, SIEBTO.

                        This user owns the schema objects (created by the database administrator) that are used during the installation of Siebel CRM.

                        SET CURRENT SQLID=’SIEBTO’;

                        Performing server functions, such as:

                        • Runstats

                        • Generate triggers

                        Siebel administrator group, for example, SADMIN.

                        This user is:

                        • A member of the generic user role

                        • A member of the Resource Access Control Facility (RACF) group, or of another security package group, selected to act as administrator

                        • A Siebel database administrator (SIEBDBA)

                        RUNSTATS TABLESPACE D0010677.H0677000 TABLE(SIEBEL.S_CONTACT)FREQVAL COUNT 10

                        CREATE TRIGGER SIEBEL.PTH0477 NO CASCADE BEFORE INSERT ON REFERENCING NEW AS N FOR EACH ROW MODE DB2 SQL WHEN ( N.ROW_ID IS NOT NULL) 
                        BEGIN ATOMIC
                        SET N.PARTITION_COLUMN = RIGHT (N.ROW_ID, 2);

                        END