百度搜:
连接:http://blog.csdn.net/zmx729618/article/details/78026497
Host列指定了允许用户登录所使用的IP,比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。
而%是个通配符,如果Host=192.168.1.%,那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%,表示所有IP都有连接权限。、
这也就是为什么在开启远程连接的时候,大部分人都直接把Host改成%的缘故,为了省事。
1:新增用户:
注:MySQL数据库下user表中,Host和User为两个主键列(primary key),已经各版本下非空未设置默认字段。
登录后,切换db:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
新增用户:
注:限制kaka用户的登陆ip为10.155.123.55,ip为随手写入,如果正确配置为您有效登陆ip,所有ip登陆,则设置Host为 '%'
mysql> INSERT INTO mysql.user(Host,User,Password) VALUES("10.155.123.55","kaka",PASSWORD("kaka123"));
在版本 5.6.27:
mysql> INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES("10.155.123.55","kaka",PASSWORD("kaka123"),"","","");
Query OK, 1 row affected (0.03 sec)
新增用户(全sql):
INSERT INTO `user`(`Host`,`User`,`Password`,`Select_priv`,`Insert_priv`,`Update_priv`,`Delete_priv`,`Create_priv`,`Drop_priv`,`Reload_priv`,`Shutdown_priv`,`Process_priv`,`File_priv`,`Grant_priv`,`References_priv`,`Index_priv`,`Alter_priv`,`Show_db_priv`,`Super_priv`,`Create_tmp_table_priv`,`Lock_tables_priv`,`Execute_priv`,`Repl_slave_priv`,`Repl_client_priv`,`Create_view_priv`,`Show_view_priv`,`Create_routine_priv`,`Alter_routine_priv`,`Create_user_priv`,`Event_priv`,`Trigger_priv`,`Create_tablespace_priv`,`ssl_type`,`ssl_cipher`,`x509_issuer`,`x509_subject`,`max_questions`,`max_updates`,`max_connections`,`max_user_connections`,`plugin`,`authentication_string`,`password_expired`) VALUES ('%','root','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N');
新增用户完成,刷新mysql的系统权限相关表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
重启生效:
[root@Tony_ts_tian bin]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
查询用户,Host,User,Password:
mysql> SELECT Host,User,Password FROM mysql.user;
+----------------+------+-------------------------------------------+
| Host | User | Password |
+----------------+------+-------------------------------------------+
| localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| 10.155.123.55 | kaka | *90B3D884FB6092549F244125549B77C000A0F9C6 |
| % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
+----------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
2:修改信息,密码,类似可修改其他字段。
mysql> UPDATE `user` SET Password=PASSWORD("123456") WHERE Host='10.155.123.55' AND User='kaka';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Password FROM `user`;
前:
| 10.155.123.55 | kaka | *90B3D884FB6092549F244125549B77C000A0F9C6 |
后:
| 10.155.123.55 | kaka | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
3:删除用户:
mysql> DELETE FROM `user` WHERE Host='10.155.123.55' AND User='kaka';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Password FROM `user`;
+----------------+------+-------------------------------------------+
| Host | User | Password |
+----------------+------+-------------------------------------------+
| localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
| % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |
+----------------+------+-------------------------------------------+
5 rows in set (0.00 sec)
4. 权限分配
GRANT语法:
GRANT 权限 ON 数据库.* TO 用户名@'登录主机' IDENTIFIED BY '密码'
权限:
ALL,ALTER,CREATE,DROP,SELECT,UPDATE,DELETE
新增用户:权限为USAGE,即为:"无权限",想要创建一个没有权限的用户时,可以指定USAGE
数据库:
*.* 表示所有库的所有表
mylove.* 表示mylove库的所有表
mylove.loves 表示mylove库的loves表
用户名:
MySQL的账户名
登陆主机:
允许登陆到MySQL Server的客户端ip
'%'表示所有ip
'localhost' 表示本机
'10.155.123.55' 特定IP
密码:
MySQL的账户名对应的登陆密码
注: IDENTIFIED BY '密码',可选。
GRANT会覆盖用户的部分信息,跟insert 、update执行功能一样。
给用户kaka分配test数据库下user表的查询select权限:
mysql> GRANT SELECT ON test.user TO kaka@'10.155.123.55' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show Grants for 'kaka'@'10.155.123.55';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for kaka@10.155.123.55 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kaka'@'10.155.123.55' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `test`.`user` TO 'kaka'@'10.155.123.55' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
为了快速测试,我要把ip切回%,ip全访问:
使用和测试: