System Table Restriction

The security parameter, RestrictSystemTables, prohibits users without database administrator privileges from viewing potentially sensitive information in system tables. For example, as a database administrator you may want to restrict access to the CONFIGURATION_PARAMETERS table to prevent any inadvertent modifications.

RestrictSystemTables is disabled by default. Enable the RestrictSystemTables parameter as follows:

=> ALTER DATABASE <database name> SET PARAMETER RestrictSystemTables=1; WARNING 4324:  Parameter RestrictSystemTables will not take effect until database restart

Disable the RestrictSystemTables parameter as follows:

=> ALTER DATABASE <database name> SET PARAMETER RestrictSystemTables=0; WARNING 4324:  Parameter RestrictSystemTables will not take effect until database restart

Note: You must restart the server to implement any changes to RestrictSystemTables.

When you enable RestrictSystemTables, the following conditions take affect:

Privileges

You must be a database administrator user to modify RestrictSystemTables settings.

Determine if a Table is Restricted

Use the SYSTEM_TABLES to determine if a table is accessible. The table contains a column called is_accessible_during_lockdown.This flag indicates if a table is accessible when RestrictedSystemTables is enabled. Valid values are:

t — A table is accessible

f — A table is not accessible

Show Accessible System Tables

The following examples show how you can query system tables that have restrictions.

Query the contents of all the system tables that have the is_accessible_during_lockdown flag set for each table:

=> SELECT * FROM system_tables; table_schema_id                     | catalog table_name                          | sales_projections table_id                            | 834868 table_description                   | sales forecast 2016 is_superuser_only                   | f is_monitorable                      | f is_accessible_during_lockdown       | t

In the preceding example, the sales_projection table remains accessible to all users when RestrictSystemTables is enabled.

Query a list of all tables that are accessible when RestrictSystemTables is enabled:

=> SELECT table_name from system_tables where is_accessible_during_lockdown='t'; -[ RECORD 1 ]----------------------------------------- table_name | user_audits -[ RECORD 2 ]----------------------------------------- table_name | views -[ RECORD 3 ]----------------------------------------- table_name | dual -[ RECORD 4 ]----------------------------------------- table_name | user_transforms

Query a list of all tables that are not accessible when RestrictSystemTables is enabled:

=> SELECT table_name from system_tables where is_accessible_during_lockdown='f'; -[ RECORD 1 ]----------------------------------------- table_name | user_client_auth -[ RECORD 2 ]----------------------------------------- table_name | directed_queries -[ RECORD 3 ]----------------------------------------- table_name | storage_locations -[ RECORD 4 ]----------------------------------------- table_name | system_columns