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:
- System tables with built-in restrictions that determine who can access information remain accessible. For example, the SESSIONS table allows a user to see only information on that user's current session when RestrictSystemTables is enabled.
-
System tables that contain settings that a user should be allowed to see are accessible when RestrictSystemTables is enabled. For example, the TYPES system table.
- System tables that contain information about other users are restricted.
- System tables that contain information about database settings are restricted.
- The behavior of the SYSMONITOR Role does not change.
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