DB2 Help

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 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 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
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 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 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 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 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 To import to an existing table using the INSERT_UPDATE, REPLACE, or the REPLACE_CREATE option: SYSADM DBADM CONTROL privilege on the table To import to a table that does not exist using the CREATE or the REPLACE_CREATE option: SYSADM DBADM CREATETAB privilege
To load data into a table	you must have one of the following authorities: SYSADM DBADM
To restart a load	you must have one of the following authorities: SYSADM DBADM 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 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 turn off constraint checking	If you do not have SYSADM or DBADM authority: you need the CONTROL privilege on the table and on all of the tables whose foreign keys can be traced back to the primary key of the table. These tables are its dependents and descendents. You cannot turn off constraint checking for a system table.
To turn on constraint checking	If you do not have SYSADM or DBADM authority: You need the CONTROL privilege on the tables that are being checked. These tables include the selected table and its dependents and descendents. To write exceptions, you need the INSERT privilege on the exception table. 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.