如何限制普通用户查看函数定义的权限（函数中有密钥信息）

OceanBase 技术问题
#1

【 使用环境 】
生产环境
【 OB or 其他组件 】
OB
【 使用版本 】
4.3.3.1 MySQL
【问题描述】清晰明确描述问题
使用root用户建立了一个自定义函数，如何才能使普通用户看不到函数的定义

【复现路径】问题出现前后相关操作
使用root账号，创建函数（举例说明）：
obclient(root@obmysql)[lfq]> show create function lfq.my_func\G;
*************************** 1. row ***************************
Function: my_func
sql_mode: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER
Create Function: CREATE DEFINER = root@% FUNCTION my_func(c1 CHAR(20)) RETURNS char(50)
DETERMINISTIC
RETURN CONCAT(‘Thank ‘,c1,’!’)
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.025 sec)

root账号权限信息：
obclient(root@obmysql)[lfq]> show grants;
±-------------------------------------------------------------------+
| Grants for root@% |
±-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON oceanbase.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON __recyclebin.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON mysql.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON test.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON information_schema.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON __public.* TO ‘root’ |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION lfq.my_func TO ‘root’ |
±-------------------------------------------------------------------+
8 rows in set (0.008 sec)

普通用户lfq的权限信息：
obclient(lfq@obmysql)[lfq]> show grants;
±---------------------------------------------------------+
| Grants for lfq@% |
±---------------------------------------------------------+
| GRANT USAGE ON . TO ‘lfq’ |
| GRANT DELETE, INSERT, UPDATE, SELECT ON lfq.* TO ‘lfq’ |
±---------------------------------------------------------+
2 rows in set (0.263 sec)

普通用户执行函数（无权限，符合预期）
obclient(lfq@obmysql)[lfq]> select lfq.my_func(“123”);
ERROR 1370 (42000): EXECUTE command denied to user ‘lfq’@’%’ for routine ‘my_func’

想要的效果（不允许普通用户查到函数定义，以下最好都禁用）：

image
image1527×337 18.1 KB

image
image2514×324 12.6 KB

image
image2514×569 28.9 KB

尝试用这个来撤销，普通用户查看函数定义的权限，以下两种方式均报错：
obclient(root@obmysql)[lfq]> REVOKE SHOW_ROUTINE FROM lfq;
ERROR 3523 (HY000): Unknown authorization ID SHOW_ROUTINE@%

obclient(root@obmysql)[lfq]> REVOKE SHOW_ROUTINE on lfq.* from lfq;
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use

【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

1 个赞
#3

根据您的问题描述和提供的信息，您希望限制普通用户查看函数定义的权限。以下是对问题的分析和解决方案：

问题分析

  1. 当前情况：

    • 您使用 root 用户创建了一个自定义函数 my_func。
    • 普通用户 lfq 无法执行该函数（符合预期），但可以查看函数的定义（通过 SHOW CREATE FUNCTION 或查询 information_schema.routines 表）。

  2. 目标：

    • 防止普通用户查看函数的定义，包括通过以下方式：
      • SHOW CREATE FUNCTION
      • SHOW FUNCTION STATUS
      • 查询 information_schema.routines 表

  3. 尝试解决：

    • 您尝试通过 REVOKE SHOW_ROUTINE 来撤销权限，但报错提示语法错误或未知授权 ID。

  4. 问题原因：

    • 在 OceanBase 数据库中，SHOW ROUTINE 并不是一个独立的权限，而是依赖于对 information_schema.routines 表的访问权限。
    • 如果普通用户拥有 SELECT 权限，则可以通过查询 information_schema.routines 表间接获取函数定义。

解决方案

1. 禁止普通用户访问 information_schema.routines 表

  • 默认情况下，普通用户可能具有对 information_schema 数据库的隐式访问权限。要限制普通用户查看函数定义，可以显式地撤销其对 information_schema.routines 表的访问权限。
REVOKE SELECT ON information_schema.routines FROM 'lfq'@'%';

注意：此操作仅针对特定用户 lfq，不会影响其他用户的权限。

2. 使用 DEFINER 属性保护函数定义

  • 当您创建函数时，指定 DEFINER = 'root'@'%'，这意味着只有 root 用户及其授权的用户才能查看和修改函数定义。
  • 普通用户即使有权限查询 information_schema.routines 表，也无法看到函数的具体定义内容。

示例：

CREATE DEFINER = 'root'@'%' FUNCTION my_func(c1 CHAR(20)) RETURNS char(50)
DETERMINISTIC
RETURN CONCAT('Thank ', c1, '!');

3. 撤销不必要的权限

  • 确保普通用户 lfq 没有对 information_schema 数据库的任何权限。可以通过以下命令检查并撤销权限：
    SHOW GRANTS FOR 'lfq'@'%';
REVOKE ALL PRIVILEGES ON information_schema.* FROM 'lfq'@'%';

4. 验证效果

  • 执行以下命令验证普通用户是否仍然能够查看函数定义：
    -- 普通用户登录后执行
SHOW CREATE FUNCTION lfq.my_func;
SHOW FUNCTION STATUS WHERE Db = 'lfq';
SELECT * FROM information_schema.routines WHERE ROUTINE_SCHEMA = 'lfq';
  • 如果正确配置了权限，上述命令应返回空结果或权限不足的错误。

注意事项

  1. 权限管理原则：

    • 遵循最小权限原则，仅授予用户完成任务所需的最低权限。
    • 避免为普通用户授予对系统表（如 information_schema）的访问权限。

  2. 版本兼容性：

    • 您使用的版本为 4.3.3.1 MySQL，上述方法适用于该版本。如果升级到更高版本，请重新验证权限行为。

  3. 潜在风险：

    • 如果完全禁止普通用户访问 information_schema 数据库，可能会影响某些工具或应用程序的功能。在实施前，请确保评估相关影响。

总结

通过以上步骤，您可以有效限制普通用户查看函数定义的权限。核心在于：

  • 撤销对 information_schema.routines 表的访问权限。
  • 利用 DEFINER 属性保护函数定义。
  • 遵循最小权限原则进行权限管理。

如果您仍有疑问或需要进一步帮助，请随时联系 OceanBase 的人工答疑团队。

详情请参考:

  1. 函数索引不支持的系统函数列表
  2. 用户自定义变量
  3. 创建表组
  4. PARAMETER
  5. CREATE DATABASE
  6. SET PASSWORD
  7. MIGRATE REPLICA
  8. REMOVE REPLICA
  9. MODIFY REPLICA
  10. CANCEL REPLICA TASK
1 个赞