视图中的调用者权限控制bequeath current_user

来源：互联网发布：身份证接口阿里云编辑：程序博客网时间：2024/05/17 22:04
-- 环境准备SQL> create user user1 identified by 123;用户已创建。SQL> grant create session,create view,create procedure to user1;授权成功。SQL> create user user2 identified by 123;用户已创建。SQL> grant create session to user2;授权成功。SQL> edit已写入 file afiedt.buf  1  create or replace view user_views_def_v bequeath definer as  2  select view_name,bequeath  3  from user_views  4* order by view_nameSQL> /视图已创建。SQL> edit已写入 file afiedt.buf  1  create or replace view user_views_cu_v bequeath current_user as  2  select view_name,bequeath  3  from user_views  4* order by view_nameSQL> /视图已创建。SQL> grant select on user_views_def_v to user2;授权成功。SQL> grant select on user_views_cu_v to user2授权成功。-- 测试1SQL> column view_name format a30SQL> select * from user1.user_views_def_v;VIEW_NAME                      BEQUEATH------------------------------ ------------USER_VIEWS_CU_V                CURRENT_USERUSER_VIEWS_DEF_V               DEFINERSQL> select * from user1.user_views_cu_v;VIEW_NAME                      BEQUEATH------------------------------ ------------USER_VIEWS_CU_V                CURRENT_USERUSER_VIEWS_DEF_V               DEFINERSQL> conn user2/123@pdb1已连接。SQL> select * from user1.user_views_def_v;未选定行SQL> select * from user1.user_views_cu_v;未选定行-- 查看视图调用者，环境SQL> conn user1/123@pdb1已连接。SQL> column ora_invoking_user format a20SQL> column ora_invoking_userid format a20SQL> select ora_invoking_user,ora_invoking_userid from dual;ORA_INVOKING_USER    ORA_INVOKING_USERID-------------------- --------------------USER1                110SQL> edit已写入 file afiedt.buf  1  create or replace function get_invoking_user  2  return varchar2  3   authid current_user  4  as  5   l_result varchar2(100);  6  begin  7    select ora_invoking_user || ':' || ora_invoking_userid  8    into l_result  9    from dual; 10  return l_result; 11* end;SQL> /函数已创建。SQL> create or replace view get_invoking_user_def_v bequeath definer as  2  select get_invoking_user as invoking_user from dual;视图已创建。SQL> create or replace view get_invoking_user_cu_v bequeath current_user as  2  select get_invoking_user as invoking_user from dual;视图已创建。SQL> grant select on get_invoking_user_def_v to user2;授权成功。SQL> grant select on get_invoking_user_cu_v to user2;授权成功。-- 测试SQL> select* from  user1.get_invoking_user_def_v;INVOKING_USER------------------------------------------------------------------------------USER1:110SQL> select * from user1.get_invoking_user_cu_v;INVOKING_USER------------------------------------------------------------------------------USER1:110SQL> conn user2/123@pdb1已连接。SQL> select* from  user1.get_invoking_user_def_v;INVOKING_USER---------------------------------------------------------USER1:110SQL> select * from user1.get_invoking_user_cu_v;INVOKING_USER---------------------------------------------------------USER2:111-- 从上可以看到，视图中也可控制调用者-- 文章参考：https://oracle-base.com
0 0