Permission management in the three-role mode
This topic describes the permissions of the three types of administrators in the three-role mode.
After the three-role mode is enabled for PolarDB-X, the default privileged accounts are the database administrator (DBA) account, database security administrator (DSA) account, and data audit administrator (DAA) account. For more information about the features of the three-role mode and how to use the mode, see Three-role mode. The following sections describe the SQL statements that the three types of administrator can execute. The following list provides the usernames of the three types of administrator:
DBA: admin_dba
DSA: admin_security
DAA: admin_audit
DBA
Only the DBA is authorized to execute DDL statements.
SELECT USER();
+--------------------------+
| USER() |
+--------------------------+
| admin_dba@10.159.164.179 |
+--------------------------+
create database priv_test;
Query OK, 1 row affected (0.09 sec)
use priv_test;
Database changed
create table test (id int primary key, value int);
Query OK, 0 rows affected (1.23 sec)
The DBA is not authorized to execute DML, Data Query Language (DQL), and Data Access Language (DAL) statements or manage permissions.
select * from test;
ERROR 5108 (HY000): [130b87654f001000][10.2.57.181:3028][priv_test]ERR-CODE: [TDDL-5108][ERR_CHECK_PRIVILEGE_FAILED_ON_TABLE] User admin_dba@'10.159.164.179' does not have 'SELECT' privilege on table 'TEST'. Database is PRIV_TEST.
insert into test values (1, 123);
ERROR 5108 (HY000): [130b877647c01000][10.2.57.181:3028][priv_test]ERR-CODE: [TDDL-5108][ERR_CHECK_PRIVILEGE_FAILED_ON_TABLE] User admin_dba@'10.159.164.179' does not have 'INSERT' privilege on table 'TEST'. Database is PRIV_TEST.
CREATE USER 'user1'@'%' IDENTIFIED BY '123456';
ERROR 5110 (HY000): [130b877e6f001000][10.2.57.181:3028][priv_test]ERR-CODE: [TDDL-5110][ERR_CHECK_PRIVILEGE_FAILED] User admin_dba@'%' does not have 'CREATE ACCOUNT' privilege.
DSA
The DSA is not authorized to execute DML, DQL, and DAL statements. The DSA is authorized to manage the permissions of accounts or roles and grant DML, DQL, and DAL permissions to standard accounts.
SELECT USER();
+-------------------------------+
| USER() |
+-------------------------------+
| admin_security@10.159.164.119 |
+-------------------------------+
use priv_test;
Database changed
select * from test;
ERROR 5108 (HY000): [130b8a31af401000][10.57.23.233:3028][priv_test]ERR-CODE: [TDDL-5108][ERR_CHECK_PRIVILEGE_FAILED_ON_TABLE] User admin_security@'10.159.164.119' does not have 'SELECT' privilege on table 'TEST'. Database is PRIV_TEST.
CREATE USER 'user1'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.08 sec)
GRANT SELECT,INSERT,UPDATE ON priv_test.* TO 'user1'@'%';
Query OK, 0 rows affected (0.06 sec)
-- Log on to the database by using the user1 account.
SELECT USER();
+---------------------+
| USER() |
+---------------------+
| user1@10.159.164.29 |
+---------------------+
show grants;
+------------------------------------------------------------+
| GRANTS FOR 'USER1'@'%' |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%' |
| GRANT SELECT, INSERT, UPDATE ON priv_test.* TO 'user1'@'%' |
+------------------------------------------------------------+
insert into test values (1, 123);
Query OK, 1 row affected (0.02 sec)
select * from test;
+------+-------+
| id | value |
+------+-------+
| 1 | 123 |
+------+-------+
The DSA is not authorized to execute DDL statements.
drop table test;
ERROR 5108 (HY000): [130b8a1b9dc01000][10.2.57.181:3028][priv_test]ERR-CODE: [TDDL-5108][ERR_CHECK_PRIVILEGE_FAILED_ON_TABLE] User admin_security@'10.159.164.59' does not have 'DROP' privilege on table 'TEST'. Database is PRIV_TEST.
DAA
The DAA is authorized only to view audit logs.
SELECT USER();
+----------------------------+
| USER() |
+----------------------------+
| admin_audit@10.159.164.209 |
+----------------------------+
SELECT USER_NAME,HOST,PORT,AUDIT_INFO,ACTION,TRACE_ID from polardbx_audit_log where SCHEMA = 'priv_test';
+----------------+----------------+-------+----------------------------------------------------+-------------+------------------+
| USER_NAME | HOST | PORT | AUDIT_INFO | ACTION | TRACE_ID |
+----------------+----------------+-------+----------------------------------------------------+-------------+------------------+
| admin_dba | 10.159.164.239 | 26245 | create table test (id int primary key, value int) | CREATE | 130b83120e003000 |
| admin_security | 10.159.164.239 | 37537 | create table test2 (id int primary key, value int) | CREATE | 130b839700402000 |
| admin_audit | 10.159.164.89 | 51128 | create table test2 (id int primary key, value int) | CREATE | 130b83ea42404000 |
| admin_dba | 10.159.164.119 | 15923 | CREATE USER 'user1'@'%' IDENTIFIED BY '123456' | CREATE | 130b8658c9c03000 |
| admin_dba | 10.159.164.119 | 15923 | CREATE USER 'user1'@'%' IDENTIFIED BY '123456' | CREATE | 130b866b49c03000 |
| admin_dba | 10.159.164.179 | 24559 | CREATE USER 'user1'@'%' IDENTIFIED BY '123456' | CREATE | 130b877e6f001000 |
| admin_security | 10.159.164.119 | 44965 | create table test2 (id int primary key, value int) | CREATE | 130b87c6f6002000 |
| admin_security | 10.159.164.119 | 44965 | CREATE USER 'user1'@'%' IDENTIFIED BY '123456' | CREATE_USER | 130b87ee65402000 |
| admin_security | 10.159.164.119 | 44965 | CREATE USER 'user1'@'%' IDENTIFIED BY '123456' | CREATE | 130b87ee65402000 |
| admin_security | 10.159.164.119 | 44965 | GRANT SELECT,UPDATE ON priv_test.* TO 'user1'@'%' | GRANT | 130b88a7b0402000 |
| admin_security | 10.159.164.59 | 21156 | drop table test | DROP | 130b8a1b9dc01000 |
+----------------+----------------+-------+----------------------------------------------------+-------------+------------------+