MySQL案例02：ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGES_尧舜禹

http://blog.sina.com.cn/u/1241980632

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

MySQL案例02：ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGES

(2018-09-26 11:22:03)

分类：数据库类文章

MySQL在授权用户时出现报错信息，具体信息如下：

一、错误信息

执行命令：

GRANT SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON DBTEST.* TO 'dbtest'@'%'

错误信息：

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

二、错误原因

根据错误提示，可以发现一些授权时全局的权限，针对的是*.*，而不能单独对某个库、表授权

三、解决方法

将全局权限单独授予，拆分进行用户授权

GRANT SELECT,INSERT, UPDATE, DELETE, CREATE, DROP ON DBTEST.* TO 'dbtest'@'%' ;

GRANT FILE *.* TO DBTEST@'%';

四、MySQL权限附录

Permissible Privileges for GRANT and REVOKE

Privilege	Column	Context
`ALL [PRIVILEGES]`	Synonym for “all privileges”	Server administration
`ALTER`	`Alter_priv`	Tables
`ALTER ROUTINE`	`Alter_routine_priv`	Stored routines
`CREATE`	`Create_priv`	Databases, tables, or indexes
`CREATE ROUTINE`	`Create_routine_priv`	Stored routines
`CREATE TABLESPACE`	`Create_tablespace_priv`	Server administration
`CREATE TEMPORARY TABLES`	`Create_tmp_table_priv`	Tables
`CREATE USER`	`Create_user_priv`	Server administration
`CREATE VIEW`	`Create_view_priv`	Views
`DELETE`	`Delete_priv`	Tables
`DROP`	`Drop_priv`	Databases, tables, or views
`EVENT`	`Event_priv`	Databases
`EXECUTE`	`Execute_priv`	Stored routines
`FILE`	`File_priv`	File access on server host
`GRANT OPTION`	`Grant_priv`	Databases, tables, or stored routines
`INDEX`	`Index_priv`	Tables
`INSERT`	`Insert_priv`	Tables or columns
`LOCK TABLES`	`Lock_tables_priv`	Databases
`PROCESS`	`Process_priv`	Server administration
`PROXY`	See `proxies_priv` table	Server administration
`REFERENCES`	`References_priv`	Databases or tables
`RELOAD`	`Reload_priv`	Server administration
`REPLICATION CLIENT`	`Repl_client_priv`	Server administration
`REPLICATION SLAVE`	`Repl_slave_priv`	Server administration
`SELECT`	`Select_priv`	Tables or columns
`SHOW DATABASES`	`Show_db_priv`	Server administration
`SHOW VIEW`	`Show_view_priv`	Views
`SHUTDOWN`	`Shutdown_priv`	Server administration
`SUPER`	`Super_priv`	Server administration
`TRIGGER`	`Trigger_priv`	Tables
`UPDATE`	`Update_priv`	Tables or columns
`USAGE`	Synonym for “no privileges”	Server administration

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：MySQL测试工具sysbench

后一篇：SVN设置提交忽略某些文件或文件夹

新浪BLOG意见反馈留言板　欢迎批评指正