SAVE_AUTH procedure¶

Saves the authorizations of the specified relation for later restoration with RESTORE_AUTH procedure.

Prototypes¶

SAVE_AUTH(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128))
SAVE_AUTH(ATABLE VARCHAR(128))

Description¶

SAVE_AUTH is a utility procedure which copies the authorization settings for the specified table or view from SYSCAT.TABAUTH to SAVED_AUTH (a utility table which exists in the same schema as the procedure). These saved settings can then be restored with the RESTORE_AUTH procedure procedure. These procedures are primarily intended for use in conjunction with the other schema evolution functions (like RECREATE_VIEWS procedure).

Warning

Column specific authorizations (stored in SYSCAT.COLAUTH) are not saved and restored by these procedures.

Note

SAVE_AUTH procedure and RESTORE_AUTH procedure are not used directly by RECREATE_VIEW procedure because when a view is marked inoperative, all authorization information is immediately wiped from SYSCAT.TABAUTH. Hence, there is nothing to restore by the time RECREATE_VIEW procedure is run.

You must call SAVE_AUTH procedure before performing the operation that will invalidate the view, and RESTORE_AUTH procedure after running RECREATE_VIEW procedure. Alternatively, you may wish to use SAVE_VIEW procedure and RESTORE_VIEW procedure instead, which rely on SAVE_AUTH procedure and RESTORE_AUTH procedure implicitly.

Parameters¶

ASCHEMA: The name of the schema containing the table for which authorizations are to be saved. If this parameter is omitted, it defaults to the value of the CURRENT SCHEMA special register.
ATABLE: The name of the table within ASCHEMA for which authorizations are to be saved.

Examples¶

Save the authorizations associated with the FINANCE.LEDGER table, drop the table, recreate it with a definition derived from another table, then restore the authorizations:

SET SCHEMA FINANCE;
CALL SAVE_AUTH('LEDGER');
DROP TABLE LEDGER;
CREATE TABLE LEDGER LIKE LEDGER_TEMPLATE;
CALL RESTORE_AUTH('LEDGER');

Advanced usage: Copy the authorizations associated with FINANCE.SALES to FINANCE.SALES_HISTORY by changing the content of the SAVED_AUTH table (which is structured identically to the SYSCAT.TABAUTH table) between calls to SAVE_AUTH procedure and RESTORE_AUTH procedure:

SET SCHEMA FINANCE;
CALL SAVE_AUTH('SALES');
UPDATE UTILS.SAVED_AUTH
    SET TABNAME = 'SALES_HISTORY'
    WHERE TABNAME = 'SALES'
    AND TABSCHEMA = CURRENT SCHEMA;
CALL RESTORE_AUTH('SALES_HISTORY');