By default, no privileges that users or roles hold on source table persist on the new table. You can preserve these privileges in the new table, however, by using the WITH (PRIVILEGES) and WITH (PRIVILEGES, ROLES) clauses.
This clause moves all access privileges on source table to the destination table. All entries for source table in the systabauth and syscolauth system catalog tables are transferred to new table in the destination database. If these entries specify a role, however, the role is ignored, and is also dropped from the source database. The following MOVE TABLE example preserves the user privileges of source table and changes the owner of new table:
MOVE TABLE customer TO DATABASE sales RENAME mary.customer WITH (PRIVILEGES);
This clause specifies that all privileges on source table to be moved to new table, including any privileges of roles. All entries for source table in the systabauth and syscolauth system catalog tables are transferred to the destination table. The following example preserves both user and role privileges in new table:
MOVE TABLE mytab TO DATABASE newdb WITH (PRIVILEGES, ROLES);
The MOVE TABLE statement returns an error if there is a conflict between user names and role names in the source and destination databases. If any roles that are preserved in the destination table are not defined in the destination database, an error is returned. It is the responsibility of the DBA to ensure that role names and user names are correctly defined in the source and destination databases.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]