首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
存储过程跨用户调用报错
更新时间：2023-12-07 03:16
分享
适用版本：V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x内容类型：Troubleshoot
适用版本
OceanBase 数据库所有版本
问题详情
存储过程跨用户调用（用户 A 向用户 B 某张表插入、查询数据报错）。
obclient [TEST2]> INSERT INTO TEST1.STD VALUES (1,90); ##用户test2向用户test1 std表中插入、查询数据是正常的
Query OK, 1 row affected (0.020 sec)
obclient [TEST2]> SELECT * FROM TEST1.STD;
+------+------+
| S_ID | S_GD |
+------+------+
| 1 | 90 |
+------+------+
1 row in set (0.015 sec)
现在创建存储过程（用户 test2 向用户 test1 std 表中插入、查询数据）。
obclient [TEST2]> DELIMITER //
obclient [TEST2]> create or replace PROCEDURE PRO1 (S1 IN INT ,C1 IN INT)
-> AS
-> BEGIN
-> INSERT INTO TEST1.STD(s_id,s_gd) VALUES (S1,C1);
-> END;
-> //
Query OK, 0 rows affected (0.094 sec)
obclient [TEST2]> CREATE OR REPLACE PROCEDURE PRO2 AS
END;
//
-> BEGIN
-> FOR rec IN (SELECT * FROM TEST1.STD) LOOP
-> DBMS_OUTPUT.PUT_LINE('s_id: ' || rec.s_id);
-> DBMS_OUTPUT.PUT_LINE('s_gd: ' || rec.s_gd);
-> DBMS_OUTPUT.PUT_LINE('---------------------------');
-> END LOOP;
-> END;
-> //
Query OK, 0 rows affected (0.032 sec)
可以看到调用存储过程报错，报错表不存在。
obclient [TEST2]> call pro1(2,91);
ORA-00942: table or view does not exist
at TEST2.PRO1 , line : 4, col : 1
obclient [TEST2]> call pro2();
ORA-00942: table or view does not exist
at TEST2.PRO2 , line : 3, col : 3
obclient [TEST2]>
解决方案
通过存储过程跨用户调用（用户 A select，insert 用户 B 的某张表）需要单独对用户 B 的某张表赋权，需要什么权限给哪个表加权限。
现在切换到 SYS 用户下给 TEST2 赋予 test1.std 的权限。
[root@ob120 ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -p xxxx
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3222359508
Server version: OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:49:25)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]> grant select,insert on test1.std to test2;
Query OK, 0 rows affected (0.044 sec)
obclient [SYS]> exit
Bye
[root@ob120 ~]# obclient -h127.0.0.1 -uTEST2@oracle -P2881 -p xxx
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3222359759
Server version: OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:49:25)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [TEST2]> call pro1(2,91); ##已经不报错了且数据已经插入进去了
Query OK, 0 rows affected (0.010 sec)
obclient [TEST2]> call pro2();
Query OK, 0 rows affected (0.440 sec)
obclient [TEST2]> select * from test1.std;
+------+------+
| S_ID | S_GD |
+------+------+
| 1 | 90 |
| 2 | 91 |
+------+------+
2 rows in set (0.007 sec)
