关于mes系统中report帐号的产生过程

zhgc

首先，创建report帐号

1. CREATE USER 'report'@'%' IDENTIFIED BY 'report' ;

复制代码

1. 比如看板，只执行一条SP, 如何只附一个SP的权限

用海豚付权限后，反查权限表，得如下数据：

show grants for report@'%';


GRANT USAGE ON PROCEDURE `sfc`.`check_dup_kp` TO 'report'@'%' WITH GRANT OPTION   （测试只沟了grant）
GRANT EXECUTE ON PROCEDURE `sfc`.`check_emp` TO 'report'@'%'   (测试只沟了execte)
GRANT ALTER ROUTINE ON PROCEDURE `sfc`.`check_ec` TO 'report'@'%'  (测试只沟了alter)



于是得到方法， 安徽mes数据库中验证：
CREATE USER 'report'@'%' IDENTIFIED BY 'report' ;
GRANT EXECUTE ON PROCEDURE `sfc`.`KANBAN_01_XCAH` TO 'report'@'%'
FLUSH PRIVILEGES;
CALL sfc.KANBAN_01_XCAH('L1')



可以看到唯一的SP，也可以执行：

zhgc

第一层权限的对应：

1. show grants for report@'%';
   
2. --all--
   
3. GRANT
   
4. SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER
   
5. ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F' WITH GRANT OPTION
   
6. 4.0.2
   
7. GRANT
   
8. SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT
   
9. ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F'
   
10. 5.0
    
11. GRANT
    
12. CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER
    
13. ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F'
    
14. 5.1
    
15. GRANT EVENT, TRIGGER ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F'

复制代码

zhgc

二层权限：
GRANT ALL PRIVILEGES ON `smt`.* TO 'report'@'%' WITH GRANT OPTION

GRANT
SELECT,INSERT, UPDATE, DELETE,
CREATE, DROP, REFERENCES, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
ON `smt`.* TO 'report'@'%' WITH GRANT OPTION

zhgc

三层权限

GRANT ALL PRIVILEGES ON `sfc`.`b_po_config_t` TO 'report'@'%' WITH GRANT OPTION
GRANT
SELECT,INSERT, UPDATE, DELETE,
CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER
ON `sfc`.`b_pack_param_t` TO 'report'@'%' WITH GRANT OPTION

SP:
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sfc`.`ap_jz_change` TO 'report'@'%' WITH GRANT OPTION

zhgc

后来，发现又不行了：

1. 1 queries executed, 0 success, 1 errors, 0 warnings
   
2. 
   
3. 查询：CALL sfc.KANBAN_01_XCAH('3A')
   
4. 
   
5. 错误代码： 1370
   
6. execute command denied to user 'report'@'%' for routine 'sfc.KANBAN_01_XCAH'

复制代码

1.SHOW GRANTS FOR report@'%';
得到

1. GRANT CREATE TEMPORARY TABLES ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F'
   
2. GRANT SELECT ON `sfc`.* TO 'report'@'%'

复制代码

2.添加
GRANT EXECUTE ON PROCEDURE `sfc`.`KANBAN_01_XCAH` TO 'report'@'%'
FLUSH PRIVILEGES;

zhgc

usage
连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;
该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

zhgc

2021/5/28 看到的权限数据：


SHOW GRANTS FOR report@'%';
GRANT CREATE TEMPORARY TABLES ON *.* TO 'report'@'%' IDENTIFIED BY PASSWORD '*8E2B5A8BF835E14935C5C7F3ADE1022CE13A371F';
GRANT SELECT ON `sfc`.* TO 'report'@'%';
GRANT EXECUTE ON PROCEDURE `sfc`.`kanban_01_xcah` TO 'report'@'%';
GRANT EXECUTE ON PROCEDURE `sfc`.`kanban_02_xcah` TO 'report'@'%';