小菜鸟

java菜鸟号正在起航

0%

MySQL权限

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 11k 阅读时长 ≈ 10 分钟

MySQL权限

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

表结构

user表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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表

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

1
2
3
4
5
6
7
8
9
10
11
12
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表

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

1
2
3
4
5
6
7
8
9
10
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表

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

1
2
3
4
5
6
7
8
9
10
11
12
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可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用

设置用户密码

查看密码过期时间

1
show variables like 'default_password_lifetime';

创建用户、密码

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

修改密码

1
2
3
4
5
6
7
8
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;命令，因为在修改系统表的同时也会更新内存中的信息

授权

1
GRANT SELECT,INSERT,UPDATE ON *.* TO  'myuser'@'localhost';
相关文章

欢迎关注我的其它发布渠道