Home >Database >Mysql Tutorial >MySQL 用户权限详细汇总
mysql 的权限体系大致分为5个层级:
全局层级:
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
数据库层级:
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。
表层级:
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级:
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时,您必须指定与被授权列相同的列。
子程序层级:
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予为子程序层级,并存储在mysql.procs_priv表中。
这些权限信息存储在下面的系统表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
mysql. procs_priv
当用户连接进来,mysqld会通过上面的这些表对用户权限进行验证!
相对于oracle来说,mysql的特性是可以限制ip,用户user、ip地址host、密码passwd这3个是用户管理的基础,权限的细节基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv这几张表就可以看到很多细节,接下来仔细分析这些表就可以知道权限的奥秘。
5121c5d8c817ad4c086d9118458eb6a9
原博客地址: http://www.php.cn/
原作者:黄杉 (mchdba)
演示过程中需要建立用户来演示,先简单介绍下如何创建用户:
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例:
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
mysql> desc mysql.db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.02 sec) mysql>
db表存储了所有对一个数据库的所有操作权限。创建用户的时候,都会往Host字段,User字段,Password字段录入用户信息;
而当执行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的授权语句的话,Select_priv和Insert_priv字段的值会变成Y其它字段仍然是N;
当你执行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的复制语句的话,后面的字段都会变成Y的值;
创建用户:
GRANT SELECT,INSERT ON d3307.* TO user4@'192.168.52' IDENTIFIED BY 'user0523';
应该除了Host、db、user字段有值,除了Select_priv、Insert_priv值为Y外,其它的都是N。
查看mysql.db表的记录正是如此,如下所示:
mysql> SELECT * FROM mysql.`db` where user='user4'\G; *************************** 1. row *************************** Host: 192.168.52 Db: d3307 User: user4 Select_priv: Y Insert_priv: Y Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_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: N1 row in set (0.01 sec) ERROR: No query specified mysql>
执行sql语句建立用户:
GRANT ALL ON d3307.* TO dba5@'192.168.52.1' IDENTIFIED BY 'dba0523';
建立用户的时候,如下所示,除了Host、db、user字段外,所有的*_priv字段记录都会变成Y值,(Grant_priv仍然是N值除非加了WITH* GRANT OPTION执行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ;)
如下所示:
mysql> SELECT * FROM mysql.`db` where user='dba5'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: dba5 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y1 row in set (0.00 sec) ERROR: No query specified mysql>
mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43 rows in set (0.10 sec) mysql>
存储用户记录的表,存储了用户的信息,每一次创建用户的时候,都会往这个表里录入记录,当你执行了,都会往Host字段,User字段,Password字段录入数据,但是后面的Select_priv、Insert_priv、Update_priv等字段的值,只有赋予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;类似的对所有库的操作权限的时候才会被记录成Y,否则都记录成N。
创建用户:
GRANT SELECT,UPDATE ON d3307.* TO user6@'192.168.52.1' IDENTIFIED BY 'user0523';
分析结果:存储在mysql.user表里面的记录当中,Host、User、Password是有值的,但是其它的Select_priv等*_priv字段值都是N。
验证结果,去查看表里的存储记录,如下所示:
mysql> SELECT * FROM mysql.user where user='user6'\G; *************************** 1. row *************************** Host: 192.168.52.1 User: user6 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A 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: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N1 row in set (0.00 sec) ERROR: No query specified mysql>
创建用户:
mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%' IDENTIFIED BY 'user0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析:
基本的Host、User、Password字段有记录值,然后grant了select和update所以关于*_priv字段中select和update字段有值为Y,其它*_priv字段值应该是N。
查看记录结果,分享正确,如下所示:
mysql> SELECT * FROM mysql.user where user='user7'\G; *************************** 1. row *************************** Host: % User: user7 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A Select_priv: Y Insert_priv: N Update_priv: Y 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: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N1 row in set (0.00 sec) ERROR: No query specified mysql>
mysql> desc mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql>
记录了对一个表的单独授权记录,只有执行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;类似的授权记录才会在这个表里录入授权信息;其中各个字段涵义如下:
字段 | 存储的数据 |
---|---|
Host字段 | 用户的登录ip范围 |
User字段 | 表所在的数据库名称 |
Table_name字段 | 授权的表的名称 |
Grantor字段 | 执行grant建立用户的授权者 |
Timestamp字段 | 0000-00-00 00:00:00 |
Table_priv字段 | 所授予的操作表的权限,比如select、udate、delete等 |
Column_priv字段 | 对这个表的某个字段单独授予的权限 |
另外当赋予all在某张表上的时候,Table_priv列会多处所有关于表的授权记录,描述如下:
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
创建用户:
mysql> GRANT INSERT,SELECT,UPDATE ON d3307.t TO user8@'192.168.52.1' IDENTIFIED BY 'dba0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析结果:
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的,但是Column_priv没有值,因为没有单独对某一个列做了授权限制的。
查看权限,如下所示:
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: 1 row in set (0.00 sec) ERROR: No query specified mysql>
授权语句操作:
mysql> GRANT UPDATE(created_time) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT(uname) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql>
分析:
单独为某个列授权,会记录在这个表的Column_priv字段里面,会记录下对单个列的授权操作记录
查看记录:
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: Select,Update 1 row in set (0.00 sec) ERROR: No query specified mysql>
而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录,如下所示:
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+--------------+---------------------+-------------+| 192.168.52.1 | d3307 | user8 | t | created_time | 0000-00-00 00:00:00 | Update | | 192.168.52.1 | d3307 | user8 | t | uname | 0000-00-00 00:00:00 | Select | +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2 rows in set (0.00 sec)mysql>
mysql> desc mysql.columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec) mysql>
单独对某一列有操作权限的时候,会将权限信息记录在这个表里面,比如新建立一个账号GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就会在这个表上录入授权信息记录,重点看Column_name字段和Column_priv字段的值。
创建用户操作:
mysql> GRANT UPDATE(uname) ON d3307.t TO user9@'192.168.52.%' IDENTIFIED BY 'user0520'; Query OK, 0 rows affected (0.00 sec) mysql>
查看结果,会在这个columns_priv表留下一条记录:
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user9'; +--------------+-------+-------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+-------------+---------------------+-------------+ | 192.168.52.% | d3307 | user9 | t | uname | 0000-00-00 00:00:00 | Update | +--------------+-------+-------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec) mysql>
mysql> desc proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Proxied_host | char(60) | NO | PRI | | | | Proxied_user | char(16) | NO | PRI | | | | With_grant | tinyint(1) | NO | | 0 | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec)mysql>
procs_priv表可以对存储过程和存储函数进行权限设置。主要字段:proc_priv。
使用CREATE USER语句创建用户,必须要拥有CREATE USER权限。其格式如下:
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'], [user[IDENTIFIED BY [PASSWORD] 'password']]...
其中,user参数表示新建用户的账户,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码;password参数表示用户的密码;如果密码是一个普通的字符串,就不需要使用PASSWORD关键字。可以没有初始密码。
例如
CREATE USER 'sys'@'%' IDENTIFIED BY 'sys';
执行之后user表会增加一行记录,但权限暂时全部为‘N’。
可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
另外,ssl_cipher、x509_issuer、x509_subject等必须要设置值,否则INSERT语句无法执行。
示例:
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
执行INSERT之后,要使用命令:FLUSH PRIVILEGES;命令来使用户生效。
用GRANT来创建新的用户时,能够在创建用户时为用户授权。但需要拥有GRANT权限。
语法如下:
GRANT priv_type ON database.table TO user[IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']...]
priv_type:参数表示新yoghurt的权限;
databse.table:参数表示新用户的权限范围;
user:参数新用户的账户,由用户名和主机构成;
IDENTIFIED BY关键字用来设置密码;
password:新用户密码;
PS:GRANT语句可以同时创建多个用户。.与db.*的区别在于。.对所有数据库生效,所以user表的SELECT会变为Y。而db.*user表为’N’,更改的是Db表。
DROP USER语句删除普通用户,需要拥有DROP USER权限。
语法如下:
DROP USER user[,user]...
user是需要删除的用户,由用户名(User)和主机名(Host)构成。
可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 删除完成后,一样要FLUSH PRIVILEGES才生效。
语法:
mysqladmin -u -username -p password "new_password"
新密码(new_password)必须用括号括起来,单引号会报错。
示例,修改中要输入旧的密码来验证:
[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sock Enter password: [root@data02 ~]# [root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";+--------+| @@port | +--------+| 3307 | +--------+[root@data02 ~]#
UPDATE user表的passwor字段的值,也可以达到修改密码的目的;
UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser';FLUSH PRIVILEGES;
刷新后生效。
使用root用户登录到MySQL服务器后,可以使用SET语句来修改密码:
修改自己的密码,不需要用户名
SET PASSWORD = PASSWORD("123");
修改其他用户密码:
SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456") FOR 用户名@主机名
使用GRANT语句修改普通用户的密码,必须拥有GRANT权限。
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
示例:
GRANT SELECT ON *.* TO 'user10'@'%' IDENTIFIED BY '123'
普通用户,直接用root超级管理员登录进去修改密码就可以了,但是如果root密码丢失了,怎么办呢?
停止mysql:service mysqld stop;
安全模式启动:mysqld_safe –skip-grant-tables &
无密码回车键登录:mysql -uroot –p
重置密码:use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
正常启动:service mysql restart
再使用mysqladmin: mysqladmin password ‘123456’
–>(1):有一个修改test库的用户:grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2):复制user表文件到test库下并且赋予mysql用户访问权限:
cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3):mysql -utest -pt1登录修改root密码:
–>(4):将test库的user表文件覆盖 mysql库的user表文件
cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql进程号,并且发送SIGHUP信号,重新加载权限表。
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):无密码登录,再使用mysqladmin重新设置密码。
PS:请参考第20课的视频,那里有详细的记录整个过修改密码的过程。
查看权限:
SHOW GRANTS; SHOW GRANTS FOR user10@'%';
或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限:
SELECT * FROM mysql.user WHERE USER='user10' AND HOST='%';
使用revoke关键字来收回权限:
REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]
示例:
REVOKE EXECUTE ON d3307.* FROM user10@'%';
赋予对业务表的查询维护权限即可,授权sql如下:
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@'%' IDENTIFIED BY '0523';
赋予增删改查的权限,授权sql如下:
GRANT SELECT,INSERT,DELETE,UPDATE ON d3307.* TO huyan@'%' IDENTIFIED BY '0523';
授予创建、修改、删除 MySQL 数据表结构权限。
GRANT CREATE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ON d3307.* TO huyan@’192.168.52.11’;GRANT DROP ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 外键权限:
GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 临时表权限:
GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 索引权限:
GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 视图、查看视图源代码 权限:
GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;GRANT SHOW VIEW ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 存储过程、函数 权限:
GRANT CREATE ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT EXECUTE ON d3307.* TO huyan@’192.168.52.11’;
授予普通DBA管理某个MySQL数据库(test)的权限:
GRANT ALL PRIVILEGES ON test TO sysdba@'192.168.52.%';
授予高级 DBA 管理 MySQL 中所有数据库的权限:
GRANT ALL ON *.* TO sysdba@'192.168.52.%';
只需要分配只读的权限:
GRANT SELECT ON d3307.* TO dataquery@'192.168.52.129' IDENTIFIED BY '20150523';
甚至有些用户,可以只分配读取某些表列的权限,如下所示:
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列权限登录操作:
[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL CONNECTION id IS 18SERVER VERSION: 5.6.12-LOG Source distribution Copyright (c) 2000, 2013, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark of Oracle Corporation AND/OR its affiliates. Other NAMES may be trademarks of their respective owners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the current input statement. mysql> SELECT * FROM d3307.t; ERROR 1142 (42000): SELECT command denied TO USER 'dataquery'@'data02' FOR TABLE 't' mysql> mysql> SELECT id,uname FROM d3307.t; +----+-------+| id | uname | +----+-------+| 1 | a | +----+-------+1 ROW IN SET (0.00 sec) mysql>
数据库一般划分为线上库,测试库,开发库。
DBA:有所有权限,超级管理员权限
应用程序:分配insert、delete、update、select、execute、events、jobs权限。
测试人员:select某些业务表权限
开发人员:select某些业务表权限
原则:所有对线上表的操作,除了应用程序之外,都必须经由DBA来决定是否执行、已经什么时候执行等。
DBA:所有权限。
测试人员:有insert、delete、update、select、execute、jobs权限。
数据分析人员:只有select查询权限
开发人员:有select权限。
原则:DBA有所有权限,而且严格控制表结构的变更,不允许除了dba之外的人对测试环境的库环境进行修改,以免影响测试人员测试。所有对测试库的表结构进行的修改必须由测试人员和DBA一起审核过后才能操作。
DBA:所有权限
测试人员:有库表结构以及数据的所有操作权限。
开发人员:有库表结构以及数据的所有操作权限。
数据分析人员:有库表结构以及数据的所有操作权限。
这里大家可以愉快的玩耍了,只要不mysql服务不hang不downtime都OK了。
参考文章资料:
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
mysql 的权限体系大致分为5个层级:
全局层级:
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
数据库层级:
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。
表层级:
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级:
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时,您必须指定与被授权列相同的列。
子程序层级:
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予为子程序层级,并存储在mysql.procs_priv表中。
这些权限信息存储在下面的系统表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
mysql. procs_priv
当用户连接进来,mysqld会通过上面的这些表对用户权限进行验证!
相对于oracle来说,mysql的特性是可以限制ip,用户user、ip地址host、密码passwd这3个是用户管理的基础,权限的细节基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv这几张表就可以看到很多细节,接下来仔细分析这些表就可以知道权限的奥秘。
5121c5d8c817ad4c086d9118458eb6a9
原博客地址: http://www.php.cn/
原作者:黄杉 (mchdba)
演示过程中需要建立用户来演示,先简单介绍下如何创建用户:
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例:
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
mysql> desc mysql.db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.02 sec) mysql>
db表存储了所有对一个数据库的所有操作权限。创建用户的时候,都会往Host字段,User字段,Password字段录入用户信息;
而当执行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的授权语句的话,Select_priv和Insert_priv字段的值会变成Y其它字段仍然是N;
当你执行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的复制语句的话,后面的字段都会变成Y的值;
创建用户:
GRANT SELECT,INSERT ON d3307.* TO user4@'192.168.52' IDENTIFIED BY 'user0523';
应该除了Host、db、user字段有值,除了Select_priv、Insert_priv值为Y外,其它的都是N。
查看mysql.db表的记录正是如此,如下所示:
mysql> SELECT * FROM mysql.`db` where user='user4'\G; *************************** 1. row *************************** Host: 192.168.52 Db: d3307 User: user4 Select_priv: Y Insert_priv: Y Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_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: N1 row in set (0.01 sec) ERROR: No query specified mysql>
执行sql语句建立用户:
GRANT ALL ON d3307.* TO dba5@'192.168.52.1' IDENTIFIED BY 'dba0523';
建立用户的时候,如下所示,除了Host、db、user字段外,所有的*_priv字段记录都会变成Y值,(Grant_priv仍然是N值除非加了WITH* GRANT OPTION执行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ;)
如下所示:
mysql> SELECT * FROM mysql.`db` where user='dba5'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: dba5 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y1 row in set (0.00 sec) ERROR: No query specified mysql>
mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43 rows in set (0.10 sec) mysql>
存储用户记录的表,存储了用户的信息,每一次创建用户的时候,都会往这个表里录入记录,当你执行了,都会往Host字段,User字段,Password字段录入数据,但是后面的Select_priv、Insert_priv、Update_priv等字段的值,只有赋予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;类似的对所有库的操作权限的时候才会被记录成Y,否则都记录成N。
创建用户:
GRANT SELECT,UPDATE ON d3307.* TO user6@'192.168.52.1' IDENTIFIED BY 'user0523';
分析结果:存储在mysql.user表里面的记录当中,Host、User、Password是有值的,但是其它的Select_priv等*_priv字段值都是N。
验证结果,去查看表里的存储记录,如下所示:
mysql> SELECT * FROM mysql.user where user='user6'\G; *************************** 1. row *************************** Host: 192.168.52.1 User: user6 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A 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: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N1 row in set (0.00 sec) ERROR: No query specified mysql>
创建用户:
mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%' IDENTIFIED BY 'user0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析:
基本的Host、User、Password字段有记录值,然后grant了select和update所以关于*_priv字段中select和update字段有值为Y,其它*_priv字段值应该是N。
查看记录结果,分享正确,如下所示:
mysql> SELECT * FROM mysql.user where user='user7'\G; *************************** 1. row *************************** Host: % User: user7 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A Select_priv: Y Insert_priv: N Update_priv: Y 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: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N1 row in set (0.00 sec) ERROR: No query specified mysql>
mysql> desc mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql>
记录了对一个表的单独授权记录,只有执行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;类似的授权记录才会在这个表里录入授权信息;其中各个字段涵义如下:
字段 | 存储的数据 |
---|---|
Host字段 | 用户的登录ip范围 |
User字段 | 表所在的数据库名称 |
Table_name字段 | 授权的表的名称 |
Grantor字段 | 执行grant建立用户的授权者 |
Timestamp字段 | 0000-00-00 00:00:00 |
Table_priv字段 | 所授予的操作表的权限,比如select、udate、delete等 |
Column_priv字段 | 对这个表的某个字段单独授予的权限 |
另外当赋予all在某张表上的时候,Table_priv列会多处所有关于表的授权记录,描述如下:
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
创建用户:
mysql> GRANT INSERT,SELECT,UPDATE ON d3307.t TO user8@'192.168.52.1' IDENTIFIED BY 'dba0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析结果:
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的,但是Column_priv没有值,因为没有单独对某一个列做了授权限制的。
查看权限,如下所示:
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: 1 row in set (0.00 sec) ERROR: No query specified mysql>
授权语句操作:
mysql> GRANT UPDATE(created_time) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT(uname) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql>
分析:
单独为某个列授权,会记录在这个表的Column_priv字段里面,会记录下对单个列的授权操作记录
查看记录:
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: Select,Update 1 row in set (0.00 sec) ERROR: No query specified mysql>
而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录,如下所示:
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+--------------+---------------------+-------------+| 192.168.52.1 | d3307 | user8 | t | created_time | 0000-00-00 00:00:00 | Update | | 192.168.52.1 | d3307 | user8 | t | uname | 0000-00-00 00:00:00 | Select | +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2 rows in set (0.00 sec)mysql>
mysql> desc mysql.columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec) mysql>
单独对某一列有操作权限的时候,会将权限信息记录在这个表里面,比如新建立一个账号GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就会在这个表上录入授权信息记录,重点看Column_name字段和Column_priv字段的值。
创建用户操作:
mysql> GRANT UPDATE(uname) ON d3307.t TO user9@'192.168.52.%' IDENTIFIED BY 'user0520'; Query OK, 0 rows affected (0.00 sec) mysql>
查看结果,会在这个columns_priv表留下一条记录:
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user9'; +--------------+-------+-------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+-------------+---------------------+-------------+ | 192.168.52.% | d3307 | user9 | t | uname | 0000-00-00 00:00:00 | Update | +--------------+-------+-------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec) mysql>
mysql> desc proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Proxied_host | char(60) | NO | PRI | | | | Proxied_user | char(16) | NO | PRI | | | | With_grant | tinyint(1) | NO | | 0 | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec)mysql>
procs_priv表可以对存储过程和存储函数进行权限设置。主要字段:proc_priv。
使用CREATE USER语句创建用户,必须要拥有CREATE USER权限。其格式如下:
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'], [user[IDENTIFIED BY [PASSWORD] 'password']]...
其中,user参数表示新建用户的账户,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码;password参数表示用户的密码;如果密码是一个普通的字符串,就不需要使用PASSWORD关键字。可以没有初始密码。
例如
CREATE USER 'sys'@'%' IDENTIFIED BY 'sys';
执行之后user表会增加一行记录,但权限暂时全部为‘N’。
可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
另外,ssl_cipher、x509_issuer、x509_subject等必须要设置值,否则INSERT语句无法执行。
示例:
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
执行INSERT之后,要使用命令:FLUSH PRIVILEGES;命令来使用户生效。
用GRANT来创建新的用户时,能够在创建用户时为用户授权。但需要拥有GRANT权限。
语法如下:
GRANT priv_type ON database.table TO user[IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']...]
priv_type:参数表示新yoghurt的权限;
databse.table:参数表示新用户的权限范围;
user:参数新用户的账户,由用户名和主机构成;
IDENTIFIED BY关键字用来设置密码;
password:新用户密码;
PS:GRANT语句可以同时创建多个用户。.与db.*的区别在于。.对所有数据库生效,所以user表的SELECT会变为Y。而db.*user表为’N’,更改的是Db表。
DROP USER语句删除普通用户,需要拥有DROP USER权限。
语法如下:
DROP USER user[,user]...
user是需要删除的用户,由用户名(User)和主机名(Host)构成。
可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 删除完成后,一样要FLUSH PRIVILEGES才生效。
语法:
mysqladmin -u -username -p password "new_password"
新密码(new_password)必须用括号括起来,单引号会报错。
示例,修改中要输入旧的密码来验证:
[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sock Enter password: [root@data02 ~]# [root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";+--------+| @@port | +--------+| 3307 | +--------+[root@data02 ~]#
UPDATE user表的passwor字段的值,也可以达到修改密码的目的;
UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser';FLUSH PRIVILEGES;
刷新后生效。
使用root用户登录到MySQL服务器后,可以使用SET语句来修改密码:
修改自己的密码,不需要用户名
SET PASSWORD = PASSWORD("123");
修改其他用户密码:
SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456") FOR 用户名@主机名
使用GRANT语句修改普通用户的密码,必须拥有GRANT权限。
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
示例:
GRANT SELECT ON *.* TO 'user10'@'%' IDENTIFIED BY '123'
普通用户,直接用root超级管理员登录进去修改密码就可以了,但是如果root密码丢失了,怎么办呢?
停止mysql:service mysqld stop;
安全模式启动:mysqld_safe –skip-grant-tables &
无密码回车键登录:mysql -uroot –p
重置密码:use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
正常启动:service mysql restart
再使用mysqladmin: mysqladmin password ‘123456’
–>(1):有一个修改test库的用户:grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2):复制user表文件到test库下并且赋予mysql用户访问权限:
cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3):mysql -utest -pt1登录修改root密码:
–>(4):将test库的user表文件覆盖 mysql库的user表文件
cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql进程号,并且发送SIGHUP信号,重新加载权限表。
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):无密码登录,再使用mysqladmin重新设置密码。
PS:请参考第20课的视频,那里有详细的记录整个过修改密码的过程。
查看权限:
SHOW GRANTS; SHOW GRANTS FOR user10@'%';
或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限:
SELECT * FROM mysql.user WHERE USER='user10' AND HOST='%';
使用revoke关键字来收回权限:
REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]
示例:
REVOKE EXECUTE ON d3307.* FROM user10@'%';
赋予对业务表的查询维护权限即可,授权sql如下:
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@'%' IDENTIFIED BY '0523';
赋予增删改查的权限,授权sql如下:
GRANT SELECT,INSERT,DELETE,UPDATE ON d3307.* TO huyan@'%' IDENTIFIED BY '0523';
授予创建、修改、删除 MySQL 数据表结构权限。
GRANT CREATE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ON d3307.* TO huyan@’192.168.52.11’;GRANT DROP ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 外键权限:
GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 临时表权限:
GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 索引权限:
GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 视图、查看视图源代码 权限:
GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;GRANT SHOW VIEW ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 存储过程、函数 权限:
GRANT CREATE ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT EXECUTE ON d3307.* TO huyan@’192.168.52.11’;
授予普通DBA管理某个MySQL数据库(test)的权限:
GRANT ALL PRIVILEGES ON test TO sysdba@'192.168.52.%';
授予高级 DBA 管理 MySQL 中所有数据库的权限:
GRANT ALL ON *.* TO sysdba@'192.168.52.%';
只需要分配只读的权限:
GRANT SELECT ON d3307.* TO dataquery@'192.168.52.129' IDENTIFIED BY '20150523';
甚至有些用户,可以只分配读取某些表列的权限,如下所示:
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列权限登录操作:
[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL CONNECTION id IS 18SERVER VERSION: 5.6.12-LOG Source distribution Copyright (c) 2000, 2013, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark of Oracle Corporation AND/OR its affiliates. Other NAMES may be trademarks of their respective owners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the current input statement. mysql> SELECT * FROM d3307.t; ERROR 1142 (42000): SELECT command denied TO USER 'dataquery'@'data02' FOR TABLE 't' mysql> mysql> SELECT id,uname FROM d3307.t; +----+-------+| id | uname | +----+-------+| 1 | a | +----+-------+1 ROW IN SET (0.00 sec) mysql>
数据库一般划分为线上库,测试库,开发库。
DBA:有所有权限,超级管理员权限
应用程序:分配insert、delete、update、select、execute、events、jobs权限。
测试人员:select某些业务表权限
开发人员:select某些业务表权限
原则:所有对线上表的操作,除了应用程序之外,都必须经由DBA来决定是否执行、已经什么时候执行等。
DBA:所有权限。
测试人员:有insert、delete、update、select、execute、jobs权限。
数据分析人员:只有select查询权限
开发人员:有select权限。
原则:DBA有所有权限,而且严格控制表结构的变更,不允许除了dba之外的人对测试环境的库环境进行修改,以免影响测试人员测试。所有对测试库的表结构进行的修改必须由测试人员和DBA一起审核过后才能操作。
DBA:所有权限
测试人员:有库表结构以及数据的所有操作权限。
开发人员:有库表结构以及数据的所有操作权限。
数据分析人员:有库表结构以及数据的所有操作权限。
这里大家可以愉快的玩耍了,只要不mysql服务不hang不downtime都OK了。
以上就是MySQL 用户权限详细汇总的内容,更多相关内容请关注PHP中文网(www.php.cn)!