Database authorization

Authorization is the process where the database manager gets information about the authenticated user. Part of that information is determining which database operations the user can perform and which data objects a user can access.

Overview of database authorization

A privilege is a type of permission for an authorization name, or a permission to perform an action or a task. The privilege allows a user to create or access database resources. Privileges are stored in the database catalogs. Authorized users can pass on privileges on their own objects to other users by using the GRANT statement. Privileges can be granted to individual users, to groups, or to PUBLIC. PUBLIC is a special group that consists of all users, including future users. Users that are members of a group will indirectly take advantage of the privileges granted to the group, where groups are supported.

A role is a database object that groups one or more privileges. Roles can be assigned to users or groups or other roles by using the GRANT statement. Users that are members of roles have the privileges that are defined for the role with which to access data.

The forms of authorization, such as administrative authority, privileges, and Row and column access (RCAC) access, are discussed in Authorization of Db2 Big SQL objects. In addition, ownership of objects brings with it a degree of authorization on the objects created.
  • Administrative authority includes system-level authorization and database-level authorization:
    System-level authorization
    SYSADM (system administrator) authority
    The SYSADM (system administrator) authority provides control over all the resources created and maintained by the database manager. The system administrator possesses all the authorities of SYSCTRL, SYSMAINT, and SYSMON authority. The user who has SYSADM authority is responsible both for controlling the database manager, and for ensuring the safety and integrity of the data.
    SYSCTRL authority
    The SYSCTRL authority provides control over operations that affect system resources. For example, a user with SYSCTRL authority can create, update, start, stop, or drop a database. This user can also start or stop an instance, but cannot access table data. Users with SYSCTRL authority also have SYSMON authority.
    SYSMAINT authority
    The SYSMAINT authority provides the authority required to perform maintenance operations on all databases that are associated with an instance. A user with SYSMAINT authority can update the database configuration, backup a database or table space, restore an existing database, and monitor a database. Like SYSCTRL, SYSMAINT does not provide access to table data. Users with SYSMAINT authority also have SYSMON authority.
    SYSMON (system monitor) authority
    The SYSMON (system monitor) authority provides the authority required to use the database system monitor.
    Database-level authorization
    DBADM (database administrator)
    The DBADM authority level provides administrative authority over a single database. This database administrator possesses the privileges required to create objects and issue database commands. The DBADM authority can be granted only by a user with SECADM authority. The DBADM authority cannot be granted to PUBLIC.
    SECADM (security administrator)
    The SECADM authority level provides administrative authority for security over a single database. The security administrator authority possesses the ability to manage database security objects (database roles, audit policies, trusted contexts, security label components, and security labels) and grant and revoke all database privileges and authorities. A user with SECADM authority can transfer the ownership of objects that they do not own. They can also use the AUDIT statement to associate an audit policy with a particular database or database object at the server.The SECADM authority has no inherent privilege to access data stored in tables. It can only be granted by a user with SECADM authority. The SECADM authority cannot be granted to PUBLIC.
    SQLADM (SQL administrator)
    The SQLADM authority level provides administrative authority to monitor and tune SQL statements within a single database. It can be granted by a user with ACCESSCTRL or SECADM authority.
    WLMADM (workload management administrator)
    The WLMADM authority provides administrative authority to manage workload management objects, such as service classes, work action sets, work class sets, and workloads. It can be granted by a user with ACCESSCTRL or SECADM authority.EXPLAIN (explain authority)The EXPLAIN authority level provides administrative authority to explain query plans without gaining access to data. It can only be granted by a user with ACCESSCTRL or SECADM authority.
    EXPLAIN (explain authority)
    The EXPLAIN authority level provides administrative authority to explain query plans without gaining access to data. It can only be granted by a user with ACCESSCTRL or SECADM authority.
    ACCESSCTRL (access control authority)
    ACCESSCTRL authority can only be granted by a user with SECADM authority. The ACCESSCTRL authority cannot be granted to PUBLIC. The ACCESSCTRL authority level provides administrative authority to issue the following GRANT (and REVOKE) statements:
    • GRANT (Database Authorities)
    • GRANT (Global Variable Privileges)
    • GRANT (Index Privileges)
    • GRANT (Module Privileges)
    • GRANT (Package Privileges)
    • GRANT (Routine Privileges)
    • GRANT (Schema Privileges)
    • GRANT (Sequence Privileges)
    • GRANT (Server Privileges)
    • GRANT (Table, View, or Nickname Privileges)
    • GRANT (Table Space Privileges)
    • GRANT (Workload Privileges)
    • GRANT (XSR Object Privileges)
    For more information about granting and revoking privileges, see Granting and revoking access.
    DATAACCESS (data access authority)
    DATAACCESS authority can be granted only by a user who holds SECADM authority. It cannot be granted to PUBLIC. The DATAACCESS authority level provides the following privileges and authorities:
    • LOAD authority
    • SELECT, INSERT, UPDATE, DELETE privilege on tables, views, nicknames, and materialized query tables
    • EXECUTE privilege on packages
    • EXECUTE privilege on modules
    • EXECUTE privilege on routines, except on the audit routines.
    • USAGE privilege on all sequences
    Database authorities (non-administrative)
    To perform activities such as creating a table or a routine, or for loading data into a table, specific database authorities are required. For example, the LOAD database authority is required for use of the load utility to load data into tables (a user must also have INSERT privilege on the table).
  • Privileges
    CONTROL privilege
    If you possess the CONTROL privilege on an object, you can access that database object, and grant and revoke privileges to or from other users on that object. The CONTROL privilege only applies to tables, views, nicknames, indexes, and packages..

    If a different user requires the CONTROL privilege to that object, a user with SECADM or ACCESSCTRL authority can grant the CONTROL privilege to that object. The CONTROL privilege cannot be revoked from the object owner, however, the object owner can be changed by using the TRANSFER OWNERSHIP statement.

    Individual privileges
    Individual privileges can be granted to allow a user to carry out specific tasks on specific objects. Users with the administrative authorities ACCESSCTRL or SECADM, or with the CONTROL privilege, can grant and revoke privileges to and from users.
    Revoking privileges
    The REVOKE statement is used to revoke previously granted privileges. The revoking of a privilege from an authorization name revokes the privilege granted by all authorization names.
    Authorization ID privileges: SETSESSIONUSER
    Authorization ID privileges involve actions on authorization IDs. There is currently only one such privilege: the SETSESSIONUSER privilege.
    Schema privileges
    Schema privileges are in the object privilege category.
    Table and view privileges
    Table and view privileges involve actions on tables or views in a database.
    Package privileges
    A package is a database object that contains the information needed by the database manager to access data in the most efficient way for a particular application program. Package privileges enable a user to create and manipulate packages.
    Sequence privileges
    The creator of a sequence automatically receives the USAGE and ALTER privileges on the sequence. The USAGE privilege is needed to use NEXT VALUE and PREVIOUS VALUE expressions for the sequence.
    Routine privileges
    Execute privileges involve actions on all types of routines such as functions, procedures, and methods within a database. Once having EXECUTE privilege, a user can then invoke that routine, create a function that is sourced from that routine (applies to functions only), and reference the routine in any DDL statement such as CREATE VIEW or CREATE TRIGGER.
    Usage privilege on workloads
    To enable use of a workload, a user who holds ACCESSCTRL, SECADM, or WLMADM authority can grant USAGE privilege on that workload to a user, a group, or a role using the GRANT USAGE ON WORKLOAD statement.
  • Object ownership

    When an object is created, one authorization ID is assigned ownership of the object. Ownership means that the user is authorized to reference the object in any applicable SQL statement. When an object is created within a schema, the authorization ID of the statement must have the required privilege to create objects in the implicitly or explicitly specified schema.