📣

TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →

Get Started

Quick Start

Guides

Connect
Manage Resources
Data Integration
Load Data
Unload Data
Multimodal Data Analytics
Performance Tuning
Security & Reliability
Management

Troubleshooting

Tutorials

Ingest & Stream Data
Migrate Data

Reference

SQL Reference
General Reference

Support Services

GRANT

Grants privileges, roles, and ownership for a specific database object. This includes:

Granting privileges to roles.
Assigning roles to users or other roles.
Transferring ownership to a role.

Syntax

Granting Privileges

To understand what a privilege is and how it works, see Privileges.

Note

CREATE-like privileges that create ownership objects cannot be granted directly to a user. These privileges must be granted to a role first, and then the role can be assigned to users. This includes:

CREATE
CREATE DATABASE
CREATE WAREHOUSE
CREATE CONNECTION
CREATE SEQUENCE
CREATE PROCEDURE
CREATE MASKING POLICY
CREATE ROW ACCESS POLICY

Since ALL includes these CREATE privileges, GRANT ALL ... TO USER will also fail. For example, GRANT ALL ON *.* TO USER u1 or GRANT CREATE DATABASE ON *.* TO USER u1 will fail. Instead, use:

GRANT ALL ON *.* TO ROLE r1;
GRANT ROLE r1 TO USER u1;

GRANT {
        schemaObjectPrivileges | ALL [ PRIVILEGES ] ON <privileges_level>
      }
TO ROLE <role_name>

Where:

schemaObjectPrivileges ::=
-- For TABLE
  { SELECT | INSERT }

-- For SCHEMA
  { CREATE | DROP | ALTER }

-- For USER
  { CREATE USER }

-- For ROLE
  { CREATE ROLE}

-- For STAGE
  { READ, WRITE }

-- For UDF
  { USAGE }

-- For MASKING POLICY (account-level privileges)
  { CREATE MASKING POLICY | APPLY MASKING POLICY }

-- For ROW ACCESS POLICY (account-level privileges)
  { CREATE ROW ACCESS POLICY | APPLY ROW ACCESS POLICY }

privileges_level ::=
    *.*
  | db_name.*
  | db_name.tbl_name
  | STAGE <stage_name>
  | UDF <udf_name>
  | MASKING POLICY <policy_name>
  | ROW ACCESS POLICY <policy_name>

Granting Masking Policy Privileges

Use the following forms to manage access to individual masking policies:

GRANT APPLY ON MASKING POLICY <policy_name> TO ROLE <role_name>
GRANT ALL [ PRIVILEGES ] ON MASKING POLICY <policy_name> TO ROLE <role_name>
GRANT OWNERSHIP ON MASKING POLICY <policy_name> TO ROLE '<role_name>'

CREATE MASKING POLICY allows a role to create new masking policies.
APPLY MASKING POLICY lets grantees attach, detach, describe, or drop any masking policy when combined with the appropriate ALTER TABLE or policy commands.
GRANT APPLY ON MASKING POLICY ... authorizes the grantee to manage a specific masking policy without granting global access.
OWNERSHIP provides full control over the masking policy; TiDB Cloud Lake automatically grants OWNERSHIP on a new policy to the creator role and revokes it when the policy is dropped.

Granting Row Access Policy Privileges

Use these forms to manage access to individual row access policies:

GRANT APPLY ON ROW ACCESS POLICY <policy_name> TO ROLE <role_name>
GRANT ALL [ PRIVILEGES ] ON ROW ACCESS POLICY <policy_name> TO ROLE <role_name>
GRANT OWNERSHIP ON ROW ACCESS POLICY <policy_name> TO ROLE '<role_name>'

CREATE ROW ACCESS POLICY allows a role to create new row access policies.
APPLY ROW ACCESS POLICY authorizes attaching or detaching any row access policy from tables, along with DESCRIBE/DROP commands.
GRANT APPLY ON ROW ACCESS POLICY ... limits access to a specific row access policy.
OWNERSHIP delivers full control over the row access policy; the creator role receives OWNERSHIP automatically and loses it when the policy is dropped.

Granting Role

To understand what a role is and how it works, see Roles.

-- Grant a role to a user
GRANT ROLE <role_name> TO <user_name>

-- Grant a role to a role
GRANT ROLE <role_name> TO ROLE <role_name>

Note

default_role is a user property — it's set when you CREATE USER or ALTER USER, and GRANT/REVOKE won't touch it. So if you later revoke a role that happens to be someone's default_role, the setting stays but the role membership is gone. Use ALTER USER ... WITH DEFAULT_ROLE to update it explicitly.

Granting Ownership

To understand what ownership is and how it works, see Ownership.

-- Grant ownership of a specific table within a database to a role
GRANT OWNERSHIP ON <database_name>.<table_name> TO ROLE '<role_name>'

-- Grant ownership of a stage to a role
GRANT OWNERSHIP ON STAGE <stage_name> TO ROLE '<role_name>'

-- Grant ownership of a user-defined function (UDF) to a role
GRANT OWNERSHIP ON UDF <udf_name> TO ROLE '<role_name>'

Examples

Example 1: Granting Privileges to a Role

Create a role:

CREATE ROLE user1_role;

Grant the ALL privilege on all existing tables in the default database to the role user1_role:

GRANT ALL ON default.* TO ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+--------------------------------------------------+
| Grants                                           |
+--------------------------------------------------+
| GRANT ALL ON 'default'.* TO ROLE 'user1_role'    |
+--------------------------------------------------+

Grant the ALL privilege on all databases to the role user1_role:

GRANT ALL ON *.* TO ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+--------------------------------------------------+
| Grants                                           |
+--------------------------------------------------+
| GRANT ALL ON 'default'.* TO ROLE 'user1_role'    |
| GRANT ALL ON *.* TO ROLE 'user1_role'            |
+--------------------------------------------------+

Grant the ALL privilege on the stage named s1 to the role user1_role:

GRANT ALL ON STAGE s1 TO ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+--------------------------------------------------+
| Grants                                           |
+--------------------------------------------------+
| GRANT ALL ON STAGE s1 TO ROLE 'user1_role'       |
+--------------------------------------------------+

Grant the ALL privilege on the UDF named f1 to the role user1_role:

GRANT ALL ON UDF f1 TO ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+--------------------------------------------------+
| Grants                                           |
+--------------------------------------------------+
| GRANT ALL ON UDF f1 TO ROLE 'user1_role'         |
+--------------------------------------------------+

Example 2: Granting Specific Privileges to a Role

Grant the SELECT privilege on all existing tables in the mydb database to the role role1:

Create role:

CREATE ROLE role1;

Grant privileges to the role:

GRANT SELECT ON mydb.* TO ROLE role1;

Show the grants for the role:

SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants                              |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+

Example 3: Granting a Role to a User

Create a user:

CREATE USER user1 IDENTIFIED BY 'abc123' WITH DEFAULT_ROLE = 'role1';

Role role1 grants are:

SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants                              |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+

Grant role role1 to user user1:

 GRANT ROLE role1 TO user1;

Now, user user1 grants are:

SHOW GRANTS FOR user1;
+-------------------------------------+
| Grants                              |
+-------------------------------------+
| GRANT ROLE role1 TO 'user1'@'%'     |
+-------------------------------------+

Example 4: Granting Ownership to a Role

-- Grant ownership of all tables in the 'finance_data' database to the role 'data_owner'
GRANT OWNERSHIP ON finance_data.* TO ROLE 'data_owner';

-- Grant ownership of the table 'transactions' in the 'finance_data' schema to the role 'data_owner'
GRANT OWNERSHIP ON finance_data.transactions TO ROLE 'data_owner';

-- Grant ownership of the stage 'ingestion_stage' to the role 'data_owner'
GRANT OWNERSHIP ON STAGE ingestion_stage TO ROLE 'data_owner';

-- Grant ownership of the user-defined function 'calculate_profit' to the role 'data_owner'
GRANT OWNERSHIP ON UDF calculate_profit TO ROLE 'data_owner';

Example 5: Granting Masking Policy Privileges

-- Allow the current user to create masking policies
GRANT CREATE MASKING POLICY ON *.* TO ROLE security_admin;

-- Create a masking policy while assuming the security_admin role
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> '***';

-- Grant a role the ability to apply the policy when altering tables
GRANT APPLY ON MASKING POLICY email_mask TO ROLE pii_readers;

-- Review the masking policy privileges
SHOW GRANTS ON MASKING POLICY email_mask;

Example 6: Granting Row Access Policy Privileges

-- Allow the current role to create row access policies
GRANT CREATE ROW ACCESS POLICY ON *.* TO ROLE row_policy_admin;

-- Define a row access policy while assuming the row_policy_admin role
CREATE ROW ACCESS POLICY rap_region AS (region STRING) RETURNS BOOLEAN -> region = 'APAC';

-- Allow a role to apply the policy when altering tables
GRANT APPLY ON ROW ACCESS POLICY rap_region TO ROLE apac_only;

-- Review the row access policy privileges
SHOW GRANTS ON ROW ACCESS POLICY rap_region;