mysql账户最小授权_mysql 创建帐号并授权

数据库安装请参考：http://youngbrick.iteye.com/blog/2335851

创建数据库：

MariaDB [mysql]> create database mydbtest default charset utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

查询数据库：

MariaDB [mysql]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| mydbtest |

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

4 rows in set (0.00 sec)

创建mysql登录用户：

用户：brick

密码：brick!123

localhost：只允许本机访问

%：允许远程访问

MariaDB [mysql]> insert into mysql.user(Host,User,Password) values('localhost','brick','brick!123');

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

mysql 5.7没有password 字段了，通过create 创建帐号

CREATE USER'username'@'localhost' IDENTIFIED BY '123456';

查看创建的帐号：

MariaDB [(none)]> select user,host,password from mysql.user;

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

| user | host | password |

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

| root | localhost | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| root | 127.0.0.1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| root | ::1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| brick | localhost | brick!123 |

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

4 rows in set (0.00 sec)

发现密码错了，密码需要password(‘密码’)加密，修改一下：

MariaDB [(none)]> update mysql.user set password=password('brick!123') where user='brick';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

确认一下，再看一眼：

MariaDB [(none)]> select user,host,password from mysql.user;

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

| user | host | password |

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

| root | localhost | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| root | 127.0.0.1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| root | ::1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |

| brick | localhost | *CEDA60EA702EFCE18A1B31CE4EB823FF363B8085 |

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

4 rows in set (0.00 sec)

授权,mydbtest所有权限授予brick帐号：

刚上面修改了mysql.user表，flush privileges一下：

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

如果不执行上面的命令，授权时会出现这个错误：

MariaDB [mysql]> grant all privileges on mydbtest.* to brick@localhost;

ERROR 1133 (42000): Can't find any matching row in the user table

flush privileges执行完后，再执行grant授权。(mydbtest.*)mydbtest数据库的所有权限授予brick

MariaDB [(none)]> grant all privileges on mydbtest.* to brick@localhost;

Query OK, 0 rows affected (0.00 sec)

授权权限后也需要flush privileges;否则帐号没有权限登录，权限没有持久化到数据库中。

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

退出mysql：

MariaDB [mysql]> exit

Bye

重新登录mysql：

[root@localhost ~]# mysql -ubrick -p

Enter password: (输入密码)

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 5.5.50-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)]> show databases;

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

| Database |

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

| information_schema |

| mydbtest |

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

2 rows in set (0.00 sec)

MariaDB [(none)]>

查看当前用户的权限：

MariaDB [(none)]> show grants;

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

| Grants for brick@localhost |

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

| GRANT USAGE ON *.* TO 'brick'@'localhost' IDENTIFIED BY PASSWORD '*CEDA60EA702EFCE18A1B31CE4EB823FF363B8085' |

| GRANT ALL PRIVILEGES ON `mydbtest`.* TO 'brick'@'localhost' |

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

2 rows in set (0.00 sec)

=======================命令备注==========================

授权grant xxxx to xxx：

MariaDB [(none)]> grant all privileges on mydbtest.* to brick@localhost;

删除权限revoke   xxx  from xxx：跟授权很像，grant换成revoke,to换成from

MariaDB [(none)]> revoke all privileges on mydbtest.* from brick@localhost;

查看用户权限：show grants for xxxx@xxxx

MariaDB [(none)]> SHOW GRANTS FOR 'brick'@'localhost';

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

| Grants for brick@localhost |

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

| GRANT USAGE ON *.* TO 'brick'@'localhost' IDENTIFIED BY PASSWORD '*CEDA60EA702EFCE18A1B31CE4EB823FF363B8085' |

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

1 row in set (0.00 sec)

更详细的授权命令写法：

https://www.techonthenet.com/mariadb/grant_revoke.php

数据导入：

将xx.sql文件放到/usr/local目录

登录mysql进入命令行

msyqlxx>use mydb;

mysqxx>source /usr/local/xx.sql;