以mariadb5.5版本为例
新建用户
登录mariadb
# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
//创建用户
MariaDB [(none)]> insert into mysql.user(Host,User,Password) values("localhost","frank",password("frank"));
Query OK, 1 row affected, 4 warnings (0.00 sec)
//刷新系统权限表
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
这样就创建了一个名为：frank 密码为：frank 的用户。
然后登录一下。
MariaDB [(none)]>exit;
# mysql -ufrank -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
为用户授权
首先以root登录mariadb,创建一个数据库phpdb
MariaDB [(none)]> create database phpdb;
Query OK, 1 row affected (0.00 sec)
授权frank用户拥有phpdb库的所有权限
MariaDB [(none)]> grant all privileges on phpdb.* to frank@localhost identified by 'frank';
Query OK, 0 rows affected (0.00 sec)
刷新系统权限表
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
如果想指定部分权限给一用户，可以这样来写:
MariaDB [(none)]>grant select,update on phpdb.* to frank@localhost identified by 'frank';
删除用户
MariaDB [(none)]> delete from mysql.user where user="frank" and host="localhost";
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> select user from mysql.user;
+------+
| user |
+------+
| root |
| root |
| |
| root |
| |
| root |
+------+
修改指定用户密码
MariaDB [(none)]>update mysql.user set password=password('frank') where User="root" and Host="localhost";
MariaDB [(none)]>flush privileges;