Start of change

Migrating the SYSADM authority

To take advantage of the granularity of DB2® administrative authority and simplify your system database administration, you can separate the privileges of the SYSADM authority and migrate them to other administrative authorities based on the security needs of your business. This will allow you to eliminate or minimize the need for granting the SYSADM authority.

About this task

If you decide to separate the SYSADM authority into the SECADM and other administrative authorities, consider setting the SEPARATE_SECURITY system parameter on panel DSNTIPP1 to YES during installation or migration. This setting enables you to achieve complete separation of administrative duties.

Procedure

To migrate the SYSADM authority that is currently assigned to authorization IDs or roles:

  1. In your security policies, identify the administration model that you will use for separating the SYSADM authority and define the criteria for assigning specific administrative authorities to specific authorization IDs or roles.

    Suppose that you choose the following model to separate the current SYSADM authority into the system DBADM, SECADM, DATAACCESS, ACCESSCTRL, and SQLADM authorities:

    Begin figure description. Separating SYSADM authority into the system DBADM, SECADM, DATAACCESS, ACCESSCTRL, and SQLADM authorities. End figure description.

    You can define the following set of criteria for granting administrative authorities:

    • The system DBADM authority can be granted to database administrators who need to manage objects
    • The DATAACCESS authority can be granted to database administrators who need to access data
    • The ACCESSCTRL authority can be granted to database administrators who need to control access to DB2 subsystems
    • The SECADM authority can be assigned (during installation) to security administrators who perform security administration and manage access control
    • The SQLADM authority can be assigned to performance analysts who are responsible for analyzing the performance of DB2 subsystems
    • The EXPLAIN privilege can be granted to application architects who need to explain SQL statements or collect metadata information about the statements
    • The SYSOPR authority and the ARCHIVE, BSDS, CREATESG, and STOSPACE privileges can be granted to system administrators for performing system administrative tasks.
  2. Perform a query to list all the users and roles that are currently granted the SYSADM authority.

    The SYSADM authority can be granted to authorization IDs or roles. You can query the catalog and find out the users and roles who are currently granted the SYSADM authority.

    Suppose that your query returns a list of the following six users, user groups, or roles that are assigned the SYSADM authority:

    • John (Security administrator)
    • Sally (Application Architect)
    • Bob (Performance Analyst)
    • ApplProgrammer_role (Application Programmer role)
    • SysAdmin_Role (System administrator role)
    • DBAdmGrp (database administrator group).
  3. Divide the responsibilities of the SYSADM authority and grant to different IDs or roles based on your security policies, as shown below:
    • John is granted the SECADM authority to perform security-related administration tasks and control access to DB2.
    • Sally is granted the DATAACCESS authority because she requires DML privileges on tables during application development, but she does not need access control or database administration.
    • Bob is granted the SQLADM authority who analyzes the performance of DB2 subsystems, but does not need access to data.
    • ApplProg_role is granted the EXPLAIN privilege because all application programmers need to explain SQL statements and collect metadata information in trusted context definitions.
    • DBAdmGrp is granted the system DBADM authority for managing and maintaining objects. Since database administrators belong to the DBAdmGrp RACF® group, they should not be able to access data or grant and revoke privileges.
    • SysAdmin_role is granted the SYSOPR authority and the ARCHIVE, BSDS, CREATESG, and STOSPACE privileges to perform system administrative tasks.
  4. Revoke the SYSADM authority from all current IDs or roles.

    Once the authorities are granted, you can revoke the SYSADM authority from John, Sally, Bob, ApplProgrammer_ role and DBAdmGrp. Revoking the SYSADM authority causes the revoking of dependent privileges, by default. If you want to leave the grants that they had made, you can issue the REVOKE statement with the NOT INCLUDING DEPENDENT PRIVILEGES clause, assuming the REVOKE_DEP_PRIVILEGES system parameter is set to SQLSTMT.

  5. Once the SYSADM authority is revoked, set the SEPARATE_SECURITY system parameter to YES on panel DSNTIPP1. With the installation SYSADM authority, you can perform an online change of the SEPARATE_SECURITY system parameter and set it to YES. This further ensures that SYSADM is separated into SECADM and other authorities.
End of change