您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- mysql- MySQL之用户管理

MySQL之用户管理

bubuko 2022/1/25 20:08:52 mysql 字数 31000 阅读 833 来源 http://www.bubuko.com/infolist-5-1.html

#前言：我们知道，无论是登陆MySQL数据库还是登陆Linux系统，都需要有用户来登陆。默认情况下，root用户是享有最高权限的超级用户，可以使用包括create,drop,insert等操作，但是我们也需要一些普通用户来进行管理，接下来就让我们对号入座，来进行如何创建用户，授权用户，和删除用户等操 ...

#前言：我们知道，无论是登陆MySQL数据库还是登陆Linux系统，都需要有用户来登陆。默认情况下，root用户是享有最高权限的超级用户，可以使用包括create,drop,insert等操作，但是我们也需要一些普通用户来进行管理，接下来就让我们对号入座，来进行如何创建用户，授权用户，和删除用户等操作

#1.通过grant命令创建用户并授权

#grant命令语法：

#grant all privileges on dbname.* to username@localhost identified by ‘passwd‘;
#说明：上述命令使授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd，其中username,dbname,passwd可根据情况修改

#语法解释
grant：授权命令
all privileges：对应权限
on dbname.*：目标：库和表
to username@localhhost ：用户名和客户端主机
identified by ‘passwd‘：用户密码

 

#例子：

#创建test用户，对db库具备所有权限，允许从localhost主机登陆管理数据库，密码使用guoke123

mysql> create database db; #创建数据库
Query OK, 1 row affected (0.00 sec)


mysql> grant all privileges on db.* to ‘test‘@‘localhost‘ identified by ‘guoke123‘; #创建用户并授权
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user; #查看用户
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | localhost        |
| test          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql> flush privileges;  #刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for ‘test‘@‘localhost‘;  #查看用户权限
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘localhost‘             |
| GRANT ALL PRIVILEGES ON `db`.* TO ‘test‘@‘localhost‘ |
+------------------------------------------------------+
2 rows in set (0.00 sec)

 

#2.使用create和grant配合创建用户

#查看帮助：help grant

技术分享图片 
mysql> help grant
Name: ‘GRANT‘
..........
Each account name uses the format described in
https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘;

The host name part of the account, if omitted, defaults to ‘%‘.

Normally, a database administrator first uses CREATE USER to create an
account and define its nonprivilege characteristics such as its
password, whether it uses secure connections, and limits on access to
server resources, then uses GRANT to define its privileges. ALTER USER
may be used to change the nonprivilege characteristics of existing
accounts. For example:

CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘password‘;
GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘;
GRANT SELECT ON db2.invoice TO ‘jeffrey‘@‘localhost‘;
ALTER USER ‘jeffrey‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 90;
View Code

 

#例子：

#使用create创建用户，再使用grant授权

mysql> create user ‘demo‘@‘localhost‘ identified by ‘guoke123‘; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db.* to ‘demo‘@‘localhost‘;  #授权
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user; #查看用户
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | localhost        |
| demo          | localhost |
+---------------+-----------+
6 rows in set (0.01 sec)

#查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

 

 

#3.使用Revoke收回权限

#查看命令帮助：help revoke

技术分享图片 
mysql> help revoke
Name: ‘REVOKE‘
Description:
Syntax:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

........
https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

REVOKE INSERT ON *.* FROM ‘jeffrey‘@‘localhost‘;
View Code

 

#例子：收回test用户的插入权限

#1.首先查看test用户拥有什么权限，查看到是all所有权限
mysql> show grants for ‘test‘@‘localhost‘;
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘localhost‘             |
| GRANT ALL PRIVILEGES ON `db`.* TO ‘test‘@‘localhost‘ |
+------------------------------------------------------+
2 rows in set (0.00 sec)

#2.收回insert权限
mysql> revoke insert on db.* from ‘test‘@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)

#3.再次查看，就没有insert权限了
mysql> show grants for ‘test‘@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘localhost‘                                                                                                                                                                                   |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db`.* TO ‘test‘@‘localhost‘ |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#提示：如果不生效的话
#    查看有没有对上用户管理的数据库名字
mysql> show grants for ‘test‘@‘localhost‘;
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘localhost‘             |
| GRANT ALL PRIVILEGES ON `db`.* TO ‘test‘@‘localhost‘ |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke insert on *.* from ‘test‘@‘localhost‘;  #这里*.*就会不生效，改成db.*
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for ‘test‘@‘localhost‘;
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘localhost‘             |
| GRANT ALL PRIVILEGES ON `db`.* TO ‘test‘@‘localhost‘ |
+------------------------------------------------------+
2 rows in set (0.00 sec)

 

#在创建用户的时候将指定想要的权限，使用，分隔

#例子:创建guoke用户的时候给insert,select,create权限
mysql> create user ‘guoke‘@‘localhost‘ identified by ‘guoke123‘; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert,select,create on db.* to ‘guoke‘@‘localhost‘; #授权
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

#4.企业生产环境的用户授权

mysql> grant select,insert,update,delete on db.* to ‘li‘@‘localhost‘ identified by ‘guoke123‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#一般情况下，授权select,insert,update,delete 4个权限即可，有些可能需要create,drop等比较危险的权限，可以再创建数据库后再将危险权限收回

mysql> revoke create on *.* from ‘test‘@‘localhost‘;
mysql> revoke drop on *.* from ‘test‘@‘localhost‘;

 

#查看all里面包含着什么权限

技术分享图片 
[root@cots3 ~]# mysql -uroot -p -e "show grants for ‘test‘@localhost" | grep -i grant | tail -1 | tr ‘,‘ ‘\n‘
Enter password: 
 GRANT 
 SELECT
 INSERT
 UPDATE
 DELETE
 CREATE
 DROP
 REFERENCES
 INDEX
 ALTER
 CREATE TEMPORARY TABLES
 LOCK TABLES
 EXECUTE
 CREATE VIEW
 SHOW VIEW
 CREATE ROUTINE
 ALTER ROUTINE
 EVENT
#提示：之前test是有所有权限，然后使用revoke将insert权限收回了，就可以查看其他的权限，上面也将INSERT权限写进去了
View Code

 

#5.本地及远程授权

#1.百分号授权法

# 允许所有主机连接%
mysql> grant all privileges on db.* to test1@‘%‘ identified by ‘guoke123‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 允许一个网段
mysql> grant all privileges on db.* to test2@‘1.1.1.%‘ identified by ‘guoke123‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#允许一个IP
mysql> grant all privileges on db.* to test3@‘1.1.1.1‘ identified by ‘guoke123‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)



#2.子网掩码配置法
mysql> grant all privileges on db.* to test4@‘1.1.1.0/255.255.255.0‘ identified by ‘guoke123‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#查看
mysql> select user,host from mysql.user;
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test1         | %                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
| test3         | 1.1.1.1               |

 

#6.mysql客户端连接远程MySQL方法

#语法：mysql -u用户名 -p密码 -h主机

#例子：mysql -uroot -p"guoke123" -h192.168.226.146

 

#7.删除MySQL用户

#语法：drop user "user"@"主机"

#查看帮助：help drop user

mysql> help drop user #查看帮助
Name: ‘DROP USER‘
Description:
Syntax:
DROP USER [IF EXISTS] user [, user] ...

https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

DROP USER ‘jeffrey‘@‘localhost‘;

#例子：

mysql> select user,host from mysql.user;  #查看用户
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test1         | %                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
+---------------+-----------------------+
12 rows in set (0.00 sec)

mysql> drop user ‘test1‘@‘%‘;  #删除test1用户
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
+---------------+-----------------------+
11 rows in set (0.00 sec)

 

MySQL之用户管理

原文：https://www.cnblogs.com/guoke-boy/p/12391441.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶