需要设计一个存储过程,用来给其他用户下的表定义触发器,过程接收两个参数:schema_name 和 table_name,但是遇到了权限问题。为了验证这个问题,使用一段简单的代码,其中,执行用户为fxy,具有DBA权限,然后向u4test中插入一条记录,代码如下:
CREATE or replace procedure tproc_u4test as
begin
insert into "U4TEST"."STUDENT"(sid,sname) values('okk','kkkk');
end;然后报出ora-06575的错误
Package or function string is in an invalid state
Cause: A SQL statement references a PL/SQL function that is in an invalid state. Oracle attempted to compile the function, but detected errors.
Action: Check the SQL statement and the PL/SQL function for syntax errors or incorrectly assigned, or missing, privileges for a referenced object.直接执行 insert into "U4TEST"."STUDENT"(sid,sname) values('okk','kkkk'); 是没有问题的,但是放到存储过程里就不行了,对于Oracle的权限系统比较模糊,不知道这里怎么解决,还请高手赐教!
CREATE or replace procedure tproc_u4test as
begin
insert into "U4TEST"."STUDENT"(sid,sname) values('okk','kkkk');
end;然后报出ora-06575的错误
Package or function string is in an invalid state
Cause: A SQL statement references a PL/SQL function that is in an invalid state. Oracle attempted to compile the function, but detected errors.
Action: Check the SQL statement and the PL/SQL function for syntax errors or incorrectly assigned, or missing, privileges for a referenced object.直接执行 insert into "U4TEST"."STUDENT"(sid,sname) values('okk','kkkk'); 是没有问题的,但是放到存储过程里就不行了,对于Oracle的权限系统比较模糊,不知道这里怎么解决,还请高手赐教!
begin
insert into "U4TEST"."STUDENT"(sid,sname) values('okk','kkkk');
end;
end;
Grant succeeded
SQL> grant sysdba to test;--编译过程不成功
Grant succeeded
SQL> grant select any table to test;--先前报错对象不存在,现在报错无权限.
Grant succeeded
SQL> grant insert any table to test;--重新编译过程成功
Grant succeeded
SQL>
但是不进行grant之前,直接执行 insert into other_user.table_name却是有权限的啊,这个很郁闷啊
但是不进行grant之前,直接执行 insert into other_user.table_name却是有权限的啊,这个理解上有些乱啊
以前我过程里面动态建视图 也报权限问题 后面手动授权就可以了