如何给mariadb数据创建用户并且授权

发布网友发布时间：2022-04-20 12:07

共1个回答

热心网友时间：2022-04-07 19:01

说明：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)