How To Create User Account With Privileges Second Only to The 'root' Account in RDS MariaDB?
Tags: beginners, rds, mariadb, alibabacloud
Published: 2022-05-07 10:37:51

## Account Privileges: 
①Privileged Account > ②Standard Account(DML+DDL) > ③Standard Account(DDL) > ④Standard Account(DML) > ⑤Standard Account(Read-Only)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eq81y25q0rrduenzw06q.png)
 

## ① How to create privileged account?
Create on the console.

Or you can create via openAPI, we will discuss in another article.

## ② How to create standard account with DDL+DML privileges?
See example as below:
The given example will create a user named 'normal3' with DDL and DML privileges on DB 'test3' and it is allowed to access from anywhere. 

```sql
create user IF NOT EXISTS normal3@'%' identified by 'Passw@rd!';
show grants for normal3;
_// The user will only have USAGE privilege after created. So you would need to grant process, replication slave and replication client on *.* to new user account._
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'normal3'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test3`.* TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`help_keyword` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`help_category` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`help_relation` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`slow_log` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`func` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`general_log` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`proc` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`time_zone` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'normal3'@'%';
GRANT SELECT ON `mysql`.`event` TO 'normal3'@'%';
```

## ③ How to create standard account with DDL privileges?
```sql
create user IF NOT EXISTS normal6@'%' identified by 'Passw@rd!';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'normal6'@'%';
GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `sampledb`.* TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`help_keyword` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`help_category` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`help_relation` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`slow_log` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`func` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`general_log` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`proc` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`time_zone` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'normal6'@'%';
GRANT SELECT ON `mysql`.`event` TO 'normal6'@'%';
```

## ④ How to create standard account with DML privileges?
```sql
create user IF NOT EXISTS normal5@'%' identified by 'Passw@rd!';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'normal5'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON `sampledb`.* TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`help_keyword` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`help_category` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`help_relation` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`slow_log` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`func` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`general_log` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`proc` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`time_zone` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'normal5'@'%';
GRANT SELECT ON `mysql`.`event` TO 'normal5'@'%';
```

## ⑤ How to create standard account with Read-Only privileges?
```sql
create user IF NOT EXISTS normal4@'%' identified by 'Passw@rd!';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'normal4'@'%';
GRANT SELECT, LOCK TABLES, SHOW VIEW ON `test4`.* TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`help_keyword` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`help_category` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`help_relation` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`slow_log` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`func` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`general_log` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`proc` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`time_zone` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'normal4'@'%';
GRANT SELECT ON `mysql`.`event` TO 'normal4'@'%';
```