MariaDB用户管理创建-修改-权限-外网访问

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

外网访问一定别忘记开启防火墙3306端口

创建用户

CREATE USER a;

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

INSERT INTO user(User, Host, Password) VALUES('foo', '%', Password('hello'));

SELECT User, Host, Password FROM user WHERE User = 'foo';

+------+------+-------------------------------------------+
| User | Host | Password |
+------+------+-------------------------------------------+
| foo | % | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |
+------+------+-------------------------------------------+

创建备份用户

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT,SHOW VIEW,LOCK TABLES,RELOAD,REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

FLUSH PRIVILEGES;

重命名用户

RENAME USER a TO A;

或者

UPDATE user SET User = 'FOO' WHERE User = 'A';

SELECT User, Host, Password FROM user WHERE User = 'FOO';

+------+------+-------------------------------------------+
| User | Host | Password |
+------+------+-------------------------------------------+
| FOO | % | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |
+------+------+-------------------------------------------+

查看用户权限

SHOW GRANTS FOR FOO;

+-------------------------------+
| Grants for FOO@% |
+-------------------------------+
| GRANT USAGE ON . TO 'FOO'@'%' |
+-------------------------------+

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

GRANT SELECT ON crashcourse.* TO FOO;

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

GRANT ALL PRIVILEGES ON *.* TO FOO IDENTIFIED BY 'password' WITH GRANT OPTION;
SHOW GRANTS FOR A;

REVOKE SELECT ON crashcourse.* FROM FOO;

为用户设置密码

SET PASSWORD FOR FOO = Password('hello');

SET PASSWORD = Password('hello'); #不接用户时则是修改登录用户的密码
或者
UPDATE user SET Password = Password('test') WHERE User = 'FOO';

删除用户

DROP USER FOO;

或者
DELETE FROM user WHERE User = 'FOO';

最后编辑于：2018.12.20 22:41:12