MySql 权限管理
程序员文章站
2022-05-31 18:38:22
...
MySql身份认证是通过IP地址和用户名联合认证的，如[email protected]表示用户root只能从本地(localhost)进行连接才能通过认证，也就是说，同一个用户名，如果来自不同的IP，MySql将其视为不同的用户。
在权限存取的过程中，系统会用到mysql库的user、host、和db这3个重要的权限表，user表最重要，host表大多数情况不并不使用到，user表中主要分为4个部分，用户列、权限列、安全列、资源控制列，user表定义如下：
CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_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',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_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',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_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',
`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',
`Repl_client_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',
`Create_user_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',
`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',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' ；
当用户进行连接的时候，权限表的存取过程有以下两个阶段：
*先从user表中的host、user、和password这3个字段中判断用户连接的IP、用户名和密码是否存在于表中，如果存在则通过验证，否则不通过；
*如果通过身份验证，则按照以下权限表的顺序得到用户的权限：user->db->tables_priv->columns_priv。
如创建用户[email protected]，并赋予所有数据库上的所有表的select权限：
mysql> GRANT SELECT ON *.* TO [email protected];
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM user WHERE user='test' AND host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password:
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)在查看db表：
mysql> SELECT * FROM db \G;并没有发现db表中没有刚才操作的记录，而user表中的select_priv列为Y，说明对所有数据库都据有相同的权限的用户记录并不记录到db表中，也就是说user表中的每个权限都代表对所有数据库都有的权限。
将[email protected]上的权限改为只对db1数据库上的所有表具有select权限：
mysql> REVOKE SELECT ON *.* FROM [email protected];
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON db1.* TO [email protected];
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM user WHERE user='test' AND host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT * FROM db WHERE Db= 'db1' \G;
*************************** 1. row ***************************
Host: localhost
Db: db1
User: test
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)发现user表中的select_priv为N，而db表中新增了Db为db1的一条记录，也就是说，当只授予部分数据库某些权限的时候，user表中相应的权限变为N，而具体的数据库权限写入到db表中。
从上面发现，当用户通过权限认证后进行分配权限时，按照user->db->tables_priv->columns_priv的顺序进行分配，也就是先检查全局权限表user，如果相应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db、tables_priv、cloumns_priv表；如果为N，则到db表中检查此用户对应的具体数据库，并得到db表中为Y的权限，如果db表中的权限为N，则检查tables_priv表，取得表中为Y的权限，如果表tables_priv中相应的权限为N，则检查columns_priv中为Y的权限。