关于MySQL绕过授予information_schema中对象时报ERROR 1044（4200）错误
这个问题是微信群中网友关于MySQL权限的讨论，有这么一个业务需求（下面是他的原话）：
因为MySQL的很多功能都依赖主键，我想用zabbix用户，来监控业务数据库的所有表，是否都建立了主键。
监控的语句是：
FROMinformation_schema.tablest1
LEFTOUTERJOINinformation_schema.table_constraintst2
ONt1.table_schema=t2.table_schema
ANDt1.table_name=t2.table_name
ANDt2.constraint_nameIN('PRIMARY')
WHEREt2.table_nameISNULL
ANDt1.table_schemaNOTIN('information_schema','myawr','mysql',
'performance_schema',
'slowlog','sys','test')
ANDt1.table_type='BASETABLE'
但是我不希望zabbix用户，能读取业务库的数据。一旦不给zabbix用户读取业务库数据的权限，那么information_schema.TABLES和information_schema.TABLE_CONSTRAINTS就不包含业务库的表信息了，也就统计不出来业务库的表是否有建主键。有没有什么办法，即让zabbix不能读取业务库数据，又能监控是否业务库的表没有建立主键？
首先，我们要知道一个事实：information_schema下的视图没法授权给某个用户。如下所示
mysql>GRANTSELECTONinformation_schema.TABLESTOtest@'%'; ERROR1044(42000):Accessdeniedforuser'root'@'localhost'todatabase'information_schema'
关于这个问题，可以参考mos上这篇文章：WhySettingPrivilegesonINFORMATION_SCHEMAdoesnotWork(文档ID1941558.1)
APPLIESTO:
MySQLServer-Version5.6andlater
Informationinthisdocumentappliestoanyplatform.
GOAL
TodeterminehowMySQLprivilegesworkforINFORMATION_SCHEMA.
SOLUTION
AsimpleGRANTstatementwouldbesomethinglike:
mysql>grantselect,executeoninformation_schema.*to'dbadm'@'localhost';
ERROR1044(42000):Accessdeniedforuser'root'@'localhost'todatabase'information_schema'
Theerrorindicatesthatthesuperuserdoesnothavetheprivilegestochangetheinformation_schemaaccessprivileges.
WhichseemstogoagainstwhatisnormallythecasefortherootaccountwhichhasSUPERprivileges.
Thereasonforthiserroristhattheinformation_schemadatabaseisactuallyavirtualdatabasethatisbuiltwhentheserviceisstarted.
Itismadeupoftablesandviewsdesignedtokeeptrackoftheservermeta-data,thatis,detailsofallthetables,proceduresetc.inthedatabaseserver.
Solookingspecificallyattheabovecommand,thereisanattempttoaddSELECTandEXECUTEprivilegestothisspecialiseddatabase.
TheSELECToptionisnotrequiredhowever,becauseallusershavetheabilitytoreadthetablesintheinformation_schemadatabase,sothisisredundant.
TheEXECUTEoptiondoesnotmakesense,becauseyouarenotallowedtocreateproceduresinthisspecialdatabase.
ThereisalsonocapabilitytomodifythetablesintermsofINSERT,UPDATE,DELETEetc.,soprivilegesarehardcodedinsteadofmanagedperuser.
那么怎么解决这个授权问题呢？直接授权不行，那么我们只能绕过这个问题，间接实现授权。思路如下：首先创建一个存储过程（用户数据库），此存储过程找出没有主键的表的数量，然后将其授予test用户。
DELIMITER//
CREATEDEFINER=`root`@`localhost`PROCEDURE`moitor_without_primarykey`()
BEGIN
SELECTCOUNT(*)
FROMinformation_schema.tablest1
LEFTOUTERJOINinformation_schema.table_constraintst2
ONt1.table_schema=t2.table_schema
ANDt1.table_name=t2.table_name
ANDt2.constraint_nameIN('PRIMARY')
WHEREt2.table_nameISNULL
ANDt1.table_schemaNOTIN('information_schema','myawr','mysql',
'performance_schema',
'slowlog','sys','test')
ANDt1.table_type='BASETABLE';
END//
DELIMITER;
mysql>GRANTEXECUTEONPROCEDUREmoitor_without_primarykeyTO'test'@'%';
QueryOK,0rowsaffected(0.02sec)
此时test就能间接的去查询information_schema下的对象了。
mysql>selectcurrent_user(); +----------------+ |current_user()| +----------------+ |test@%| +----------------+ 1rowinset(0.00sec) mysql>callmoitor_without_primarykey; +----------+ |COUNT(*)| +----------+ |6| +----------+ 1rowinset(0.02sec) QueryOK,0rowsaffected(0.02sec)
查看test用户的权限。
mysql>showgrantsfortest@'%'; +-------------------------------------------------------------------------------+ |Grantsfortest@%| +-------------------------------------------------------------------------------+ |GRANTUSAGEON*.*TO`test`@`%`| |GRANTEXECUTEONPROCEDURE`zabbix`.`moitor_without_primarykey`TO`test`@`%`| +-------------------------------------------------------------------------------+ 2rowsinset(0.00sec)
到此这篇关于关于MySQL绕过授予information_schema中对象时报ERROR1044（4200）错误的文章就介绍到这了,更多相关mysqlERROR1044（4200）内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！