(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
+------+------+-------------------------------------------+
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
(jlive)[mysql]>SELECT User, Host, Password FROM user WHERE User = 'FOO';
+------+------+-------------------------------------------+
| User | Host | Password
+------+------+-------------------------------------------+
| FOO
+------+------+-------------------------------------------+
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
删除用户
(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)