create database db_xxx;
create user user_xxx@'%' identified by pwd_xxx;
grant all privileges on db_xxx.* to user_xxx@'%';
create user user_xxx_admin@'%' identified by pwd_xxx;
grant select,insert,update,delete,create on db_xxx.* to user_xxx_admin@'%';
create user user_xxx_ro@'%' identified by pwd_xxx;
GRANT USAGE ON db_xxx.* TO user_xxx_ro@'%' ;
grant select on db_xxx.* to user_xxx_ro@'%';
flush privileges;
查看user_xxx权限
show grants for 'user_xxx'@'%';
给user_xxx授予查看存储过程权限
grant select on mysql.proc to 'user_xxx'@'%';
grant 操作 MySQL 存储过程、函数 权限
grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;
总结:
1、查看存储过程权限:SELECT #是对mysql.proc表的权限;
2、执行存储过程权限:EXECUTE #是对指定数据库的权限;
3、创建存储过程权限:CREATE ROUTINE #是对指定数据库的权限;
4、修改存储过程权限:ALTER ROUTINE #是对指定数据库的中自己定义的存储过程;
5、修改别人定义的存储过程权限:SUPER #是对全局整个mysql的权限;