dba用户创建存储过程报权限不足

we6100

于 2022-09-25 08:45:42 发布

阅读量392

点赞数

分类专栏： Oracle 文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/we6100/article/details/127033893

版权

Oracle 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

https://www.yuque.com/wei01/rlggh6/fz76hr

原始sql，（如果是sys用户不会有下面问题)

create or replace procedure backup_emp
 is
  Begin

    insert into scott.employees_bak (TASKID, TASKTYPE, TASK_STATUES, FRE, EXECUTE, OUTIME) select * from scott.employees;
    delete from scott.employees_bak where insert_time<sysdate - 14;
    commit;

 end;
/

由于用户只有DBA role 并没有表的实际insert delete权限，在pl/sql内无法识别role权限

修改后，添加authid current_user 调用当前登陆的用户权限，execute immediate 'xxx'

create or replace procedure backup_emp authid current_user
 is
  Begin

    execute immediate 'insert into scott.employees_bak (TASKID, TASKTYPE, TASK_STATUES, FRE, EXECUTE, OUTIME) select * from scott.employees';
    execute immediate 'delete from scott.employees_bak where insert_time<sysdate - 14';
    commit;

 end;
/