- Author(s): morgo
- Last updated: May 04, 2021
- Discussion at: N/A
- Introduction
- Motivation or Background
- Detailed Design
- Test Design
- Impacts & Risks
- Investigation & Alternatives
- Unresolved Questions
This document was created to discuss the design of Security Enhanced Mode. It comes from the DBaaS requirement that SUPER users must not be able to perform certain actions that could compromise the system.
- Configuration Option: The name of a variable as set in a configuration file.
- System Variable (aka sysvar): The name of a variable that is set in a running TiDB server using the MySQL protocol.
- Super: The primary MySQL "admin" privilege, which is intended to be superseded by MySQL’s "dynamic" (fine-grained) privileges starting from MySQL 8.0.
Currently the MySQL SUPER privilege encapsulates a very large set of system capabilities. It does not follow the best practices of allocating fine grained access to users based only on their system-access requirements.
This is particularly problematic in a DBaaS scenario such as TiDB Cloud where the SUPER privilege has elements that are required by both end users (TiDB Cloud Customers) and system operations (PingCAP SREs).
The design of Security Enhanced Mode (SEM) takes the approach of:
- Restricting
SUPERto a set of capabilities that are safe for end users. - Implementation of dynamic privileges (issue #22439).
This approach was requested by product management based on the broad "in the wild" association of SUPER as "the MySQL admin privilege". Thus, proposals to create a new lesser-SUPER privilege have already been discussed and rejected.
The design and name of "Security Enhanced" is inspired by prior art with SELinux and AppArmor.
A boolean option called EnableEnhancedSecurity (default FALSE) will be added as a TiDB configuration option. The following subheadings describe the behavior when EnableEnhancedSecurity is set to TRUE.
The following system variables will be hidden unless the user has the RESTRICTED_VARIABLES_ADMIN privilege:
- variable.TiDBDDLSlowOprThreshold,
- variable.TiDBAllowRemoveAutoInc,
- variable.TiDBCheckMb4ValueInUTF8,
- variable.TiDBConfig,
- variable.TiDBEnableSlowLog,
- variable.TiDBEnableTelemetry,
- variable.TiDBExpensiveQueryTimeThreshold,
- variable.TiDBForcePriority,
- variable.TiDBGeneralLog,
- variable.TiDBMetricSchemaRangeDuration,
- variable.TiDBMetricSchemaStep,
- variable.TiDBOptWriteRowID,
- variable.TiDBPProfSQLCPU,
- variable.TiDBRecordPlanInSlowLog,
- variable.TiDBRowFormatVersion,
- variable.TiDBSlowQueryFile,
- variable.TiDBSlowLogThreshold,
- variable.TiDBEnableCollectExecutionInfo,
- variable.TiDBMemoryUsageAlarmRatio,
- variable.TiDBRedactLog
The following system variables will be reset to defaults:
- variable.Hostname
The following status variables will be hidden unless the user has the RESTRICTED_STATUS_ADMIN privilege:
- tidb_gc_leader_desc
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
- cluster_config
- cluster_hardware
- cluster_load
- cluster_log
- cluster_systeminfo
- inspection_result
- inspection_rules
- inspection_summary
- metrics_summary
- metrics_summary_by_label
- metrics_tables
- tidb_hot_regions
The following tables will be modified to hide columns unless the user has the RESTRICTED_TABLES_ADMIN privilege:
- tikv_store_status
- The address, capacity, available, start_ts and uptime columns will return NULL.
- Tidb_servers_info
- The “IP” column will return NULL.
- cluster_* tables
- The “instance” column will show the server ID instead of the server IP address.
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
- pd_profile_allocs
- pd_profile_block
- pd_profile_cpu
- pd_profile_goroutines
- pd_profile_memory
- pd_profile_mutex
- tidb_profile_allocs
- tidb_profile_block
- tidb_profile_cpu
- tidb_profile_goroutines
- tidb_profile_memory
- tidb_profile_mutex
- tikv_profile_cpu
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
- expr_pushdown_blacklist
- gc_delete_range
- gc_delete_range_done
- opt_rule_blacklist
- tidb
- global_variables
The remaining system tables will be limited to read-only operations and can not create new tables.
All tables will be hidden, including the schema itself unless the user has the RESTRICTED_TABLES_ADMIN privilege.
SHOW CONFIGis changed to require theCONFIGprivilege (with or without SEM enabled).SET CONFIGis disabled by theCONFIGPrivilege (no change necessary)- The
BACKUPandRESTOREcommands prevent local backups and restores. - The statement
SELECT .. INTO OUTFILEis disabled (this is the only current usage of theFILEprivilege, effectively disablingFILE. For compatibilityGRANTandREVOKEofFILEwill not be affected.)
TiDB currently permits the SUPER privilege as a substitute for any dynamic privilege. This is not 100% MySQL compatible - MySQL accepts SUPER in most cases, but not in GRANT context. However, TiDB requires this extension because:
- The visitorInfo framework does not permit OR conditions
- GRANT ALL in TiDB does not actually grant each of the individual privileges (historical difference)
When SEM is enabled, SUPER will no longer be permitted as a substitute for any RESTRICTED_* privilege. The distinction that this only applies when SEM is enabled, helps continue to work around the current server limitations.
The integration test suite will run with EnableEnhancedSecurity=FALSE, but new integration tests will be written to cover specific use cases.
Unit tests will be added to cover the enabling and disabling of sysvars, and tables.
Tests will need to check that invisible tables are both non-visible and non-grantable (it should work, since visibility can be plugged into the privilege manager directly).
If the user with SUPER privilege grants privileges related to these tables to other users, for example, GRANT SELECT, INSERT, UPDATE ON information_schema.cluster_config TO 'userA'@'%%'; -- it should fail.
It is important that users can still use TiDB with all connectors when SEM is enabled, and that the TiDB documentation makes sense for users with SEM enabled.
It is not expected that any user scenarios are affected by SEM, but see "Impact & Risks" for additional discussion behind design decisions.
We will need to consider the impact on tools. When SEM is disabled, no impact is expected. When SEM is enabled, it should be possible to make recommendations to the tools team so that they can still access meta data required to operate in DBaaS environment:
- Lightning and BR will not work currently with SEM + pingcap#21988
- In 5.0 the recommended method for BR/Lightning to get TiKV GC stats should change.
- There is one PR still pending for obtaining statistics: pingcap#22286
No performance impact is expected.
Documentation is critically impacted by SEM, since it should be possible for a manual page to cover the use-case of SEM both enabled and disabled.
Supporting PRs will be required to modify both documentation and functionality so that system variables and/or tables that are hidden by SEM are not required. For example:
-
A further change to move the
new_collation_enabledvariable from mysql.tidb to a status variable has been identified, as it appears on several manual pages. No PR has been created yet.
The impact of SEM only applies in the case that it is enabled, which it is only intended to be on DBaaS (although users of on-premises installations of TiDB may also consider enabling it).
The intention behind SEM is to reduce the impact on end users, who can continue to use SUPER as the defacto "admin" privilege (versus alternatives such as mentioned below). The larger impact will be on System Operators, who will need fine grained privileges to replace the SUPER privilege.
The largest risk with SEM enabled is application/MySQL compatibility. There are a number of SEM behaviors which have been discussed, with the following outcomes:
| Suggestion | Observed Risk | Outcome |
|---|---|---|
| Is it possible to make a system variable non-readable by a non privileged user? | MySQL does not have a semantic where a sysvar would ever be non readable. Non-settable however is fine. | Variables will either be invisible or visible. Never non-readable, although non-writeable is possible (example: sql_log_bin). |
| Is it possible to hide columns in information schema? | Users may depend on ordinality of information_schema table column order. This is particularly likely with tables with useful columns at the start. | Columns will appear with NULL values when they must be hidden. |
| Is it possible to hide sysvars such as hostname? | For MySQL-specific sysvars, there is an increased likelihood applications will read them, and result in an error if they are not present. | For a specific case like hostname, it is a requirement to return a placeholder value such as ‘localhost’, rather than hide the variable. |
Users will also be able to observe if the system they are using has enhanced security mode enabled via the system variable, tidb_enable_enhanced_security (read-only).
The alternative to SEM is to implement fine-grained privileges for end users. This idea has been discussed and rejected. See "Motivation or Background" for context.
Amazon RDS also uses the approach of not granting SUPER to users, and instead offering a set of custom stored procedures to support use-cases that would usually require SUPER. This idea has been rejected.
None