Granting a user the privileges to create tables
The authority that you must grant to a user who creates tables varies. One set of privileges are required when you specify a database and table space name in the SPACE field of the QMF user profile. A different set of privileges is required when you specify only a database or no value.
- Privileges that are required for explicitly created table spaces:
- At a minimum, the user needs the CREATETAB privilege for the database
and the USE privilege on the receiving table space.
If you want to allow a user to create tables, but want to maintain control over how much resource is used, assign a table space for the user rather than granting CREATETS authority. That way, you can control the size of the table space and the amount of resource used.
- Privileges that are required for implicitly created table spaces:
- At a minimum, the user needs the CREATETAB and CREATETS privileges on the database.
Users of the default Db2® for z/OS® database, DSNDB04, might already have some of the preceding privileges. During database installation, the CREATETAB and CREATETS privileges for the default database are granted to PUBLIC. A user of the default database, operating under the implicit table space option, automatically has the minimum authority to create tables. If, instead, this user operates under the explicit table space option, only the USE privilege must be granted.
When users create tables for others, the owner qualifier (the owner of the object) must be the user's primary or secondary authorization ID. With other IDs, the appropriate CREATE table statement might run, but INSERT statements might not run. When users create their own tables after the table structure is created, the users have the necessary INSERT privilege. All that is needed is the privilege to run the CREATE TABLE statement.