Mysql之权限管理
mysql命令
一、初试命令
show databases; #查看当前Mysql都有那些数据，根目录都有那些文件夹
create database 数据库名; #创建文件夹
use 数据库名; #使用选中数据库，进入目录
show tables; #查看当前数据库下都有那些表，
create table 表名(nid int,name varchar(20), pwd varchar(64)); #创建数据库表
select * from 表名; #查看表中的所有数据
insert into 表名(nid,name,pwd) values(1,'alex','123'); #插入数据
select * from 表名; #查看表中数据
drop database 库名; #删除数据库
示例：
1 [root@mysql ~]# mysql -uroot -p #再输入密码 2 Welcome to the MySQL monitor. Commands end with ; or \g. 3 Your MySQL connection id is 4 4 Server version: 5.5.49 MySQL Community Server (GPL) 5 6 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 7 8 Oracle is a registered trademark of Oracle Corporation and/or its 9 affiliates. Other names may be trademarks of their respective 10 owners. 11 12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 13 14 15 #创建数据库 16 mysql> create database test1; 17 Query OK, 1 row affected (0.02 sec) 18 19 20 #进入test1库中 21 mysql> use test1; 22 Database changed 23 24 #创建表tb1（nid和name字段） 25 mysql> create table tb1(nid int,name varchar(20)); 26 Query OK, 0 rows affected (0.06 sec) 27 28 #创建tb2 （nid和name字段） 29 mysql> create table tb2(nid int,name varchar(20)); 30 Query OK, 0 rows affected (0.01 sec) 31 32 #查看表 33 mysql> show tables; 34 +-----------------+ 35 | Tables_in_test1 | 36 +-----------------+ 37 | tb1 | 38 | tb2 | 39 +-----------------+ 40 2 rows in set (0.01 sec) 41 42 #查看tb1表中数据 43 mysql> select * from tb1; 44 Empty set (0.01 sec) #没有数据 45 46 #往表中插入数据 47 mysql> insert into tb1(nid,name)values(1,'alex'); 48 Query OK, 1 row affected (0.01 sec) 49 50 #往表中插入数据 51 mysql> insert into tb1(nid,name)values(1,'eric'); 52 Query OK, 1 row affected (0.01 sec) 53 54 #查看tb1表中数据 55 mysql> select * from tb1; 56 +------+------+ 57 | nid | name | 58 +------+------+ 59 | 1 | alex | 60 | 1 | eric | 61 +------+------+ 62 2 rows in set (0.00 sec) 63 64 #退出 65 mysql> exit 66 Bye
#查看mysql库中user表中的字段
1 #查看数据库
2 mysql> show databases;
3 +--------------------+
4 | Database |
5 +--------------------+
6 | information_schema |
7 | mysql |
8 | performance_schema |
9 | test1 |
10 +--------------------+
11 4 rows in set (0.00 sec)
12
13
14 #进入mysql库
15 mysql> use mysql
16 Database changed
17
18 #查看表
19 mysql> show tables;
20 +---------------------------+
21 | Tables_in_mysql |
22 +---------------------------+
23 | columns_priv |
24 | db |
25 | event |
26 | func |
27 | general_log |
28 | help_category |
29 | help_keyword |
30 | help_relation |
31 | help_topic |
32 | host |
33 | ndb_binlog_index |
34 | plugin |
35 | proc |
36 | procs_priv |
37 | proxies_priv |
38 | servers |
39 | slow_log |
40 | tables_priv |
41 | time_zone |
42 | time_zone_leap_second |
43 | time_zone_name |
44 | time_zone_transition |
45 | time_zone_transition_type |
46 | user |
47 +---------------------------+
48 24 rows in set (0.00 sec)
49
50
51 #查看user表（横状显示）
52 mysql> select * from user;
53 +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
54 | 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 |
55
56 省略部分......
57
58
59 #坚状显示user表
60 mysql> desc user;
61 +------------------------+-----------------------------------+------+-----+---------+-------+
62 | Field | Type | Null | Key | Default | Extra |
63 +------------------------+-----------------------------------+------+-----+---------+-------+
64 | Host | char(60) | NO | PRI | | |
65 | User | char(16) | NO | PRI | | |
66 | Password | char(41) | NO | | | |
67 | Select_priv | enum('N','Y') | NO | | N | |
68 | Insert_priv | enum('N','Y') | NO | | N | |
69 | Update_priv | enum('N','Y') | NO | | N | |
70 | Delete_priv | enum('N','Y') | NO | | N | |
71 | Create_priv | enum('N','Y') | NO | | N | |
72 | Drop_priv | enum('N','Y') | NO | | N | |
73 | Reload_priv | enum('N','Y') | NO | | N | |
74 | Shutdown_priv | enum('N','Y') | NO | | N | |
75 | Process_priv | enum('N','Y') | NO | | N | |
76 | File_priv | enum('N','Y') | NO | | N | |
77 | Grant_priv | enum('N','Y') | NO | | N | |
78 | References_priv | enum('N','Y') | NO | | N | |
79 | Index_priv | enum('N','Y') | NO | | N | |
80 | Alter_priv | enum('N','Y') | NO | | N | |
81 | Show_db_priv | enum('N','Y') | NO | | N | |
82 | Super_priv | enum('N','Y') | NO | | N | |
83 | Create_tmp_table_priv | enum('N','Y') | NO | | N | |
84 | Lock_tables_priv | enum('N','Y') | NO | | N | |
85 | Execute_priv | enum('N','Y') | NO | | N | |
86 | Repl_slave_priv | enum('N','Y') | NO | | N | |
87 | Repl_client_priv | enum('N','Y') | NO | | N | |
88 | Create_view_priv | enum('N','Y') | NO | | N | |
89 | Show_view_priv | enum('N','Y') | NO | | N | |
90 | Create_routine_priv | enum('N','Y') | NO | | N | |
91 | Alter_routine_priv | enum('N','Y') | NO | | N | |
92 | Create_user_priv | enum('N','Y') | NO | | N | |
93 | Event_priv | enum('N','Y') | NO | | N | |
94 | Trigger_priv | enum('N','Y') | NO | | N | |
95 | Create_tablespace_priv | enum('N','Y') | NO | | N | |
96 | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
97 | ssl_cipher | blob | NO | | NULL | |
98 | x509_issuer | blob | NO | | NULL | |
99 | x509_subject | blob | NO | | NULL | |
100 | max_questions | int(11) unsigned | NO | | 0 | |
101 | max_updates | int(11) unsigned | NO | | 0 | |
102 | max_connections | int(11) unsigned | NO | | 0 | |
103 | max_user_connections | int(11) unsigned | NO | | 0 | |
104 | plugin | char(64) | YES | | | |
105 | authentication_string | text | YES | | NULL | |
106 +------------------------+-----------------------------------+------+-----+---------+-------+
107 42 rows in set (0.01 sec)
108
109
110 #查看user表中，host和user字段中的内容
111 mysql> select host,user from user;
112 +-----------+------+
113 | host | user |
114 +-----------+------+
115 | 127.0.0.1 | root |
116 | localhost | root |
117 +-----------+------+
118 2 rows in set (0.00 sec)
二、用户授权
用户管理特殊命令：
创建用户
create user '用户名'@'IP地址' identified by '密码';
1 #授权nulige用户本地登录mysql,密码是：oldboy123 2 mysql> create user nulige@localhost identified by 'oldboy123'; 3 Query OK, 0 rows affected (0.01 sec) 4 5 #查看user表中，host和user字段的内容 6 mysql> select host,user from user; 7 +-----------+--------+ 8 | host | user | 9 +-----------+--------+ 10 | 127.0.0.1 | root | 11 | localhost | nulige | 12 | localhost | root | 13 +-----------+--------+ 14 3 rows in set (0.00 sec) 15 16 mysql> exit; 17 Bye 18 19 #用刚创建的用户登录 20 [root@mysql ~]# mysql -unulige -p 21 Enter password: 22 Welcome to the MySQL monitor. Commands end with ; or \g. 23 Your MySQL connection id is 7 24 Server version: 5.5.49 MySQL Community Server (GPL) 25 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 26 Oracle is a registered trademark of Oracle Corporation and/or its 27 affiliates. Other names may be trademarks of their respective 28 owners. 29 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 30 mysql>
删除用户
drop user '用户名'@'IP地址';
1 mysql> create user alex@localhost identified by 'oldboy123'; 2 Query OK, 0 rows affected (0.00 sec) 3 #查看user表中用户（这里指可以登录mysql的用户） 4 mysql> select host,user from user; 5 +-----------+--------+ 6 | host | user | 7 +-----------+--------+ 8 | 127.0.0.1 | root | 9 | localhost | alex | 10 | localhost | nulige | 11 | localhost | root | 12 +-----------+--------+ 13 4 rows in set (0.00 sec) 14 #删除alex用户 15 mysql> drop user alex@localhost; 16 Query OK, 0 rows affected (0.00 sec) 17 18 #可能因大写或特殊linux主机名导致的。用drop user ""@"MYSQL"；删除不了，报错。 19 解决办法： 20 delete from mysql.user where user='' and host='MYSQL'; 21 flush privileges; 22 23 24 mysql> select host,user from user; 25 +-----------+--------+ 26 | host | user | 27 +-----------+--------+ 28 | 127.0.0.1 | root | 29 | localhost | nulige | 30 | localhost | root | 31 +-----------+--------+ 32 3 rows in set (0.00 sec)
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
1 #创建连接mysql数据库用户 2 3 mysql> create user 'alex'@'localhost' identified by 'oldboy123'; 4 Query OK, 0 rows affected (0.00 sec) 5 6 #查看user表中字段 7 mysql> select host,user from user; 8 +-----------+--------+ 9 | host | user | 10 +-----------+--------+ 11 | 127.0.0.1 | root | 12 | localhost | alex | 13 | localhost | nulige | 14 | localhost | root | 15 +-----------+--------+ 16 4 rows in set (0.00 sec) 17 18 #修改用户和host地址 19 mysql> rename user 'alex'@'localhost' to 'eric'@'127.0.0.1'; 20 Query OK, 0 rows affected (0.00 sec) 21 22 #查看user表中字段 23 mysql> select host,user from user; 24 +-----------+--------+ 25 | host | user | 26 +-----------+--------+ 27 | 127.0.0.1 | eric | #把alex修改成eric 28 | 127.0.0.1 | root | 29 | localhost | nulige | 30 | localhost | root | 31 +-----------+--------+ 32 4 rows in set (0.00 sec)
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
1 mysql> select host,user from user;
2 +-----------+--------+
3 | host | user |
4 +-----------+--------+
5 | 127.0.0.1 | eric | #修改eric用户和host主机地址
6 | 127.0.0.1 | root |
7 | localhost | nulige |
8 | localhost | root |
9 +-----------+--------+
10 4 rows in set (0.00 sec)
11
12 #修改密码; 这里有个坑，如果eric@localhost,后面登录的时候，可以不加-h，否则必须加，要不然会报错。
13 mysql> set password for 'eric'@'127.0.0.1' = password('123456');
14 Query OK, 0 rows affected (0.00 sec)
15
16 #退出
17 mysql> exit;
18 Bye
19 #用eric用户登录数据库
20 [root@mysql ~]# mysql -ueric -h 127.0.0.1 -p
21 Enter password: #输入密码：123456
22 Welcome to the MySQL monitor. Commands end with ; or \g.
23 Your MySQL connection id is 15
24 Server version: 5.5.49 MySQL Community Server (GPL)
25
26 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
27
28 Oracle is a registered trademark of Oracle Corporation and/or its
29 affiliates. Other names may be trademarks of their respective
30 owners.
31
32 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
33
34 mysql>
远程客户端连接
1 mysql -u root -h 192.168.1.1 -p #远程连接mysql数据库，加-h参数，后面接IP地址 2 回车后，再输入密码。
三、权限管理
权限：
默认，什么都没有
1 show grants for '用户'@'IP地址' ---> 查看权限 2 grant 权限 on 数据库.表 to '用户'@'IP地址' ---> 授权 3 revoke 权限 on 数据库.表 from '用户'@'IP地址' ---> 取消权限
对于权限有如下类型：
#标红部分为常用权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
对于数据库
1 用户名@IP地址 用户只能在改IP下才能访问 2 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 3 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
特殊的：
|
1
|
flush privileges，将数据读取到内存中，从而实现不重启即可生效。 |
示例：
1 grant all privileges on db1.tb1 TO '用户名'@'IP' 2 3 grant select on db1.* TO '用户名'@'IP' 4 5 grant select,insert on *.* TO '用户名'@'IP' 6 7 revoke select on db1.tb1 from '用户名'@'IP'
示例1：授权nulige用户select权限（只能查看），查看test1库中，tb1表
#用root用户登录 [root@mysql ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.5.49 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test1 | +--------------------+ 4 rows in set (0.00 sec) #进入test1库 mysql> use test1; Database changed #查看表 mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | tb1 | | tb2 | +-----------------+ 2 rows in set (0.00 sec) #授权nulige用户select权限（只能查看），查看test1库中，tb1表 mysql> grant select on test1.tb1 to 'nulige'@'localhost'; #授权用grant Query OK, 0 rows affected (0.00 sec) ############################## #再开一个窗口，用CRT登录。 #切换nulige用户 [root@mysql ~]# mysql -unulige -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.5.49 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test1 | #发现授权的test1库 +--------------------+ 2 rows in set (0.00 sec) #进入test1库 mysql> use test1 Database changed #查看表 mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | tb1 | +-----------------+ 1 row in set (0.00 sec) #查看tb1表中内容 mysql> select * from tb1; +------+------+ | nid | name | +------+------+ | 1 | alex | | 1 | eric | +------+------+ 2 rows in set (0.00 sec)
示例2：授权nulige用户select和insert权限
1 #登录root用户 2 3 [root@mysql ~]# mysql -uroot -p 4 Enter password: 5 Welcome to the MySQL monitor. Commands end with ; or \g. 6 Your MySQL connection id is 16 7 Server version: 5.5.49 MySQL Community Server (GPL) 8 9 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 10 11 Oracle is a registered trademark of Oracle Corporation and/or its 12 affiliates. Other names may be trademarks of their respective 13 owners. 14 15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 16 17 18 mysql> show databases; 19 +--------------------+ 20 | Database | 21 +--------------------+ 22 | information_schema | 23 | mysql | 24 | performance_schema | 25 | test1 | 26 +--------------------+ 27 4 rows in set (0.00 sec) 28 29 30 mysql> use test1; 31 Database changed 32 33 34 mysql> show tables; 35 +-----------------+ 36 | Tables_in_test1 | 37 +-----------------+ 38 | tb1 | 39 | tb2 | 40 +-----------------+ 41 2 rows in set (0.00 sec) 42 43 mysql> grant select,insert on test1.tb1 to 'nulige'@'localhost'; 44 Query OK, 0 rows affected (0.00 sec) 45 46 47 #登录nulige用户 48 [root@mysql ~]# mysql -unulige -p 49 Enter password: 50 Welcome to the MySQL monitor. Commands end with ; or \g. 51 Your MySQL connection id is 18 52 Server version: 5.5.49 MySQL Community Server (GPL) 53 54 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 55 56 Oracle is a registered trademark of Oracle Corporation and/or its 57 affiliates. Other names may be trademarks of their respective 58 owners. 59 60 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 61 62 mysql> show databases; 63 +--------------------+ 64 | Database | 65 +--------------------+ 66 | information_schema | 67 | test1 | 68 +--------------------+ 69 2 rows in set (0.00 sec) 70 71 72 mysql> use test1 73 Database changed 74 75 76 mysql> show tables; 77 +-----------------+ 78 | Tables_in_test1 | 79 +-----------------+ 80 | tb1 | 81 +-----------------+ 82 1 row in set (0.00 sec) 83 84 85 mysql> select * from tb1; 86 +------+------+ 87 | nid | name | 88 +------+------+ 89 | 1 | alex | 90 | 1 | eric | 91 +------+------+ 92 2 rows in set (0.00 sec) 93 94 95 mysql> insert into tb1(nid,name)values(1,'linyan'); 96 Query OK, 1 row affected (0.02 sec) 97 98 99 mysql> select * from tb1; 100 +------+--------+ 101 | nid | name | 102 +------+--------+ 103 | 1 | alex | 104 | 1 | eric | 105 | 1 | linyan | 106 +------+--------+ 107 3 rows in set (0.00 sec)
示例3：创建'nulige'@'192.168.1.%'并授权select,inster
1 [root@mysql ~]# mysql -uroot -p 2 Enter password: oldboy123 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 20 5 Server version: 5.5.49 MySQL Community Server (GPL) 6 7 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 8 9 Oracle is a registered trademark of Oracle Corporation and/or its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15 mysql> show databases; 16 +--------------------+ 17 | Database | 18 +--------------------+ 19 | information_schema | 20 | mysql | 21 | performance_schema | 22 | test1 | 23 +--------------------+ 24 4 rows in set (0.00 sec) 25 26 mysql> use mysql 27 Database changed 28 29 mysql> select user,host from user; 30 +--------+-----------+ 31 | user | host | 32 +--------+-----------+ 33 | eric | 127.0.0.1 | 34 | root | 127.0.0.1 | 35 | nulige | localhost | 36 | root | localhost | 37 +--------+-----------+ 38 4 rows in set (0.00 sec) 39 40 #创建数据库用户 41 mysql> create user "nulige"@"192.168.1.%" identified by "oldboy123"; 42 Query OK, 0 rows affected (0.00 sec) 43 44 #查看user表字段 45 mysql> select user,host from user; 46 +--------+-------------+ 47 | user | host | 48 +--------+-------------+ 49 | eric | 127.0.0.1 | 50 | root | 127.0.0.1 | 51 | nulige | 192.168.1.% | 52 | nulige | localhost | 53 | root | localhost | 54 +--------+-------------+ 55 6 rows in set (0.00 sec) 56 57 #给nulige授权select,insert 权限，可以访问test1数据库 58 mysql> grant select,insert on test1.tb1 to "nulige"@"192.168.1.%"; 59 Query OK, 0 rows affected (0.00 sec) 60 61 #查看用户权限 62 mysql> show grants for 'nulige'@'192.168.1.%'; 63 +-----------------------------------------------------------------------------------------------------------------+ 64 | Grants for nulige@192.168.1.% | 65 +-----------------------------------------------------------------------------------------------------------------+ 66 | GRANT USAGE ON *.* TO 'nulige'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | 67 | GRANT SELECT, INSERT ON `test1`.`tb1` TO 'nulige'@'192.168.1.%' | 68 +-----------------------------------------------------------------------------------------------------------------+ 69 2 rows in set (0.00 sec)
示例4：查看用户权限
1 #查看用户权限 2 3 mysql> show grants for 'nulige'@localhost; 4 +---------------------------------------------------------------------------------------------------------------+ 5 | Grants for nulige@localhost | 6 +---------------------------------------------------------------------------------------------------------------+ 7 | GRANT USAGE ON *.* TO 'nulige'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | 8 | GRANT SELECT, INSERT ON `test1`.`tb1` TO 'nulige'@'localhost' | 9 +---------------------------------------------------------------------------------------------------------------+ 10 2 rows in set (0.00 sec)
示例5：取消用户权限
1 #查看用户权限 2 mysql> show grants for 'nulige'@'192.168.1.%'; 3 +-----------------------------------------------------------------------------------------------------------------+ 4 | Grants for nulige@192.168.1.% | 5 +-----------------------------------------------------------------------------------------------------------------+ 6 | GRANT USAGE ON *.* TO 'nulige'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | 7 | GRANT SELECT, INSERT ON `test1`.`tb1` TO 'nulige'@'192.168.1.%' | 8 +-----------------------------------------------------------------------------------------------------------------+ 9 2 rows in set (0.00 sec) 10 11 12 #取消用户insert权限 13 mysql> revoke insert on test1.tb1 from 'nulige'@'192.168.1.%'; 14 Query OK, 0 rows affected (0.00 sec) 15 16 17 #查看用户权限 18 mysql> show grants for 'nulige'@'192.168.1.%'; 19 +-----------------------------------------------------------------------------------------------------------------+ 20 | Grants for nulige@192.168.1.% | 21 +-----------------------------------------------------------------------------------------------------------------+ 22 | GRANT USAGE ON *.* TO 'nulige'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | 23 | GRANT SELECT ON `test1`.`tb1` TO 'nulige'@'192.168.1.%' | 24 +-----------------------------------------------------------------------------------------------------------------+ 25 2 rows in set (0.00 sec)
示例6：删除数据库（删除test1库)
1 #查看数据库 2 mysql> show databases; 3 +--------------------+ 4 | Database | 5 +--------------------+ 6 | information_schema | 7 | mysql | 8 | performance_schema | 9 | test1 | 10 +--------------------+ 11 4 rows in set (0.00 sec) 12 13 #删除test1数据库 14 mysql> drop database test1; 15 Query OK, 2 rows affected (0.02 sec)
#授权all权限管理所有数据库
mysql> grant all on *.* to root@'192.168.30.%' identified by 'oldboy123'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +------+--------------+ | user | host | +------+--------------+ | root | 127.0.0.1 | | root | 192.168.30.% | | root | ::1 | | | localhost | | root | localhost | | | template.com | | root | template.com | +------+--------------+ 7 rows in set (0.00 sec)
