| 一文看尽MySQL用户权限管理，真香！ | 您所在的位置：网站首页 › mysql用户权限表设计 › 一文看尽MySQL用户权限管理，真香！ |
一文看尽MySQL用户权限管理，真香！
|
关于MySQL的权限简单的理解就是MySQL允许你做你权利以内的事情，不可以越界。MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，由mysql_install_db脚本初始化。 存储账户权限信息表主要有：user、db、tables_priv、columns_priv、procs_priv、proxies_priv这六张表（5.6之前还有host表，现在已经把host内容整合进user表） 权限管理概念MySQL用户权限管理主要有以下作用： 可以限制用户访问哪些库、哪些表可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作可以限制用户登录的IP或域名可以限制用户自己的权限是否可以授权给别的用户用户操作MySQL数据库的权限管理，主要分为两个阶段： 有没有权限连接上来有没有权限执行本操作1. MySQL连接权限服务器如何判断用户有没有权连接上来？ 依据： 你从哪里来？host你是谁？user你的密码是多少？password用户的这三个信息，存储在mysql库中的user表中。 2. 操作执行权限mysql数据库(系统数据库)下的表：user、db、tables_priv、columns_priv、proce_priv、proxies_priv共同构成授权表； 1）user表user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。 2）db表db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。 3）tables_priv表tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。 4）columns_priv表columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。 5）proce_privcolumns_priv表指定存储过程权限。这里代表允许使用某个存储过程的权限。 6）proxies_priv利用 MySQL proxies_priv（模拟角色）实现类似用户组管理。角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。 注：MySQL5.7.X以后可以模拟角色(Role)的功能，通过mysql.proxies_priv模拟实现 3. MySQL执行权限检查顺序 mysql执行权限检查顺序 开始查询 校验user表，对于全局权限是ok → 直接执行检验DB表，对于某个有特定的数据库有权限 → 执行检验tables_priv，对于特定数据库下的某些表是有权限 → 执行检验columns_priv，对于特定表中的某些列有权限 → 执行权限分布 可能的设置的权限 表权限 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter' 列权限 'Select', 'Insert', 'Update', 'References' 过程权限 'Execute', 'Alter Routine', 'Grant' 用户登录，user表首先能限制用户登录，其次还保存了该用户的全局权限，如果该用户没有任何权限，那么将从db表中查找该用户是否有某个数据库的操作权限，如果都没有，将从table_priv表中查找该用户是否有某个表的操作权限，如果有，则该用户可以按照已有的权限来操作该表。 实际操作在我们安装完数据库后，只能在本地登录数据库，当进行远程登录时，会提示我们错误。  æä¹è§£å³mysqlä¸å许è¿ç¨è¿æ¥çé误1130 - Host'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server 这时，我们就要进行相应的配置，给特定的用户赋予特定的操作权限。 MySQL中使用GRANT命令和REVOKE命令来管理用户的权限。 创建/授权用户：有两种方式创建MySQL授权用户 方法一、通过insert语句直接操作MySQL系统权限表（此处不再赘述） 方法二、执行create user/grant命令（推荐） 注意： 在试用grant命令创建用户时要注意了，在MySQL5.7.7版本前，如果被授权的用户不存在，那么grant语句会自动创建新的账户，除非设置参数sql_mode 包含 “NO_AUTO_CREATE_USER” 。但从5.7.7版本开始，默认的sql_mode就包含“NO_AUTO_CREATE_USER” （grant语句不再创建新的账户）因此，如果你是用的MySQL版本较新，要注意语法了，需要先用create user创建用户，再用grant命令赋权限； 一、创建用户格式创建用户命令一般格式： create user [用户名]@[访问地址] identified by [密码]举例1：创建zhangsan用户，只是创建用户并没有权限，'localhost'表示只能在本地登录，无法通过远程连接；密码是 password CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'password';举例2：创建lisi用户，只是创建用户并没有权限，密码为 password，%通配符表示任何主机都可以连接（可以远程连接）， CREATE USER 'lisi'@'%' IDENTIFIED BY 'password';举例3：（5.7.7版本前）的创建用户并赋予RELOAD,PROCESS权限 ，在所有的库和表上；*.*：代表所有的库表 GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';二、授权格式及示例：grant [权限1,权限2,权限3] on *.* to user@'host' identified by 'password'常用权限：all privileges、create、drop、select、insert、delete、update 例如，给"zhangsan" 用户管理员权限，并且允许该用户继续给别的用户赋权限 grant all privileges on *.* to 'zhangsan'@'192.168.1.%' with grant option;all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.userto：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：'zhangsan'@'192.168.1.%'，表示zhangsan这个用户只能在192.168.0.* IP段登录with grant option：通过在grant语句的最后使用该子句，就允许被授权的用户把得到的权限继续授给其它用户注：使用GRANT添加权限，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个UPDATE权限，那么该用户就同时拥有了SELECT和UPDATE权限。 1. 授予管理员权限举例4：把zhangsan 变成管理员用户,拥有root权限 mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost' WITH GRANT OPTION; #刷新权限命令 mysql> flush privileges;2. 授予数据库、表权限举例5：授予用户zhangsan可以对test数据库中的所有表进行查询 grant select on test.* to zhangsan;举例6：授予lisi在test数据库中创建、修改、删除表的权限以及创建视图的权限 grant create、alter、drop、create view on test.* to lisi;举例7：授予lisi可以对当前数据库中的所有表进行查询, * :表示当前数据库 grant select on * to lisi;举例8：授予lisi可以创建、修改、删除数据库以及对所有数据库中的所有表进行create、alter和drop grant create,alter,drop on . to lisi；举例9：授予lisi可以创建新用户 grant create user on . to lisi;3. 授予列权限# 例10：给zhangsan用户赋权限，设置为在test库，shop表，上的id、name、price列只有select 权限 mysql> grant select(id,name,price) on test.temp to zhangsan@'localhost' WITH GRANT OPTION; #刷新权限 mysql> flush privileges;# 举例11：授予用户zhangsan可以对test.shop表的id和name列进行更新 grant update(id,name) on test.shop to zhangsan@'localhost';# 举例12：查看自己的权限： show grants;三、修改用户配置：1. 回收 mysql 权限通过 revoke 命令收回用户权限,回收的时候看一下这个用户有哪些权限然后回收 show grants for admin@'localhost';mysql> show grants \G *************************** 1. row *************************** Grants for admin@localhost: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION 1 row in set (0.00 sec)revoke ：回收权限 mysql> revoke PROCESS ON *.* FROM admin@'localhost'; #刷新权限 mysql> flush privileges;2. 删除 mysql 用户drop user admin@'localhost';3. 修改 mysql 用户密码修改用户密码的方式包括以下三种： -- 第一种 ALTER USER 'chenhh'@'localhost' IDENTIFIED BY 'mypassword'; -- 第二种 SET PASSWORD FOR 'chenhh'@'localhost' = PASSWORD('mypassword'); -- 第三种 GRANT USAGE ON *.* TO 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';如在数据库外命令行中，通过：mysqladmin -u用户名 -p旧的密码 password 新密码 shell> mysqladmin -u user_name -h host_name password "new_password"# 修改当前会话本身用户密码的方式包括： -- 第一种 ALTER USER USER() IDENTIFIED BY 'mypassword'; -- 第二种 SET PASSWORD = PASSWORD('mypassword');4. 忘记密码1> 添加登录跳过权限检查配置 修改my.cnf，在mysqld配置节点添加skip-grant-tables配置 [mysqld] skip-grant-tables2> 重新启动mysql服务 shell> service mysqld restart3> 修改密码 -- 第一种 ALTER USER 'chenhh'@'localhost' IDENTIFIED BY 'mypassword'; -- 第二种 SET PASSWORD FOR 'chenhh'@'localhost' = PASSWORD('mypassword'); -- 第三种 GRANT USAGE ON *.* TO 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';5. 设置MySQL用户密码过期策略设置系统参数default_password_lifetime作用于所有的用户账户，修改my.cnf系统文件重启MySQL即可 [mysqld] default_password_lifetime=90 #default_password_lifetime=180 设置180天过期 #default_password_lifetime=0 设置密码不过期如果为每个用户设置了密码过期策略，则会覆盖上述系统参数，设置方式如下： -- 90天过期 ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; -- 密码不过期 ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE NEVER; -- 默认过期策略 ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE DEFAULT;手动强制某个用户密码过期 ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE;6. mysql 用户 lock通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态 Create user语句默认的用户是unlock状态 创建的时候给用户锁定方式 create user chenhh@localhost identified by 'mysql' account lock;Alter user语句默认不会修改用户的lock/unlock状态 # 修改用户为unlock alter user chenhh@'localhost' account unlock;当客户端使用lock状态的用户登录MySQL时，会收到如此报错 Access denied for user ‘user_name’@’host_name’. Account is locked. 7. 用户重命名rename user 'chenhh'@'%' to 'zhoujielun'@'%'; |
【本文地址】