MySQL权限

码林季风
• 阅读 110

MySQL权限

mysql的权限存储在mysql库的user, db, tables_priv, columns_priv, procs_priv这几个系统表中

表结构

user表

存放用户账户信息以及全局级别(所有数据库)权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限

<!-- more -->

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',--ip
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',--用户名
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--查询数据权限
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--插入数据权限
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--修改数据权限
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--删除数据权限
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--创建新的数据库和表的权限
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许删除数据库、表、视图的权限，包括truncate table命令
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许关闭数据库实例，执行语句包括mysqladmin shutdown
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许查看MySQL中的进程信息，比如执行show processlist
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用 的命令包括load data infile,select ... into outfile,load file()函数
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许此用户授权或者收回给其他用户赋予的权限
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建外键
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建和删除索引
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许修改表结构的权限，但必须要求有create和insert权 限配合。如果是rename表名，则要求有alter和drop原表，create和 insert新表的权限
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许通过执行show databases命令查看所有的数据库名
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/drop server等命令
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建临时表的权限
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',-- 允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许执行存储过程和函数的权限
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许slave主机通过此用户连接master以便建立主从复制关系
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许执行show master status,show slave status,show binary logs命令
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建视图的权限
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',-- 通过执行show create view命令查看视图创建的语句mysqladmin processlist, show engine等命令
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建存储过程、函数的权限
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许修改或者删除存储过程、函数的权限
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建、修改、删除、重命名user的权限
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许查询，创建，修改，删除MySQL事件
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建，删除，执行，显示触发器的权限
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--允许创建、修改、删除表空间和日志组的权限
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',--限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理，可以使用show global variables like '%max_user_connections%';查看
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin, -- 存放加密之后的密码
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--密码是否会过期，default_password_lifetime存储着密码的默认过期时间，可以使用show global variables like '%default_password_lifetime%';查看,default_password_lifetime设置为0表示不过期
  `password_last_changed` timestamp NULL DEFAULT NULL,--密码上次修改时间
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,--密码的过期时间
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',--用户被锁住，无法使用
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

db表

存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库

CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';

tables_priv表

存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表

CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';

columns_priv表

存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';

procs_priv表

存放存储过程和函数级别的权限

CREATE TABLE `procs_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,--枚举类型，代表是存储过程还是函数
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';

用户授权

用户授权需要User和Host，User@Host

Host字段允许使用%和_两个匹配字符，比如%代表所有主机，%.mysql.com代表来自mysql.com这个域名下的所有主机，192.168.1.%代表所有来自192.168.1网段的主机

授权生效

  • 执行Grant,revoke,setpassword,renameuser命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中
  • 如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括:flush privileges/mysqladmin flush-privileges/mysqladmin reload
  • 如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生 效
  • 如果是修改database级别的权限，则新权限在客户端执行use database命令后生效
  • 如果是修改global级别的权限，则需要重新创建连接新权限才能生效
  • --skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用

设置用户密码

查看密码过期时间

show variables like 'default_password_lifetime';

创建用户、密码

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';

修改密码

ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('mypass');
UPDATE USER SET PASSWORD=PASSWORD("mypass") WHERE `user` = "myuser";
GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypass'; 


-- 操作完之后进行刷新权限
FLUSH PRIVILEGES;
由于权限表信息较少，且访问频繁，MySQL在启动时就会将所有权限信息加载到内存，所以在手工修改权限表后需要执行FLUSH PRIVILEGES;命令来重新加载权限信息，当然在使用GRANT、REVOKE来修改相关权限不需要执行FLUSH PRIVILEGES;命令，因为在修改系统表的同时也会更新内存中的信息

授权

GRANT SELECT,INSERT,UPDATE ON *.* TO  'myuser'@'localhost';
https://zhhll.icu/2021/数据库/关系型数据库/MySQL/基础/17.MySQL权限/

本文由mdnice多平台发布

数据库
点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
•
4年前
mysql 8.0给数据库添加用户和赋权
\使用mysql数据库正式环境USEmysql\为mysql创建用户：waiyan密码为：XXXXXCREATEUSER waiyanIDENTIFIEDBY'XXXXX';\查看下用户waiyan的权限SELECT\FROMuserWHEREuser'waiyan';SH
1069
0
0
Wesley13 Wesley13
•
4年前
MySQL.授权管理
查看权限:showgrantsfor'用户’@‘IP地址’　　授权：grant权限on数据库.表to‘用户’@‘IP地址’取消权限：revoke权限on数据库.表from‘用户’@‘IP地址’常用权限：allprivileges除grant外的所有权限　　　　　select  仅查权限　　　　　select，i
1034
0
0
Wesley13 Wesley13
•
4年前
MySQL权限管理
\权限管理　　\mysql用户权限表　　mysql库的user表　　mysqlselectuser,hostfromuser;　　　　|user|host|　　　　|d
1006
0
0
Wesley13 Wesley13
•
4年前
MySql创建新用户
1.创建数据库：createdatabasephonedefaultcharsetutf8;2.创建数据库用户创建用户账号：qingkong，密码：mysqlcreateuserqingkongidentifiedby"mysql"phone库的所有权限（all）给用户qingkong在
1149
0
0
Stella981 Stella981
•
4年前
Linux（Centos）给MySQL创建用户，并分配权限
//登录MYSQL使用root账号登录mysqlurootp密码　密码//创建用户(%表示可以远程登录该数据库)mysqlcreateuser's_root'@'%'identifiedby'123456';//刷新系统权限表用户授权格式：grant权限on
1310
0
0
Wesley13 Wesley13
•
4年前
mysql 库简介与操作
mysql系统库information\_schema：虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等performance\_schema：MySQL5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象mys
877
0
0
Wesley13 Wesley13
•
4年前
MongoDB增删改查
MongoDB增删改查查询数据库:showdbs进入数据库:useMYDB查询数据库中表:showtables在mongo中创建新的用户并赋予相应权限:db.createUser({user:<name_string,
1024
0
0
Wesley13 Wesley13
•
4年前
MySQL 基本语法
MySQL基本语法\TOC\MySQL本身自带4张表：information\_schema：数据库又称为信息架构，数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。performance\_schema：数据库主要用于收集数据库服务器性能参数，
1205
0
0
Wesley13 Wesley13
•
4年前
Mysql用户与权限操作
1.用户与权限概述用户是数据库的使用者和管理者。MySQL通过用户的设置来控制数据库操作人员的访问与操作范围。服务器中名为mysqI的数据库，用于维护数据库的用户以及权限的控制和管理。MySQL中的所有用户信息都保存在mysql.user数据表中。根据my
1097
0
0
Wesley13 Wesley13
•
4年前
MySQL 8.0.11 中使用 grant ... identified by 时 error 1064 near 'identified by '密码'' a...
(1)问题:当使用 grant权限列表on数据库to'用户名'@'访问主机'identifiedby'密码';时会出现"......near'identifiedby'密码''atline1"这个错误(2)原因:因为新版的的mysql版本已经将创建账户和赋予权限的方式分开了
810
0
0
Wesley13 Wesley13
•
4年前
mysql 权限管理，一个非db管理员创建mysql的做法
安装了mysql，建了数据库，需要几个账号，进行权限管理，现总结如下一个建了四个账号。1.    root账号，这是安装数据库时设置的，假设账号密码如下：root passwd此账号具有管理数据库的最高权限，可以创建用户，建数据库，修改表等全部权限2.  第二个账户createpasswd此账户具有对特定数据库操作的全部
726
0
0
码林季风
码林季风
Lv1
不经一番寒彻骨，怎得梅花扑鼻香。
文章
7
粉丝
0
获赞
0
热门文章
领域驱动设计 (DDD) 的思考
Redux系列源码解读
Tomcat 8熵池阻塞变慢详解
RBAC | 使用authing实现基于角色的访问控制
【数据库数据恢复】SQL Server数据库系统表损坏的数据恢复案例