0%

MySQL权限

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
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';

修改密码

1
2
3
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('mypass');
GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypass';