Skip to content

column-privilege-management.md

Latest commit

 

History

History
172 lines (123 loc) · 8.2 KB

column-privilege-management.md

File metadata and controls

172 lines (123 loc) · 8.2 KB
title Column-Level Privilege Management
summary TiDB supports a MySQL-compatible column-level privilege management mechanism, enabling you to grant or revoke `SELECT`, `INSERT`, `UPDATE`, and `REFERENCES` privileges on specific columns of a table using `GRANT` or `REVOKE`, thus achieving finer-grained access control.

Column-Level Privilege Management

Starting from v8.5.6, TiDB supports a MySQL-compatible column-level privilege management mechanism. With column-level privileges, you can grant or revoke SELECT, INSERT, UPDATE, and REFERENCES privileges on specific columns of a table, achieving finer-grained data access control.

Note:

Although MySQL syntax allows column-level specification like REFERENCES(col_name), REFERENCES itself is a database/table-level privilege used for foreign key-related permission checks. Therefore, column-level REFERENCES does not actually take effect in MySQL. TiDB's behavior is consistent with MySQL.

Syntax

Granting and revoking column-level privileges are similar to table-level privileges, with the following differences:

  • The column name list is placed after the privilege type, not after the table name.
  • Multiple column names are separated by commas (,).
GRANT priv_type(col_name [, col_name] ...) [, priv_type(col_name [, col_name] ...)] ...
    ON db_name.tbl_name
    TO 'user'@'host';

REVOKE priv_type(col_name [, col_name] ...) [, priv_type(col_name [, col_name] ...)] ...
    ON db_name.tbl_name
    FROM 'user'@'host';

Where:

  • priv_type supports SELECT, INSERT, UPDATE, and REFERENCES.
  • The ON clause requires specifying the specific table, for example, test.tbl.
  • A single GRANT or REVOKE statement can include multiple privilege items, and each privilege item can specify its own list of column names.

For example, the following statement grants SELECT privileges on col1 and col2 and UPDATE privilege on col3 to the user:

GRANT SELECT(col1, col2), UPDATE(col3) ON test.tbl TO 'user'@'host';

Grant column-level privileges

The following example grants the SELECT privilege on col1 and col2 of table test.tbl to user newuser, and grants the UPDATE privilege on col3 to the same user:

CREATE DATABASE IF NOT EXISTS test;
USE test;

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (col1 INT, col2 INT, col3 INT);

DROP USER IF EXISTS 'newuser'@'%';
CREATE USER 'newuser'@'%';

GRANT SELECT(col1, col2), UPDATE(col3) ON test.tbl TO 'newuser'@'%';
SHOW GRANTS FOR 'newuser'@'%';
+---------------------------------------------------------------------+
| Grants for newuser@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%'                                 |
| GRANT SELECT(col1, col2), UPDATE(col3) ON test.tbl TO 'newuser'@'%' |
+---------------------------------------------------------------------+

In addition to using SHOW GRANTS, you can also view column-level privilege information by querying INFORMATION_SCHEMA.COLUMN_PRIVILEGES.

Revoke column-level privileges

The following example revokes the SELECT privilege on column col2 from user newuser:

REVOKE SELECT(col2) ON test.tbl FROM 'newuser'@'%';
SHOW GRANTS FOR 'newuser'@'%';
+---------------------------------------------------------------+
| Grants for newuser@%                                          |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%'                           |
| GRANT SELECT(col1), UPDATE(col3) ON test.tbl TO 'newuser'@'%' |
+---------------------------------------------------------------+

Column-level privilege access control example

After granting or revoking column-level privileges, TiDB performs privilege checks on columns referenced in SQL statements. For example:

  • SELECT statements: SELECT column privileges affect columns referenced in the SELECT list as well as WHERE, ORDER BY, and other clauses.
  • UPDATE statements: columns being updated in the SET clause require UPDATE column privileges. Columns read in expressions or conditions usually also require SELECT column privileges.
  • INSERT statements: columns being written to require INSERT column privileges. INSERT INTO t VALUES (...) is equivalent to writing to all columns.

In the following example, user newuser can only query col1 and update col3:

-- Execute as newuser
SELECT col1 FROM tbl;
SELECT * FROM tbl; -- Error (missing SELECT column privilege for col2, col3)

UPDATE tbl SET col3 = 1;
UPDATE tbl SET col1 = 2; -- Error (missing UPDATE column privilege for col1)

UPDATE tbl SET col3 = col1;
UPDATE tbl SET col3 = col3 + 1; -- Error (missing SELECT column privilege for col3)
UPDATE tbl SET col3 = col1 WHERE col1 > 0;

Compatibility differences with MySQL

TiDB's column-level privileges are generally compatible with MySQL. However, there are differences in the following scenarios:

Scenario TiDB MySQL
Revoking column-level privileges not granted to a user REVOKE executes successfully. REVOKE throws an error.
Execution order of column pruning and SELECT privilege check SELECT column privileges are checked first, then column pruning is performed. For example: executing SELECT a FROM (SELECT a, b FROM t) s requires SELECT column privileges for both t.a and t.b. Column pruning is performed first, then SELECT column privileges are checked. For example: executing SELECT a FROM (SELECT a, b FROM t) s only requires SELECT column privilege for t.a.

Column pruning and privilege checks in view scenarios

When performing SELECT privilege checks on views, MySQL and TiDB differ as follows:

  • MySQL first prunes columns in the view's internal query and then checks the column privileges of the internal tables, making the checks relatively lenient in some scenarios.
  • TiDB does not perform column pruning before privilege checks, so additional column privileges might be required.
-- Prepare the environment by logging in as root
DROP USER IF EXISTS 'u'@'%';
CREATE USER 'u'@'%';

DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT, b INT, c INT, d INT);

DROP VIEW IF EXISTS v;
CREATE SQL SECURITY INVOKER VIEW v AS SELECT a, b FROM t WHERE c = 0 ORDER BY d;

GRANT SELECT ON v TO 'u'@'%';

-- Log in as u
SELECT a FROM v;
-- MySQL: Error, missing access privileges for t.a, t.c, t.d
-- TiDB: Error, missing access privileges for t.a, t.b, t.c, t.d

-- Log in as root
GRANT SELECT(a, c, d) ON t TO 'u'@'%';

-- Log in as u
SELECT a FROM v;
-- MySQL: Success (internal query is pruned to `SELECT a FROM t WHERE c = 0 ORDER BY d`)
-- TiDB: Error, missing access privileges for t.b

SELECT * FROM v;
-- MySQL: Error, missing access privileges for t.b
-- TiDB: Error, missing access privileges for t.b

-- Log in as root
GRANT SELECT(b) ON t TO 'u'@'%';

-- Log in as u
SELECT * FROM v;
-- MySQL: Success
-- TiDB: Success

See also