Authorities and privileges for table tasks


Task Authorities and privileges
To alter a table You must have at least one of the following privileges on the table to be altered:
  • ALTER privilege
  • CONTROL privilege
  • SYSADM or DBADM authority
  • ALTERIN privilege on the schema of the table
To run statistics for a table, You must be able to CONNECT to the database to run update TABLE statistics.
To alter a table with a primary key You must have at least one of the following privileges on the table to be altered:
  • ALTER privilege
  • CONTROL privilege
  • SYSADM or DBADM authority
  • ALTERIN privilege on the schema of the table

To drop the primary key, you must have at least one of the following privileges on every dependent table:

  • ALTER privilege
  • CONTROL privilege
  • SYSADM or DBADM authority
  • ALTERIN privilege on the schema of the table
To alter a table with a foreign key You must have at least one of the following privileges on the table to be altered:
  • ALTER privilege
  • CONTROL privilege
  • SYSADM or DBADM authority
  • ALTERIN privilege on the schema of the table

To add or drop a foreign key, you must have one of the following privileges on the parent table:

  • REFERENCES privilege
  • REFERENCES privilege on each column of the specified parent key
  • CONTROL privilege
  • SYSADM or DBADM authority
To change the quiesce mode of a table You must have one of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • DBADM
  • LOAD
To copy a table You need both:
  • One of the following authorities on the source table:
    • SELECT privilege
    • SYSADM or DBADM authority
  • One of the following authorities on the target database:
    • CREATETAB and CONNECT privileges, and either
      • IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the table does not exist
      • CREATEIN privilege on the schema if the schema name of the table exists
    • SYSADM or DBADM authority
To create a table with a foreign key You must have at least one of the following privileges:
  • CREATETAB privilege on the database and either:
    • IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the table does not exist
    • CREATEIN privilege on the schema if the schema name of the table exists
  • SYSADM or DBADM authority

To define a foreign key, you must have at least one of the following privileges on the foreign table:

  • REFERENCES privilege on the table
  • REFERENCES privilege on each column of the specified parent key
  • CONTROL privilege on the table
  • SYSADM or DBADM authority
To define a foreign key You must have at least one of the following privileges on the foreign table:
  • REFERENCES privilege on the table
  • REFERENCES privilege on each column of the specified parent key
  • CONTROL privilege on the table
  • SYSADM or DBADM authority
To create a table You must have at least one of the following privileges:
  • CREATETAB privilege on the database and USE privilege on the table space, and either:
    • IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the table does not exist
    • CREATEIN privilege on the schema if the schema name of the table exists
  • SYSADM or DBADM authority
To drop a table You must either be the definer as recorded in the DEFINER column of SYSCAT.TABLES, or have at least one of the following privileges:
  • SYSADM or DBADM authority
  • DROPIN privilege on the schema for the table
  • CONTROL privilege on the table
To export data from a table or view into an output file You must have one of the following authorities:
  • SYSADM
  • DBADM
  • CONTROL or SELECT privilege for each table or view in the SELECT statement
To grant and revoke privileges on a table You need the proper authorizations:
  • To grant and revoke privileges on a catalog table, you need either SYSADM or DBADM authority.
  • To grant and revoke privileges on a user-defined table, you need to meet the following requirements:
    • To grant and revoke the CONTROL privilege, you need SYSADM or DBADM authority.
    • To grant privileges other than CONTROL, you need one of the following authorizations. To revoke privileges other than CONTROL, you need one of the first three of these authorizations.
      • SYSADM authority
      • DBADM authority
      • The CONTROL privilege on the table
      • The privilege that you want to grant, along with the Grant option (the right to grant this privilege to other users and groups)

      Example

      You can grant the ALTER privilege on a user-defined table if you hold one of these authorizations:

      • SYSADM authority
      • DBADM authority on the database in which the table resides
      • The CONTROL privilege on the table
      • The ALTER privilege, along with the right to grant the ALTER privilege on this table to other users and groups
To import a file into a table You must have one the following authorities:
  • To import using the INSERT option:
    • SYSADM
    • DBADM
    • CONTROL privilege on the table or view
    • INSERT and SELECT privilege on the table or view
  • To import to an existing table using the INSERT_UPDATE, REPLACE, or the REPLACE_CREATE option:
    • SYSADM
    • DBADM
    • CONTROL privilege on the table or view
  • To import to a table that does not exist using the CREATE or the REPLACE_CREATE option:
    • SYSADM
    • DBADM
    • CREATETAB privilege on the database, and one of:
      • IMPLICIT_SCHEMA privilege on the database, if the schema name of the table does not exist
      • CREATEIN privilege on the schema, if the schema of the table exists
To load data into a table You must have one of the following authorities:
  • SYSADM
  • DBADM
  • LOAD authority on the database and:
    • INSERT privilege on the table if you load data in INSERT mode, TERMINATE mode (to terminate a previous load operation), or RESTART mode (to restart a previous load insert operation)
    • INSERT and DELETE privilege on the table if you load data in REPLACE mode, TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a previous load replace operation)
    • INSERT privilege on the exception table, if one is used during the load operation
Note:Because all load processes (and all DB2 server processes in general) are owned by the instance owner, and all these processes use the identification of the instance owner to access the required files, the instance owner must have read access to input data files. The input data files must be readable by the instance owner, regardless of who performs the load operation.
To restart a load You must have one of the following authorities:
  • SYSADM
  • DBADM
  • LOAD

See above for more information.

You should know at what point you want to restart the load process.

To terminate a load you must have one of the following authorities:
  • SYSADM
  • DBADM
  • LOAD

See above for more information.

You should also try to restart the load before terminating it. Terminate a load only if you try to restart it and an unrecoverable error occurs.

To rename a table You must have at least one of the following privileges:
  • CONTROL privilege
  • SYSADM or DBADM authority

To reorganize a table

You must have one of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • DBADM
  • CONTROL privilege on the selected table

For an indication of whether it would be beneficial to reorganize your table, you can run the REORGCHK command. This command is used through the Command Center.

A table cannot be reorganized if both of the following statements are true:

  • The table resides in one or more database managed space (DMS) table spaces.
  • An online backup of a table space that contains the table is currently in progress.
To run statistics for a table You must have one of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • DBADM
  • CONTROL privilege on the selected table
To update statistics for a DB2 Server for VSE & VM table The privileges held by the authorization ID of the statement must include CONNECT authority.
To turn off constraint checking To turn off constraint checking, you need one of the following:
  • SYSADM
  • DBADM
  • CONTROL privilege on the table, and on all dependent or descendent tables in referential integrity constraints
  • LOAD authority

You cannot turn off constraint checking for a system table.

To turn on constraint checking and carry out checking To turn on constraint checking and carry out checking, you need one of the following:
  • SYSADM
  • DBADM
  • CONTROL privileges on the tables being checked, and if exceptions are being posted to one or more tables, INSERT privilege on the exception tables
  • LOAD authority, and if exceptions are being posted to one or more tables:
    • SELECT and DELETE privilege on each table being checked, and
    • INSERT privilege on the exception tables

If the selected table is a dependent or descendent table, make sure that the constraints of all of its parent (direct or indirect) tables are turned on.

You cannot turn on constraint checking for a system table.

To turn on constraint checking without first carrying out checking To turn on constraint checking without first carrying out checking, you need one of the following:
  • SYSADM
  • DBADM
  • CONTROL privileges on the tables being checked
  • LOAD authority