第三课mysql授权认证

1、MySQL授权认证-崔冬青 老男孩IT教育，只培养技术精英MySQL权限系统介绍 权限系统的作用是授予来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限 不能明确的指定拒绝某个用户的连接 权限控制(授权与回收)的执行语句包括create user, grant, revoke 授权后的权限都会存放在MySQL的内部数据库中（数据库名叫 mysql）,并在数据库启动之后把权限信息复制到内存中 MySQL用户的认证信息不光包括用户名，还要包含连接发起的主 机(以下两个joe被认为不是同一个用户) SHOW GRANTS FOR ; SHOW

2、 GRANTS FOR ;MySQL权限级别介绍 MySQL权限级别 全局性的管理权限，作用于整个MySQL实例级别 数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上 数据库对象级别的权限，作用于指定的数据库对象上（表、视图等）或者所有的数据库对象上 权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中 MySQL权限级别介绍 查看mysql实例默认root用户的权限(来自localhost)mysql show gra

3、nts for rootlocalhost;+ +| Grants for rootlocalhost|+ +| GRANT ALL PRIVILEGES ON *.* TO rootlocalhost WITH GRANT OPTION | 对比root用户在几个权限系统表中的数据 mysql select * from user where user=root and host=localhost; #都是Ymysql select * from db where user=root and host=localhost; #无记录 mysql select * from tables_p

4、riv where host=localhost and user=root; #无记录mysql select * from columns_priv where user=root and host=localhost; #无记录mysql select * from procs_priv where user=root and host=localhost; #无记录 MySQL权限级别介绍 查看mysql实例默认mysql.sys用户的权限(来自localhost) mysql show grants formysql.syslocalhost;+ +| Grants for mysq

5、l.syslocalhost+ +| GRANT USAGE ON *.* TO mysql.syslocalhost| GRANT TRIGGER ON sys.* TO mysql.syslocalhost| GRANT SELECT ON sys.sys_config TO mysql.syslocalhost | 对比mysql.sys用户在几个权限系统表中的数据 mysql select * from user where user=mysql.sys and host=localhost; #都是Nmysql select * from db where user=mysql.sy

6、s and host=localhost; #一条记录，在sys数据库上的Trigger_priv字段为Y,mysql select * from tables_priv where host=localhost and user=mysql.sys; #一条记录，在sys数据库的sys_config表上有select权限 mysql select * from columns_priv where user=mysql.sys and host=localhost; #无记录 mysql select * from procs_priv where user=mysql.sys and ho

7、st=localhost; #无记录 MySQL权限详解(1) All/All Privileges权限代表全局或者全数据库对象级别的所有权限 Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表，create和insert新表的权限 Alter routine权限代表允许修改或者删除存储过程、函数的权限 Create权限代表允许创建新的数据库和表的权限 Create routine权限代表允许创建存储过程、函数的权限 Create tablespace权限代表允许创建、修改、删除表空间和日志组的权限

8、Create temporary tables权限代表允许创建临时表的权限 Create user权限代表允许创建、修改、删除、重命名user的权限 Create view权限代表允许创建视图的权限MySQL权限详解(2)Delete权限代表允许删除行数据的权限 Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令Event权限代表允许查询，创建，修改，删除MySQLExecute权限代表允许执行存储过程和函数的权限 File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select into ou

9、tfile,load file()函数 Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限 Index权限代表是否允许创建和删除索引 Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限 Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表 的读或写 MySQL权限详解(3)Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show

10、 engine等命令 Reference权限是在5.7.6版本之后引入，代表是否允许创建外键 Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中， refresh命令代表关闭和重新开启日志文件并刷新所有的表 Replication client权限代表允许执行show master status,show slave status,show binary logs命令 Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系 Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Se

11、lect 1+1，Select PI()+2；而且select权限在执行update/delete 语句中含有where条件的情况下也是需要的 Show databases权限代表通过执行show databases命令查看所有的数据库名Show view权限代表通过执行show create view命令查看视图创建的语句 MySQL权限详解(4)Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdownSuper权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/al

12、ter/drop server等命令 Trigger权限代表允许创建，删除，执行，显示触发器的权限Update权限代表允许修改表中的数据的权限 Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限 mysql create user abclocalhost;mysql show grants for abclocalhost;+ +| Grants for abclocalhost+ +| GRANT USAGE ON *.* TO abclocalhost |+ +|系统权限表 权 限 存 储 在 mysql 库 的 user,db, tables_priv, columns

13、_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中 User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限 Db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访 问此数据库 Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 Procs_priv表：存放存储过程和函数级别的权限 系统权限表 User

14、和db权限表结构系统权限表 User权限表结构中的特殊字段Plugin,password,authentication_string三个字段存放用户认证信息 Password_expired设置成Y则表明允许DBA将此用户的设置成过期而且过期后要求用户的使用者重置（alter user/set password重置）Password_last_changed作为一个时间戳字段代表上次修改时间，执 Password_lifetime代表从password_last_changed时间开始此数 Account_locked代表此用户被锁住，无法使用 过期的天系统权限表 Tables_priv和co

15、lumns_priv权限表结构 Timestamp和grantor两个字段暂时没用系统权限表 Tables_priv和columns_priv权限值系统权限表 procs_priv权限表结构 Routine_type是枚举类型，代表是存储过程还是函数 Timestamp和grantor两个字段暂时没用 系统权限表 系统权限表字段长度限制表 权限认证中的大小写敏感问题 字段user,password,authencation_string,db,table_name大小写敏感 字段host,column_name,routine_name大小写不敏感 系统权限表 User用户大小写敏感mysql

16、 create user abclocalhost;ERROR 1396 (HY000): Operation CREATE USER failed for abclocalhost mysql create user Abclocalhost;Query OK, 0 rows affected (0.01 sec) Host主机名大小写不敏感 mysql create user abcLocalhost; ERROR 1396 (HY000): Operation CREATE USER failed for abclocalhost查看用户权限信息查看已经授权给用户的权限信息 SHOW G

17、RANTS FOR rootlocalhost;+ +| Grants for rootlocalhost|+ +| GRANT ALL PRIVILEGES ON *.* TO rootlocalhost WITHGRANT OPTION | GRANT PROXY ON TO rootlocalhost WITH GRANT OPTION|查看用户的其他非授权信息mysql show createuser rootlocalhost;+ +| CREATE USER forrootlocalhost|+ +| CREATE USER rootlocalhost IDENTIFIED WIT

18、H mysql_native_password AS *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |+ +MySQL授权用户 MySQL的授权用户由两部分组成：用户名和登录主机名 表达用户的语法为user_namehost_name单引号不是必须，但如果其中包含特殊字符则是必须的 localhost代表登录的用户 Host_name可以使主机名或者ipv4/ipv6的地址。Localhost代表本机，代表ipv4的 本机地址，:1代表

19、ipv6的本机地址 Host_name字段允许使用%和_两个匹配字符，比如%代表所有主机，%.代表 来自这个域名下的所有主机，192.168.1.%代表所有来自192.168.1网段的主机 MySQL修改权限的生效执行Grant,revoke,set password,rename user命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中 如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括：flush privileges/mysqladmin fl

20、ush-privileges/mysqladmin reload如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效 如果是修改database级别的权限，则新权限在客户端执行use database命令后生效 如果是修改global级别的权限，则需要重新创建连接新权限才能生效 -skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用 MySQL用户连接shell mysql -user=finley -password db_nameshell mysql -u finley -p db_nameshell mysql

21、 -user=finley -password=password db_name shell mysql -u finley -ppassword db_name创建MySQL用户 有两种方式创建MySQL授权用户 执行create user/grant命令（推荐方式） 通过insert语句直接操作MySQL系统权限表 mysql CREATE USER finleylocalhost IDENTIFIED BY some_pass; mysql GRANT ALL PRIVILEGES ON *.* TO finleylocalhost GRANT OPTION; mysql CREATE

22、USER finley% IDENTIFIED BY some_pass;WITH mysql GRANT ALL PRIVILEGES ON *.* TO finley% WITH GRANT OPTION; mysql CREATE USER adminlocalhost IDENTIFIED BYadmin_pass; mysql GRANT RELOAD,PROCESS ON *.* TO adminlocalhost; mysql grant select(id) on test.temp to cdqlocalhost;创建MySQL用户mysql SHOW GRANTS FOR

23、adminlocalhost;+ +| Grants for adminlocalhost+ +| GRANT RELOAD, PROCESS ON *.* TO adminlocalhost|+ +mysql SHOW CREATE USERadminlocalhostG* 1. row * CREATE USER for adminlocalhost: CREATE USER adminlocalhost IDENTIFIED WITHmysql_native_passwordAS *67ACDEBDAB923990001F0FFB017EB8ED41861105 REQUIRE NONE

24、 PASSWORD EXPIRE DEFAULT ACCOUNTUNLOCK创建MySQL用户mysql CREATE USER customlocalhost IDENTIFIEDBY obscure;mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP-ON bankaccount.*TO customlocalhost;mysql CREATE USER IDENTIFIED BY obscure;mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE

25、,DROP-ON expenses.*TO ;mysql CREATE USER custom%. IDENTIFIEDBY obscure;mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP-ON customer.*TO custom%.;回收MySQL用户权限 通过revoke命令收回用户权限 回收MySQL用户权限 通过revoke命令收回用户权限 mysql show grants for mysql.syslocalhost;+ +| Gr

26、ants for mysql.syslocalhost+ +| GRANT USAGE ON *.* TO mysql.syslocalhost| GRANT TRIGGER ON sys.* TO mysql.syslocalhost| GRANT SELECT ON sys.sys_config TO mysql.syslocalhost | mysql revoke select on sys.sys_config from mysql.syslocalhost;mysql show grants for mysql.syslocalhost;+ +| Grants for mysql.

27、syslocalhost+ +| GRANT USAGE ON *.* TO mysql.syslocalhost| GRANT TRIGGER ON sys.* TO mysql.syslocalhost |删除MySQL用户通过执行drop user命令删除MySQL用户 mysql DROP USER jeffreylocalhost;设置MySQL用户资源限制 通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理 MAX_QUERIES_PER_HOUR：

28、一个用户在一个小时内可以执行查询的次数（基本包含所有语句） MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句） MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间 MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例 的数量 从5.0.3版本开始，对用户user%.的资源限制是指所有通过域名主机连接user用户的连接，而不是分别指从和主机过来的连接 设

29、置MySQL用户资源限制通过执行create user/alter user设置/修改用户的资源限制 mysql CREATE USER francislocalhost IDENTIFIED BY frank-WITH MAX_QUERIES_PER_HOUR 20MAX_UPDATES_PER_HOUR 10MAX_CONNECTIONS_PER_HOUR 5MAX_USER_CONNECTIONS 2; mysql ALTER USER francislocalhost WITH MAX_QUERIES_PER_HOUR 100; 取消某项资源限制既是把原先的值修改成0 mysql AL

30、TER USER francislocalhost WITH MAX_CONNECTIONS_PER_HOUR 0; 当针对某个用户的max_user_connections非0时，则忽略全局系统参数 max_user_connections，反之则全局系统参数生效 设置MySQL用户的执行create user创建用户和 mysql CREATE USER jeffreylocalhost IDENTIFIED BY mypass; 修改用户的方式包括：mysql ALTER USER jeffreylocalhost IDENTIFIED BY mypass;mysql SET PASSW

31、ORD FOR jeffreylocalhost = PASSWORD(mypass); mysql GRANT USAGE ON *.* TO jeffreylocalhost IDENTIFIED BY mypass; shell mysqladmin -u user_name -h host_name password new_password 修改本身用户的方式包括： mysql ALTER USER USER() IDENTIFIEDBY mypass; mysql SET PASSWORD = PASSWORD(mypass);设置MySQL用户过期策略设置系统参数default_

32、password_lifetime作用于所有的用户账户 default_password_lifetime=180 设置180天过期 default_password_lifetime=0 设置不过期 如果为每个用户设置了过期策略，则会覆盖上述系统参数 ALTER USER jeffreylocalhost PASSWORD EXPIRE INTERVAL 90 DAY;不过期 ALTER USER jeffreylocalhost PASSWORD EXPIRE NEVER;ALTER USER jeffreylocalhost PASSWORD EXPIRE DEFAULT; 默认过期策略

33、手动强制某个用户过期 ALTER USER jeffreylocalhost PASSWORD EXPIRE;mysql SELECT 1;ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql ALTER USER USER() IDENTIFIED BY new_password;Query OK, 0 rows affected (0.01 sec) mysql SELECT 1;| 1 |MySQL用户lock 通过执行create user/alter user命令中带accountlock/unlock子句设置用户的lock状态 Create user语句默认的用户是unlock状态 mysql create user abc2localhost