When you create an account in the ApsaraDB RDS console, you can grant it permissions on entire databases. To restrict an account to specific tables, views, or individual fields, run SQL statements directly against the instance to apply fine-grained authorization.
Prerequisites
Before you begin, ensure that you have:
A privileged account. For instructions, see Create an account.
How it works
Fine-grained authorization in MySQL uses layered privileges. Each level is independent, so a complete authorization typically requires multiple GRANT statements.
| Level | Syntax | Supported privilege types |
|---|---|---|
| Global | ON *.* | PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, and others |
| Table | ON db.table | ALL PRIVILEGES, SELECT, UPDATE, INSERT, DELETE, and others |
| View | ON db.view | SELECT only |
| Column | ON TABLE table (col) | SELECT, UPDATE, and others |
Accounts created through this procedure cannot view their authorized databases in the ApsaraDB RDS console. To grant console visibility for a database, use a wildcard table name (*) instead of a specific table name when granting table permissions.Grant table permissions
Use a privileged account to connect to the RDS instance, then run the following SQL statements.
Step 1: Create an account.
CREATE USER `<username>`@`%` IDENTIFIED BY '<password>';Step 2: Grant global permissions.
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<username>'@'%';Step 3: Grant full permissions on a specific table.
GRANT ALL PRIVILEGES ON `<database>`.`<table>` TO '<username>'@'%';Replace<table>with*to grant access to all tables in the database. This also allows the account to view the database in the ApsaraDB RDS console.
Step 4: Grant read-only permissions on commonly used mysql system tables.
GRANT SELECT ON `mysql`.`help_topic` TO '<username>'@'%';
GRANT SELECT ON `mysql`.`func` TO '<username>'@'%';
GRANT SELECT ON `mysql`.`time_zone` TO '<username>'@'%';
GRANT SELECT ON `mysql`.`slow_log` TO '<username>'@'%';
GRANT SELECT ON `mysql`.`proc` TO '<username>'@'%';
GRANT SELECT ON `mysql`.`general_log` TO '<username>'@'%';
-- Grant permissions on other system tables based on your requirements.Examples
Authorize an account to manage one table
Create account test01 with full access to table test100 in database rds001.
CREATE USER `test01`@`%` IDENTIFIED BY 'passwd';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test01'@'%';
GRANT ALL PRIVILEGES ON `rds001`.`test100` TO 'test01'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'test01'@'%';
GRANT SELECT ON `mysql`.`func` TO 'test01'@'%';Authorize an account to manage multiple tables
Create account test02 with full access to tables test100, test200, and test300 in database rds001.
CREATE USER `test02`@`%` IDENTIFIED BY 'passwd';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test02'@'%';
GRANT ALL PRIVILEGES ON `rds001`.`test100` TO 'test02'@'%';
GRANT ALL PRIVILEGES ON `rds001`.`test200` TO 'test02'@'%';
GRANT ALL PRIVILEGES ON `rds001`.`test300` TO 'test02'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'test02'@'%';
GRANT SELECT ON `mysql`.`func` TO 'test02'@'%';Grant view permissions
SELECT is the only privilege type you can grant at the view level.
GRANT SELECT ON <database>.<view> TO <username>;Examples
Authorize an account to query one view
GRANT SELECT ON rds001.view_test1 TO test01;Authorize an account to query multiple views
GRANT SELECT ON rds001.view_test1 TO test01;
GRANT SELECT ON rds001.view_test2 TO test01;Grant field permissions
Column-level grants restrict access to specific fields within a table. Each privilege type must be followed by the column name in parentheses.
GRANT UPDATE (<field>) ON TABLE <table> TO <username>; -- Authorize updates to a field.
GRANT SELECT (<field>) ON TABLE <table> TO <username>; -- Authorize queries on a field.Examples
Authorize an account to update one field
GRANT UPDATE (testid) ON TABLE testtable TO test01;Authorize an account to update multiple fields
GRANT UPDATE (test_id) ON TABLE testtable TO test01;
GRANT UPDATE (test_name) ON TABLE testtable TO test01;Alternative: use Data Management (DMS)
Run the same SQL statements in Data Management (DMS) instead of a client or CLI.
FAQ
I get "Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation" when running the SQL statements. What should I do?
The account you used lacks the CREATE USER privilege. Use the privileged account to run the statements, or grant the privilege to your current account first:
GRANT CREATE USER ON *.* TO 'your_user'@'host';
FLUSH PRIVILEGES;