深圳全飞鸿

标题: 关于mes系统中report帐号的产生过程 [打印本页]
作者: zhgc    时间: 2019-11-29 15:17
标题: 关于mes系统中report帐号的产生过程

首先，创建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    时间: 2019-11-30 00:00
第一层权限的对应：



  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    时间: 2019-11-30 00:06
二层权限：
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    时间: 2019-11-30 00:11
三层权限

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    时间: 2019-12-23 16:40
后来，发现又不行了：
  1. 1 queries executed, 0 success, 1 errors, 0 warnings

  3. 查询：CALL sfc.KANBAN_01_XCAH('3A')

  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    时间: 2020-6-23 21:37
usage
连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;
该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
作者: zhgc    时间: 2021-5-28 15:10
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'@'%';



欢迎光临 深圳全飞鸿 (http://www.nagomes.com/disc/) Powered by Discuz! X3.2