水利建设相关网站,网站和新媒体建设管理,四川省重庆市,深圳做网站(信科网络)一、MySQL 权限场景
可以根据登录用户限制用户访问资源(库、表)可以根据登录用户限制用户的操作权限(能对哪些库、表执行增删改查操作)可以指定用户登录IP或者域名可以限制用户权限分配
二、Mysql五个层级权限级别分析 Mysql权限级别分为了五个层级#xff0c;并且每个级别… 一、MySQL 权限场景
可以根据登录用户限制用户访问资源(库、表)可以根据登录用户限制用户的操作权限(能对哪些库、表执行增删改查操作)可以指定用户登录IP或者域名可以限制用户权限分配
二、Mysql五个层级权限级别分析 Mysql权限级别分为了五个层级并且每个级别的权限都对应着不同的表这些表都存在于mysql库下在Mysql官方文档中有详细介绍权限的概念MYSQL官方中文文档以下将简单讲述这五个级别的作用和范围。
1全局级别 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*用于授予和撤销全局权限。
在mysql数据库里存在一张user表mysql中所有的用户都是存放在user表中user表的字段可以分为四大类(用户列、权限列、安全例、资源控制列) 1.1 USER表的组成结构 通过查看user表结构可以看出user表保存了用户的登录信息和权限这个权限是全局性的针对的是全部数据库资源换句话说只要user表里的某个权限是Y(Y表示YES,N表NO)那么这个权限可以作用于全部数据库资源。 表名称user描述:Users and global privileges字符集:utf8数据库:mysqlrm-d2xt21zt5fp4k7m94.mysql.rds.ops.zzub-chinaunicom.cn:3306【zzzhjc-master-mysql】列名类型长度精度标度是否为空缺省值描述安全级别Hostchar(60)60N低敏感Userchar(32)32N低敏感Select_privenum(N,Y)1NN低敏感Insert_privenum(N,Y)1NN低敏感Update_privenum(N,Y)1NN低敏感Delete_privenum(N,Y)1NN低敏感Create_privenum(N,Y)1NN低敏感Drop_privenum(N,Y)1NN低敏感Reload_privenum(N,Y)1NN低敏感Shutdown_privenum(N,Y)1NN低敏感Process_privenum(N,Y)1NN低敏感File_privenum(N,Y)1NN低敏感Grant_privenum(N,Y)1NN低敏感References_privenum(N,Y)1NN低敏感Index_privenum(N,Y)1NN低敏感Alter_privenum(N,Y)1NN低敏感Show_db_privenum(N,Y)1NN低敏感Super_privenum(N,Y)1NN低敏感Create_tmp_table_privenum(N,Y)1NN低敏感Lock_tables_privenum(N,Y)1NN低敏感Execute_privenum(N,Y)1NN低敏感Repl_slave_privenum(N,Y)1NN低敏感Repl_client_privenum(N,Y)1NN低敏感Create_view_privenum(N,Y)1NN低敏感Show_view_privenum(N,Y)1NN低敏感Create_routine_privenum(N,Y)1NN低敏感Alter_routine_privenum(N,Y)1NN低敏感Create_user_privenum(N,Y)1NN低敏感Event_privenum(N,Y)1NN低敏感Trigger_privenum(N,Y)1NN低敏感Create_tablespace_privenum(N,Y)1NN低敏感ssl_typeenum(,ANY,X509,SPECIFIED)9N低敏感ssl_cipherblob65535N低敏感x509_issuerblob65535N低敏感x509_subjectblob65535N低敏感max_questionsint(11) unsigned100N0低敏感max_updatesint(11) unsigned100N0低敏感max_connectionsint(11) unsigned100N0低敏感max_user_connectionsint(11) unsigned100N0低敏感pluginchar(64)64Nmysql_native_password低敏感authentication_stringtext65535Y低敏感password_expiredenum(N,Y)1NN低敏感password_last_changedtimestampY低敏感password_lifetimesmallint(5) unsigned50Y低敏感account_lockedenum(N,Y)1NN低敏感索引名类型包含字段PRIMARYPrimaryHost,User 1.1.1 用户列 user表中的用户列包括字段Host、User、Password:
Host: 登录的主机名表示允许用户从哪台机器登录接连到Mysql服务器。可以使用IP地址或者域名%表示允许从任何主机连接到Mysql服务器。 User:登录的用户名 Password: 用户登录数据库的密码当该字段为空时表示该用户账号登录不需要密码
1.1.2 权限列 user表中的权限列是以_priv结尾的字段这些字段默认为NN表示该权限不能用到所有数据库上Y则与N的效果相反。以下是权限列各个字段的说明:
列名说明Select_priv是否拥有SELECT权限命令查询数据Insert_priv是否拥有INSERT权限命令插入数据Update_priv是否拥有UPDATE权限命令修改数据Delete_priv是否拥有DELETE权限命令删除数据Create_priv是否拥有权限创建新的数据库和表Drop_priv是否拥有权限删除现有数据库和表Reload_priv是否拥有权限执行刷新和重新加载MySQL所用各种内部缓存的特定命令包括日志、权限、主机、查询和表Shutdown_priv是否拥有权限关闭MySQL服务器Process_priv是否拥有权限通过SHOW PROCESSLIST命令查看其他用户的进程File_priv是否拥有权限执行SELECT INTO OUTFILE和LOAD DATA INFILE命令Grant_priv是否拥有权限将自己已授权的权限授权给其他用户Index_priv是否拥有权限创建和删除表索引Alter_priv是否拥有权限重命名和修改表结构Show_db_priv是否拥有权限查看服务器上所有数据库的名字包括用户拥有足够访问权限的数据库Super_priv是否拥有权限执行某些强大的管理功能例如通过KILL命令删除用户进程使用SET GLOBAL修改全局MySQL变量执行关于复制和日志的各种命令Create_tmp_table_priv是否拥有权限创建临时表Lock_tables_priv是否拥有权限使用LOCK TABLES命令阻止对表的访问/修改Repl_slave_priv是否拥有权限读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中有利于主机和客户机之间的通信Create_view_priv是否拥有权限创建视图Show_view_priv是否拥有权限查看视图或了解视图如何执行Create_routine_priv是否拥有权限更改或放弃存储过程和函数Alter_routine_priv是否拥有权限修改或删除存储函数及函数Create_user_priv是否拥有权限执行CREATE USER命令这个命令用于创建新的MySQL账户Event_priv是否拥有权限创建、修改和删除事件Trigger_priv是否拥有权限创建和删除触发器 1.1.3 安全列 user表的安全列有4个字段ssl_type、ssl_cipher、x509_issuer、x509_subject ssl用于加密 x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED那么则没有ssl加密功能。 1.1.4 资源控制列 user表的4个资源控制列是max_questions、max_updates、max_connections、max_user_connections max_questions每小时可以允许执行多少次查询 max_updates每小时可以允许执行多少次更新 max_connections每小时可以建立多少连接 max_user_connections单个用户可以同时具有的连接数。 默认值为0表示无限制。
2数据库层级 数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*用于授予和撤销数据库权限。 刚刚我们已经讨论了全局级别的权限配置对user表进行了解析但是user表上的权限都是针对与所有数据库的如果我们想将权限力度划分的更细如果我想让某个数据库用户只能查看某个数据库里的表数据那么此时就需要用到数据库层级的权限控制主要分析mysql库里的db表。
2.1db表结构 表名称db描述:Database privileges字符集:utf8数据库:mysqlrm-d2xt21zt5fp4k7m94.mysql.rds.ops.zzub-chinaunicom.cn:3306【zzzhjc-master-mysql】列名类型长度精度标度是否为空缺省值描述安全级别Hostchar(60)60N低敏感Dbchar(64)64N低敏感Userchar(32)32N低敏感Select_privenum(N,Y)1NN低敏感Insert_privenum(N,Y)1NN低敏感Update_privenum(N,Y)1NN低敏感Delete_privenum(N,Y)1NN低敏感Create_privenum(N,Y)1NN低敏感Drop_privenum(N,Y)1NN低敏感Grant_privenum(N,Y)1NN低敏感References_privenum(N,Y)1NN低敏感Index_privenum(N,Y)1NN低敏感Alter_privenum(N,Y)1NN低敏感Create_tmp_table_privenum(N,Y)1NN低敏感Lock_tables_privenum(N,Y)1NN低敏感Create_view_privenum(N,Y)1NN低敏感Show_view_privenum(N,Y)1NN低敏感Create_routine_privenum(N,Y)1NN低敏感Alter_routine_privenum(N,Y)1NN低敏感Execute_privenum(N,Y)1NN低敏感Event_privenum(N,Y)1NN低敏感Trigger_privenum(N,Y)1NN低敏感索引名类型包含字段PRIMARYPrimaryHost,Db,UserUserNormalUser db表里的权限列与user表里的含义差不多一致只是db表的权限只是针对与某个数据库而非全局数据库。其中DB字段存储的是数据库名称。 3表层级 表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限 如果你清楚了全局级别的权限和数据库层的权限那么表层级的权限就相对好理解了表层级的权限是用于控制用户对某个数据库里的某个表是否有权限进行操作。比如你想指定某个用户只能操作指定数据库里的某张表此时就需要用到表层级的权限来控制。
3.1tables_priv表结构 tables_priv表的字段相比较与user和db表要少的多因为它控制的权限更细它的结构如下
表名称tables_priv描述:Table privileges字符集:utf8数据库:mysqlrm-d2xt21zt5fp4k7m94.mysql.rds.ops.zzub-chinaunicom.cn:3306【zzzhjc-master-mysql】列名类型长度精度标度是否为空缺省值描述安全级别Hostchar(60)60N低敏感Dbchar(64)64N低敏感Userchar(32)32N低敏感Table_namechar(64)64N低敏感Grantorchar(93)93N低敏感TimestamptimestampNCURRENT_TIMESTAMP低敏感Table_privset(Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger)98N低敏感Column_privset(Select,Insert,Update,References)31N低敏感索引名类型包含字段PRIMARYPrimaryHost,Db,User,Table_nameGrantorNormalGrantor 3.2 tables_priv表的组成结构 列名说明Host用户登录的主机可以是IP或者域名Db数据库名称User登录用户名Talbe_name表名称Grantor权限的设置者Table_priv对表进行操作的权限对表的操作权限有Select、Insert、Update、Delete、Create、Drop、Grant、Index、Alter、Create View、Show View以及TriggerColumn_priv对表中的数据列进行操作的权限 Insert、Update、References 4列层级 列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时您必须指定与被授权列相同的列。
列层级的使用还是比较少所以这里就不再过多阐述。
4.1columns_priv表结构
表名称columns_priv描述:Column privileges字符集:utf8数据库:mysqlrm-d2xt21zt5fp4k7m94.mysql.rds.ops.zzub-chinaunicom.cn:3306【zzzhjc-master-mysql】列名类型长度精度标度是否为空缺省值描述安全级别Hostchar(60)60N低敏感Dbchar(64)64N低敏感Userchar(32)32N低敏感Table_namechar(64)64N低敏感Column_namechar(64)64N低敏感TimestamptimestampNCURRENT_TIMESTAMP低敏感Column_privset(Select,Insert,Update,References)31N低敏感索引名类型包含字段PRIMARYPrimaryHost,Db,User,Table_name,Column_name 5子程序层级 REATE ROUTINE, ALTER ROUTINE,EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且除了CREATE ROUTINE外这些权限可以被授予为子程序层级并存储在mysql.procs_priv表中。
5.1procs_priv表结构
表名称procs_priv描述:Procedure privileges字符集:utf8数据库:mysqlrm-d2xt21zt5fp4k7m94.mysql.rds.ops.zzub-chinaunicom.cn:3306【zzzhjc-master-mysql】列名类型长度精度标度是否为空缺省值描述安全级别Hostchar(60)60N低敏感Dbchar(64)64N低敏感Userchar(32)32N低敏感Routine_namechar(64)64N低敏感Routine_typeenum(FUNCTION,PROCEDURE)9N低敏感Grantorchar(93)93N低敏感Proc_privset(Execute,Alter Routine,Grant)27N低敏感TimestamptimestampNCURRENT_TIMESTAMP低敏感索引名类型包含字段PRIMARYPrimaryHost,Db,User,Routine_name,Routine_typeGrantorNormalGrantor 6权限分配顺序 MySQL权限分配顺序是按照层级由大到小进行解析的也就是先查看 user表所有数据库–db表(某个数据库)-table_priv(某个表)-columns_pirv(某列)进行查找。 如果发现user表中某个权限是Y那么就不会继续往下找如果User表某个权限是N那么就去找db表依次往下找。 Mysql用户权限分配详解_mysql分配用户权限_对酒当歌丶人生几何的博客-CSDN博客
二、实战用户权限分配 1新建用户 创建用户常用的命令是CREATE USER不过使用CREATE USER语句创建用户时需要操作员要有相应的权限也就是user表中的字段Create_priv为Y才能创建用户。比如创建一个名为admin的用户并且登录密码为123456:
CREATE USER ‘admin’‘%’ IDENTIFIED BY ‘123456’ 一般创建用户的格式为CREATE USER ‘用户名’‘登录IP或者域名’ IDENTIFIED BY ‘登录密码’。 其中需要注意的是登录IP或者域名很多时候会将登录IP设置为**%%**代表所有主机表示该用户可以在任何机器上进行登录。192.168.101.%表示允许192.168.101这个网段的主机进行登录。
当你只是完上面的创建admin用户命令后你可以打开mysql库下的user表你会发现里面多了一条admin的数据但是所有的权限都是N这表明该admin用户没有任何权限不能查看操作任何数据库资源。
此时你可以尝试使用admin用户登录你现有的数据库你会发现admin账号只能查看information_schema这个数据库那是因为我们在创建admin账号时并没有分配任何权限。
如果你想在创建账号的时候就分配给该账号一定的权限那么你可以使用 : grant all privileges on *.* to admin% identified by 123456 with grant option; 该语句表示创建admin账号允许从任何主机登录并且拥有数据库全部的权限可以将自己的权限授予给别人。我们将这条语句拆分:
创建账号时并赋予全部权限: grant all privileges on *.* to 用户名登录IP或者域名 identified by 登录密码 with grant option; all privileges: 表示授权用户全部权限,当然你也可以指定赋予具体权限比如SELECT、UPDATE、CREATE、DROP等。 on:表示这些权限可以作用于哪些数据库和哪些表*.*表示作用于所有数据库和所有表格式为 数据库名.表名。比如有一个test库想让admin拥有查看test库所有表的权限那么可以这样写grant SELECT on test.* to admin% identified by 登录密码; to将权限授予哪个用户。格式用户名登录IP或域名。 IDENTIFIED by指定用户的登录密码。 with grant option表示允许用户将自己的权限授权给其它用户。 1 2 3 4 5 6 7 但请注意可能由于MySQL的数据库版本问题有些版本不允许在创建用户时就给予权限那么需要将创建和权限赋予分为两步:
create user admin% identified by 123456 -- 创建用户 GRANT all privileges ON *.* TO admin% -- 将所有权限赋予给admin 1 2 一般来说我们不会轻易创建一个用户给他赋予所有权限也就是 grant all privileges那么grant后面可以接哪些权限呢这里简单整理了一部分权限以及其含义
权限 意义 ALL [PRIVILEGES] 设置除GRANT OPTION之外的所有简单权限 ALTER 允许使用ALTER TABLE ALTER ROUTINE 更改或取消已存储的子程序 CREATE 允许使用CREATE TABLE CREATE USER 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 CREATE VIEW 允许使用CREATE VIEW DELETE 允许使用DELETE DROP 允许使用DROP TABLE INDEX 允许使用CREATE INDEX和DROP INDEX INSERT 允许使用INSERT SELECT 允许使用SELECT SHOW DATABASES SHOW DATABASES显示所有数据库 SHOW VIEW 允许使用SHOW CREATE VIEW SHUTDOWN 允许使用mysqladmin shutdown UPDATE 允许使用UPDATE GRANT OPTION 允许授予权限 以上表格举例了部分权限以及其含义如果你想给一个账号赋予多个权限可以用英文逗号分开比如Grant SELECT,UPDATE,DROP等如果想连接更多信息欢迎点击Mysql账号管理查看
2删除用户 刚刚我们已经创建了一个admin账号并且没有为其设置任何权限此时我们可以使用 DROP USER 命令删除某个用户例如我们要删除之前创建的admin用户那么我们可以使用命令:
DROP USER admin% -- 删除admin用户用户名(User)和主机名(Host)构成 FLUSH PRIVILEGES; -- 刷新权限 1 2 此时user表中的admin用户就删除了。 3禁用用户、启用用户 有时候我们只想禁用某个用户的登录并不想将该用户进行删除那么可以使用MySQL的禁用功能。在演示之前我们现在创建一个admin用户该用户拥有访问数据库(db_cztl_dev)的权限请注意db_cztl_dev是在我MySQL中存在的一个数据库您可以使用您自己的数据库。执行命令:
create user admin% identified by 123456 -- 创建用户 GRANT SELECT ON db_cztl_dev.* TO admin% -- 将db_cztl_dev数据库的读权限赋予admin 1 2 此时user表会有admin账号的信息我们可以看到user表里的admin的select_priv是为N因为admin现在的权限只是能访问某一个数据库而非全部数据库所以user表里的select_pirv是为N但是db表(数据库层级)会在db_cztl_dev那条数据的select_priv为N。 表示admin可以访问数据库db_cztl_dev拥有该数据库下的所有SELECT权限让我们用admin账号登录就可以看到db_cztl_dev数据库。
如果我们要禁用admin账号可以输入命令:
ALTER USER admin% ACCOUNT lock; -- 禁用admin账号 FLUSH PRIVILEGES; -- 刷新权限 1 2 此时重新使用admin用户登录就可以提示以下信息:
解除admin禁用可以使用命令:
ALTER USER admin% ACCOUNT UNLOCK; --解除admin用户 FLUSH PRIVILEGES; -- 刷新权限 1 2 4重命名用户名 修改用户名的格式如下
rename user 用户名IP或者域名 to 新用户名IP或者域名; 1 比如我们要将admin用户重名为king那么可以这样写
rename user admin% to king%; 1 5修改用户密码 set password for 用户名IP或者域名password(新密码) 1 6收回权限(revoke) 根据上面几小节我们指定如何给一个用户赋予权限可以用Grant命令相对应的撤销用户的某个权限可以使用REVOKE。
6.1查看数据库中所有用户信息 我们知道用户的基本信息都存放在user表中那么查看所有用户信息可以使用以下语句:
SELECT user,host FROM mysql.user; 1 6.2查看用户拥有的所有权限 查看某个用户的权限可以使用 show grants for 用户名比如我们要查看admin用户的权限有哪些:
SHOW GRANTS FOR admin% 1 可以看到admin用户拥有查看数据库db_cztl_dev下所有资源的权限。 6.3使用REVOKE撤销权限 由4.2小节我们可以看到admin拥有访问db_cztl_dev数据库的权限假如我们此时想撤销admin的权限可以这样写:
REVOKE SELECT ON db_cztl_dev.* FROM admin%; -- 撤销admin在数据db_cztl_dev的权限 1 此时我们再使用SHOW GRANTS FOR admin%查看admin权限可以看到admin访问db_cztl_dev数据库的权限已被撤销。 7修改用户权限(Grant 权限类型 ON 数据库.表 TO 用户) 通过6.3我们已经撤销了admin查看db_cztl_dev的权限如果此时我们想赋予admin用户可以访问并且修改db_cztl_dev库下cztl_line表的权限可以这样做:
GRANT SELECT,UPDATE ON db_cztl_dev.cztl_line TO admin%; FLUSH PRIVILEGES; 1 2 重新使用admin账号登录数据库就可以访问和修改db_cztl_dev库下的cztl_line表。
如果想让admin用户能访问整个db_cztl_dev库下所有资源可以追加权限
GRANT SELECT ON db_cztl_dev.* TO admin% 1 重新admin登录就可以查到所有表 8 设置MySQL用户密码过期策略 设置系统参数default_password_lifetime作用于所有的用户账户可以通过show variables like ‘default_password_lifetime’;查看密码过期策略0表示用不过期可以通过 set global default_password_lifetime180;设置密码过期时间为180天。 8.1手动强制某个用户密码过期 ALTER USER 用户名IP或者域名 PASSWORD EXPIRE; 1 假如我们要将admin账号强制密码过期可以这样做:
ALTER USER admin% PASSWORD EXPIRE; --强制admin用户密码过期 1 重启使用admin登录时会弹出重置密码界面。
