查看自己的权限:show grants
查看用户:select host,user,password from mysql.user
授权:GRANT privileges ON databasename.tablename TO 'username'@'%' (此处privileges为权限名)
授所有权限:GRANT all privileges ON databasename.tablename TO 'username'@'%'(此处privileges为实际字符,可省略)
授权并有授权权,可转授其他人:GRANT privileges ON databasename.tablename TO 'username'@'%' WITH GRANT OPTION;
查看使用某表的所有进程:select * from information_schema.processlist where INFO like '%acct_info%';
授予全部表全部权限:grant all privileges on *.* to 'user'@'%' with grant option;
授权之后需要 flush privileges;
用户信息在mysql.user,库权限信息在mysql.Db,表权限信息在mysql.tables_priv,表权限也可以在information_schame.SCHEMA_PRIVILEGES中获取
授予all privileges后需要撤销,可以使用revoke all privileges on *.* from 'xzq'@'%',之后账号权限回到原来的状态,不是全部消失
授予flush privileges的权限:grant reload on *.* to 'xingzhiqiang'@'%';
===问题==============================================
1、明明配置了权限,show grants显示也有权限,但是却无法使用,结果发现是之前权限配置有误,导致show grants中出现了两种配置
grant select on zhixiao_acct.* to 'xzq'@'%'
grant select on `zhixiao\_acct`.* to 'xzq'@'%'
此时再grant insert on zhixiao_acct.*将可以正常执行,但无法生效,解决方法是将zhixiao\_acct中的权限全部删除,使show grants中只有zhixiao_acct的权限