MariaDB 用户与授权

MariaDB 用户与授权

说明：MariaDB的默认用户是存放在mysql库的user表中，当然也可以通过对mysql.user表的增删改查来新增用户，删除用户，修改密码和权限

创建用户

(jlive)[crashcourse]>CREATE USER a;

Query OK, 0 rows affected (0.00 sec)

MariaDB的用户是由'用户名'@'主机'组成， 如果是简写的用户则主机默认为%,即可来源于所以的主机

或者

(jlive)[mysql]>INSERT INTO user(User, Host, Password) VALUES('foo', '%', Password('hello'));

Query OK, 1 row affected, 4 warnings (0.00 sec)

(jlive)[mysql]>SELECT User, Host, Password FROM user WHERE User = 'foo';

+------+------+-------------------------------------------+

| User | Host | Password                                  |

+------+------+-------------------------------------------+

| foo  | %    | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |

+------+------+-------------------------------------------+

 

1 row in set (0.00 sec)

创建备份用户

(jlive)[crashcourse]>CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>GRANT SELECT,SHOW VIEW,LOCK TABLES,RELOAD,REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

Query OK, 0 rows affected (0.15 sec)

(jlive)[crashcourse]>FLUSH PRIVILEGES;

 

Query OK, 0 rows affected (0.00 sec)

重命名用户

(jlive)[crashcourse]>RENAME USER a TO A;

Query OK, 0 rows affected (0.00 sec)

或者

(jlive)[mysql]>UPDATE user SET User = 'FOO' WHERE User = 'foo';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

(jlive)[mysql]>SELECT User, Host, Password FROM user WHERE User = 'FOO';

+------+------+-------------------------------------------+

| User | Host | Password                                  |

+------+------+-------------------------------------------+

| FOO  | %    | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |

+------+------+-------------------------------------------+

 

1 row in set (0.00 sec)

查看用户权限

(jlive)[crashcourse]>SHOW GRANTS FOR A;

+-------------------------------+

| Grants for A@%                |

+-------------------------------+

| GRANT USAGE ON *.* TO 'A'@'%' |

+-------------------------------+

1 row in set (0.00 sec)

单一的SHOW GRANTS不接用户时可以查看登录用户本身的权限

修改用户权限

(jlive)[crashcourse]>GRANT SELECT ON crashcourse.* TO A;

Query OK, 0 rows affected (0.00 sec)

特殊用户可以赋予最大权限，还可顺便修改密码

GRANT ALL PRIVILEGES ON *.* TO foo IDENTIFIED BY 'password' WITH GRANT OPTION；

(jlive)[crashcourse]>SHOW GRANTS FOR A;

+--------------------------------------------+

| Grants for A@%                             |

+--------------------------------------------+

| GRANT USAGE ON *.* TO 'A'@'%'              |

| GRANT SELECT ON `crashcourse`.* TO 'A'@'%' |

+--------------------------------------------+

2 rows in set (0.01 sec)

解除用户权限

(jlive)[crashcourse]>REVOKE SELECT ON crashcourse.* FROM A;

Query OK, 0 rows affected (0.00 sec)

为用户设置密码

(jlive)[crashcourse]>SET PASSWORD FOR A = Password('hello');

Query OK, 0 rows affected (0.00 sec)

SET PASSWORD = Password('hello'); #不接用户时则是修改登录用户的密码

或者

(jlive)[mysql]>UPDATE user SET Password = Password('test') WHERE User = 'FOO';

Query OK, 1 row affected (0.00 sec)

 

Rows matched: 1  Changed: 1  Warnings: 0

删除用户

(jlive)[crashcourse]>DROP USER A;

 

Query OK, 0 rows affected (0.00 sec)

或者

(jlive)[mysql]>DELETE FROM user WHERE User = 'FOO';

 

Query OK, 1 row affected (0.00 sec)