今天看了帐号相关的内容，生产库中经常会有申请查询帐号的情况，这里演示开通查询帐号，非常简单。

环境信息

root@localhost:francs>select database();  
+------------+  
| database()  |  
+------------+  
| francs |  
+------------+  
1 row in  set  (0.00 sec)  
  
root@localhost:francs>show tables;  
+------------------+  
|  Tables_in_francs  |  
+------------------+  
| test_1 |  
| test_2 |  
| test_3 |  
+------------------+  
3 rows in  set  (0.00 sec)

备注： francs 库中有三张表，现需要开通一查询帐号，仅具有对 francs 库表 test_1 的查询权限。

创建帐号

1 2	root@localhost:(none)>grant select on francs.test_1 to 'francs_select'@'192.168.2.%' identified by '123456'; Query OK, 0 rows affected (0.11 sec)

备注：创建帐号和赋权可以一条命令完成，在 MySQL 中定义用户和其它数据库中不同，格式为 ‘user_name‘@’host_name’， user_name 为用户名， host_name 为客户端 IP，也可以先创建帐号，再赋权。

客户端验证

客户端 IP 192.168.2.1

francs@francs:~$ mysql -ufrancs_select -D francs -p  
Enter password:    
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with  -A  
  
Welcome to the MySQL monitor. Commands  end  with  ;  or g.  
Your  MySQL connection id is  18  
Server version:  5.6.20-log Source distribution  
  
Copyright  (c)  2000,  2014,  Oracle  and/or its affiliates.  All rights reserved.  
  
Oracle  is a registered trademark of Oracle  Corporation  and/or its  
affiliates.  Other names may be trademarks of their respective  
owners.  
  
Type  'help;'  or  'h'  for help.  Type  'c' to clear the current input statement.  
  
mysql>  select  *  from test_1 limit 1;  
+----+------+  
| id | name |  
+----+------+  
| 0  |  0_a |  
+----+------+  
1 row in  set  (0.00 sec)  
  
mysql>  select  *  from test_2 limit 1;  
ERROR 1142  (42000): SELECT command denied to user 'francs_select'@'192.168.2.1'  for table 'test_2'

备注：francs_select 用户具有对表 test_1 的查询权限，但没有权限查询表 test_2.

查看权限

root@localhost:mysql>select  *  From tables_priv where  User='francs_select';  
+-------------+--------+---------------+------------+----------------+---------------------+------------+-------------+  
|  Host |  Db |  User |  Table_name  |  Grantor |  Timestamp |  Table_priv  |  Column_priv  |  
+-------------+--------+---------------+------------+----------------+---------------------+------------+-------------+  
|  192.168.2.%  | francs | francs_select | test_1 | root@localhost |  0000-00-00  00:00:00  |  Select | |  
+-------------+--------+---------------+------------+----------------+---------------------+------------+-------------+  
1 row in  set  (0.00 sec)

参考

原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/tech/database/239600.html

MySQL：创建只读帐号

环境信息

创建帐号

客户端验证

查看权限

参考

发表回复

MySQL：创建只读帐号

环境信息

创建帐号

客户端验证

查看权限

参考

相关推荐

发表回复