docs/design/2021-03-09-security-enhanced-mode.md
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.
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:
SUPER to a set of capabilities that are safe for end users.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:
The following system variables will be reset to defaults:
The following status variables will be hidden unless the user has the RESTRICTED_STATUS_ADMIN privilege:
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
The following tables will be modified to hide columns unless the user has the RESTRICTED_TABLES_ADMIN privilege:
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
The following tables will be hidden unless the user has the RESTRICTED_TABLES_ADMIN privilege:
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 CONFIG is changed to require the CONFIG privilege (with or without SEM enabled).SET CONFIG is disabled by the CONFIG Privilege (no change necessary)BACKUP and RESTORE commands prevent local backups and restores.SELECT .. INTO OUTFILE is disabled (this is the only current usage of the FILE privilege, effectively disabling FILE. For compatibility GRANT and REVOKE of FILE will 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:
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:
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_enabled variable 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