Top DB2 authority questions DBA to know

DB2 has many in-built security features and are most popular. The following security problems DB2 can handle. The below list is for your quick note:

  • Privilege theft or mismanagement

  • Application or application server tampering

  • Data or log tampering

  • Storage media theft

  • Unauthorized object access

To address these areas, DB2 offers the following security solutions:

  • Authentication

  • Authorization

  • Data integrity

  • Confidentiality

  • System integrity

  • Audit

The Audit trace in DB2

“The trace can record changes in authorization IDs for a security audit as well as changes made to the structure of data (e.g., dropping a table) or to data values (e.g., updating or inserting records) for an audit of data access.

You can also use the audit trace to track access attempts by unauthorized IDs, the results of GRANT and REVOKE statements, the mapping of Kerberos security tickets to RACF IDs, and other activities of interest to auditors.”

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,740 other followers

What is DB2 SYADM?

SYSADM authority is assigned to the group specified by the sysadm_group configuration parameter. Membership in that group is controlled outside the database manager through the security facility used on your platform.

Only a user with SYSADM authority can perform the following functions:

  • Upgrade a database
  • Restore a database
  • Change the database manager configuration file (including specifying the groups having SYSADM, SYSCTRL, SYSMAINT, or SYSMON authority)

What is DB2 DBADM?

The system DBADM authority allows an administrator, an authorization ID or a role, to manage databases across a DB2® subsystem, while having no access to the data in the databases.

With the system DBADM authority, you can issue SQL statements to perform the following tasks:

  • Create and drop aliases, auxiliary tables, and distinct types
  • Create, alter, and drop databases, tables, global temporary tables, table spaces, and sequences
  • Create triggers, functions, indexes, procedures, and views with additional required privileges
  • Comment on all but security-related objects (i.e., roles, trusted contexts)
  • Issue other SQL statements, such as the EXPLAIN, LABEL, PREPARE, and RENAME statements

What is DB2 DBCTRL?

The DBCTRL authority includes the DBMAINT privileges on a specific database. A user with the DBCTRL authority can run utilities that can change the data.

Best example: DBA1 needs to be able to create tables in a database and be able to run periodic REORGs. However, DBA1 is not permitted to access the data or manipulate it. Here you need DBCTRL authority.

 What is DB2 DBMAINT?

A user with the DBMAINT authority can grant the privileges on a specific database to an ID. With the DBMAINT authority, that user can perform the following actions within that database:

  • Create objects
  • Run utilities that don’t change data
  • Issue commands
  • Terminate all utilities on the database except DIAGNOSE, REPORT, and STOSPACE

If a user has the DBMAINT authority with the GRANT option, that user can grant those privileges to others.

What is DB2 SYSOPR?

A user with the SYSOPR authority can issue all DB2® commands except ARCHIVE LOG, START DATABASE, STOP DATABASE, and RECOVER BSDS.

What is DB2 SYSCTRL?

The SYSCTRL authority is designed for administering a system that contains sensitive data. With the SYSCTRL authority, you have nearly complete control of the DB2® subsystem. However, you cannot access user data directly unless you are explicitly granted the privileges to do so.

  • Act as installation SYSOPR (when the catalog is available) or DBCTRL over any database
  • Run any allowable utility on any database
  • Issue a COMMENT ON, LABEL ON, or LOCK TABLE statement for any table
  • Create a view on any catalog table for itself or for other IDs
  • Create tables and aliases for itself or for others IDs
  • Bind a new plan or package and name any ID as the owner of the plan or package
  •  Create roles (only if SEPARATE_SECURITY is set to NO) 
  •  Use any valid value for OWNER in BIND or REBIND (only if SEPARATE_SECURITY is set to NO) 
  •  Has implicit ACCESSCTRL authority to grant most privileges (only if SEPARATE_SECURITY is set to NO) 

Best example: Authority is required to revoke a privilege that another ID has granted

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.