RESTORE_AUTHS procedure

Restores the authorizations of all relations in the specified schema that were previously saved with SAVE_AUTHS procedure

Prototypes

RESTORE_AUTHS(ASCHEMA VARCHAR(128))
RESTORE_AUTHS()

Description

RESTORE_AUTHS is a utility procedure which restores the authorization settings (previously saved with SAVE_AUTHS procedure) for all tables in the specified schema. If no schema is specified, the current schema is used.

Warning

The procedure only attempts to restore settings for those tables or views which currently exist, and for which settings were previously saved. If you use SAVE_AUTHS procedure on a schema, drop several objects from the schema and then call RESTORE_AUTHS procedure on that schema, the procedure will succeed with no error, although several authorization settings have not been restored. Furthermore, any settings associated with the specified schema that are not restored are removed from store used by SAVE_AUTHS procedure (SAVED_AUTH in the schema containing the procedures).

Parameters

ASCHEMA
The name of the schema containing the tables for which to restore authorziation settings. If this parameter is omitted the value of the CURRENT SCHEMA special register will be used instead.

Examples

Save all the authorization information from the tables in the FINANCE_DEV schema, do something arbitrary to the schema and restore the authorizations again:

SET SCHEMA FINANCE_DEV;
CALL SAVE_AUTHS();
-- Do something arbitrary to the schema (e.g. run a script to manipulate its structure)
CALL RESTORE_AUTHS();

Advanced usage: Copy the authorizations from the FINANCE_DEV schema to the FINANCE schema by changing the content of SAVED_AUTH (this is the table in which SAVE_AUTH procedure temporarily stores authorizations; it has exactly the same structure as SYSCAT.TABAUTH):

CALL SAVE_AUTHS('FINANCE_DEV');
UPDATE UTILS.SAVED_AUTH
    SET TABSCHEMA = 'FINANCE'
    WHERE TABSCHEMA = 'FINANCE_DEV';
CALL RESTORE_AUTHS('FINANCE');