Security Management
Table of Contents
- ALTER USER
- CREATE GROUP
- CREATE ROLE
- CREATE USER
- CURRENT_USER
- DROP GROUP
- DROP ROLE
- DROP USER
- GRANT
- GRANT ROLE
- REVOKE
- REVOKE GROUP
- REVOKE ROLE
- SET PASSWORD
- SHOW GRANTS
- SHOW GROUPS
- SHOW ROLES
- SHOW USERS
- Permissions Matrix
ALTER USER
Syntax:
ALTER USER user[@host] [IDENTIFIED BY 'password'] [SET DEFAULT RESOURCE POOL = poolname] [REQUIRE {SSL | NONE}]
Examples:
CREATE USER tom WITH DEFAULT RESOURCE POOL = general;
ALTER USER tom WITH DEFAULT RESOURCE POOL = executive;
ALTER USER 'test' REQUIRE SSL;
CREATE GROUP
Syntax:
CREATE GROUP 'group_name'
CREATE ROLE
Syntax:
CREATE ROLE 'role_name'
CREATE USER
Syntax:
CREATE USER user[@host] [IDENTIFIED BY 'password'] [WITH DEFAULT RESOURCE POOL = poolname]
Examples:
CREATE USER tom;
GRANT SELECT ON test.* TO 'tom'@'%';
CURRENT_USER
Syntax:
CURRENT_USER()
USER()
Examples:
SELECT CURRENT_USER();
SELECT USER();
DROP GROUP
Syntax:
DROP GROUP [IF EXISTS] 'group_name'
DROP ROLE
Syntax:
DROP ROLE [IF EXISTS] 'role_name'
DROP USER
Syntax:
DROP USER [IF EXISTS] user [, user] ...
GRANT
Syntax:
GRANT priv_type [, priv_type [ ... ]] ON priv_level
TO user_or_role [, user_or_role [ ... ]]
[WITH GRANT OPTION]
[REQUIRE {SSL | NONE}]
where
priv_type:
ALL PRIVILEGES
| SELECT
| INSERT
| UPDATE
| DELETE
| CREATE
| DROP
| RELOAD
| PROCESS
| FILE READ
| FILE WRITE
| INDEX
| ALTER
| SHOW METADATA
| SUPER
| CREATE TEMPORARY TABLES
| LOCK TABLES
| CREATE VIEW
| SHOW VIEW
| CREATE USER
| CLUSTER
| ALTER VIEW
| DROP VIEW
| BACKUP
| CREATE DATABASE
| DROP DATABASE
| CREATE PIPELINE
| DROP PIPELINE
| ALTER PIPELINE
| START PIPELINE
| SHOW PIPELINE
| EXECUTE
| CREATE ROUTINE
| ALTER ROUTINE
priv_level:
*
| *.*
| database.*
| database.table
user_or_role:
user [, user]
| role
user:
'user_name'@'host_name' [IDENTIFIED BY 'password']
role:
ROLE 'role_name'
Examples:
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ROLE
Syntax:
GRANT ROLE 'role_name' TO 'group_name'
Examples:
GRANT ROLE 'full_backup_role' TO 'backup_admins_group';
REVOKE
Syntax:
REVOKE priv_type [, priv_type] ... ON priv_level
FROM user [, user] ...
Examples:
REVOKE INSERT ON *.* FROM 'user'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'%';
REVOKE GROUP
Syntax:
REVOKE GROUP 'role_name' FROM user
Examples:
REVOKE GROUP 'backup_admins' FROM 'username'@'%';
REVOKE ROLE
Syntax:
REVOKE ROLE 'role_name' FROM 'group_name'
Examples:
REVOKE ROLE ‘full_backups_role' FROM 'backup_admins';
SET PASSWORD
Syntax:
SET PASSWORD FOR 'username'@'host' = PASSWORD('password')
SHOW GRANTS
Syntax:
SHOW GRANTS
SHOW GRANTS FOR USER 'user'@'host_name'
SHOW GRANTS FOR ROLE 'role_name'
SHOW GROUPS
Syntax:
SHOW GROUPS
SHOW GROUPS FOR USER 'user'@'host_name'
SHOW GROUPS FOR ROLE 'role_name'
SHOW ROLES
Syntax:
SHOW ROLES
SHOW ROLES FOR USER 'user'@'host_name'
SHOW ROLES FOR GROUP 'group_name'
SHOW USERS
Syntax:
SHOW USERS
SHOW USERS FOR ROLE 'role_name'
SHOW USERS FOR GROUP 'group_name'
Permissions Matrix
| SQL | Allowed Targets in Default Mode | Allowed Targets in Strict Mode | Notes |
|---|---|---|---|
| USAGE | Cluster | Cluster | Connect, show variables |
| SELECT | Cluster, Database, Table | Database, Table | Select rows |
| INSERT | Cluster, Database, Table | Database, Table | Insert rows |
| UPDATE | Cluster, Database, Table | Database, Table | Update cells of existing rows |
| DELETE | Cluster, Database, Table | Database, Table | Delete rows |
| CREATE | Cluster, Database, Table | Database, Table | Create tables |
| DROP | Cluster, Database, Table | Database, Table | Drop tables |
| RELOAD | Cluster, Database | Cluster. Database | Load backups into database |
| FILE WRITE | Cluster | Cluster | File write access |
| PROCESS | Cluster | Cluster | View and kill queries |
| FILE READ | Cluster | Cluster | File read access |
| WITH GRANT OPTION | (disallowed) | WITH GRANT OPTION disabled in strict mode | |
| INDEX | Cluster, Database, Table | Database, Table | Create and drop indexes |
| ALTER | Cluster, Database, Table | Database, Table | Alter tables (including indexes) |
| SHOW METADATA | Cluster | Cluster | Show all metadata |
| SUPER | Cluster | Cluster | Set global variables, modify resource pool settings |
| CREATE TEMPORARY TABLES | Cluster, Database | Database | Create temporary tables |
| LOCK TABLES | Cluster, Database | Cluster, Database | Lock tables (read and write) |
| REPLICATION | Cluster, Database | Cluster, Database | Read data for replication |
| CREATE VIEW | Cluster, Database | Database | Create views |
| ALTER VIEW | Cluster, Database, View | Database, View | Alter views |
| DROP VIEW | Cluster, Database, Table | Database, Table | Drop views |
| SHOW VIEW | Cluster, Database, View | Database, View | Show VIEW definitions |
| CLUSTER | Cluster | Cluster | Cluster administration, including replication, partition movement, and topology. Includes all permissions in SHOW METADATA. |
| BACKUP | Cluster, Database | Cluster. Database | Take backups and snapshots |
| CREATE USER | Cluster | Cluster | Create and drop users (no grants) |
| GRANT | Cluster, Database | Cluster. Database | Grant / revoke permissions, manage roles & groups. Distinct from WITH GRANT OPTION. |
| DROP DATABASE | Cluster, Database | Cluster. Database | Drop databases |
| CREATE DATABASE | Cluster, Database | Cluster. Database | Create databases |
| CREATE ROUTINE | Cluster, Database, Function | Database, Function | Create extensibility functions or procedures |
| ALTER ROUTINE | Cluster, Database, Function | Database, Function | Replace or delete extensibility functions or procedures |
| EXECUTE | Cluster, Database, Function | Database, Function | Execute extensibility functions or procedures |
| CREATE PIPELINE | Cluster, Database, Table | Database, Table | Create pipelines |
| DROP PIPELINE | Cluster, Database, Table | Database, Table | Drop pipelines |
| START PIPELINE | Cluster, Database, Table | Database, Table | Start pipelines |
| ALTER PIPELINE | Cluster, Database, Table | Database, Table | Alter pipelines |
| SHOW PIPELINE | Cluster, Database, Table | Database, Table | Show pipelines |