ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1431|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2017-6-7 12C新特性: ACCESSIBLE BY (PL/SQL 白名单)

[复制链接]
论坛徽章:
461
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-6-13 04:36 | 显示全部楼层 |阅读模式

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:        mentzel.iudith

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

注:本题执行环境为12c或更高版本

我创建了下面两个包:

CREATE OR REPLACE PACKAGE qz_pkg_public
IS
   c_allowed  CONSTANT BOOLEAN := TRUE;

   FUNCTION stuff RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY qz_pkg_public
IS
   FUNCTION stuff RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'Public';
   END stuff;
END;
/

CREATE OR REPLACE PACKAGE qz_pkg_private
   ACCESSIBLE BY (PROCEDURE qz_proc)
IS
   c_allowed  CONSTANT BOOLEAN := TRUE;

   FUNCTION stuff RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY qz_pkg_private
IS
   FUNCTION stuff RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'Private';
   END stuff;
END;
/

哪些选项创建了名为QZ_PROC的过程,使得下列代码块执行之后:

BEGIN
   qz_proc ('PUBLIC');
   qz_proc ('PRIVATE');
END;
/

会显示如下的输出:

Public
Private

(A)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
BEGIN
   DBMS_OUTPUT.put_line(
        CASE p_pkg
           WHEN 'PUBLIC'
              THEN qz_pkg_public.stuff()
           WHEN 'PRIVATE'
              THEN qz_pkg_private.stuff()           
           ELSE 'Error'
        END);
END;
/

(B)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
    l_cursor INTEGER := DBMS_SQL.OPEN_CURSOR();
    l_status INTEGER ;
BEGIN
    DBMS_SQL.PARSE( l_cursor,
            'BEGIN DBMS_OUTPUT.put_line(QZ_PKG_' || p_pkg || '.STUFF); END;',
             DBMS_SQL.NATIVE );
     
    l_status := DBMS_SQL.EXECUTE(l_cursor);

    DBMS_SQL.CLOSE_CURSOR(l_cursor);
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(SQLERRM);
END;
/



(C)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
   l_result  VARCHAR2(100);
BEGIN
   EXECUTE IMMEDIATE
          'BEGIN :l_result := QZ_PKG_' || p_pkg || '.STUFF; END;'
     USING OUT l_result;

   DBMS_OUTPUT.put_line(l_result);
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(SQLERRM);
END;
/

(D)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
BEGIN
   $IF qz_pkg_public.c_allowed
   $THEN
       DBMS_OUTPUT.put_line(
          CASE p_pkg
             WHEN 'PUBLIC'
               THEN qz_pkg_public.stuff()
             WHEN 'PRIVATE'
               THEN qz_pkg_private.stuff()           
             ELSE 'Error'
          END);
   $ELSE
       NULL;
   $END
END;
/

(E)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
BEGIN
   $IF qz_pkg_private.c_allowed
   $THEN
       DBMS_OUTPUT.put_line(
          CASE p_pkg
             WHEN 'PUBLIC'
               THEN qz_pkg_public.stuff()
             WHEN 'PRIVATE'
               THEN qz_pkg_private.stuff()           
             ELSE 'Error'
          END);
   $ELSE
       NULL;
   $END
END;
/

(F)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
BEGIN
   $IF $$PLSQL_OPTIMIZE_LEVEL = 2
   $THEN
       DBMS_OUTPUT.put_line(
          CASE p_pkg
             WHEN 'PUBLIC'
               THEN qz_pkg_public.stuff()
             WHEN 'PRIVATE'
               THEN qz_pkg_private.stuff()           
             ELSE 'Error'
          END);
   $ELSE
       NULL;
   $END
END;
/

(G)
CREATE OR REPLACE PROCEDURE qz_proc (p_pkg IN VARCHAR2)
AS
    l_result  VARCHAR2(100);
BEGIN
    CASE p_pkg
       WHEN 'PUBLIC'
       THEN
             SELECT qz_pkg_public.stuff()
             INTO l_result
             FROM DUAL;
       WHEN 'PRIVATE'
       THEN
             SELECT qz_pkg_private.stuff()
             INTO l_result
             FROM DUAL;
       ELSE
             l_result := 'Error';
    END CASE;

    DBMS_OUTPUT.put_line( l_result );
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line (SQLERRM);
END;
/

论坛徽章:
248
秀才
日期:2016-08-05 10:38:01水瓶座
日期:2016-03-24 22:16:36摩羯座
日期:2016-03-17 15:09:14巨蟹座
日期:2016-03-12 12:03:33双鱼座
日期:2016-02-27 21:51:59射手座
日期:2016-02-23 23:47:49双子座
日期:2016-01-27 13:01:30天蝎座
日期:2016-01-19 16:08:26SQL大赛参与纪念
日期:2016-01-13 10:32:19秀才
日期:2016-03-01 09:57:08
发表于 2017-6-13 18:27 | 显示全部楼层
学习了

ADF

A: qz_proc 根据访问的包的定义 qz_pkg_private
   ACCESSIBLE BY (PROCEDURE qz_proc) 所以可以访问包 qz_pkg_private
B: 利用DBMS_SQL执行动态PLSQL,以访问包 qz_pkg_private
   报错 PLS-00904: insufficient privilege to access object QZ_PKG_PRIVAT
C: 利用EXECUTE IMMEDIATE 执行动态PLSQL,以访问包 qz_pkg_private
   同样报错 PLS-00904: insufficient privilege to access object QZ_PKG_PRIVAT
D: $IF $THEN ...条件编译环境下,可以访问包 普通的包变量 qz_pkg_public.c_allowed
E: $IF $THEN ...条件编译环境下,不能访问包 受限的包变量qz_pkg_private.c_allowed
   PLS-00904: insufficient privilege to access object QZ_PKG_PRIVAT
F: $IF $$PLSQL_OPTIMIZE_LEVEL = 2  条件判断没有访问包变量或包,  条件判断的执行体部分可以静态访问这种受限的包
G: 报错
   ORA-06552: PL/SQL: Statement ignored
   ORA-06553: PLS-904: insufficient privilege to access object QZ_PKG_PRIVATE
   SQL 语句中不能访问受限的包

使用道具 举报

回复
论坛徽章:
461
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-6-14 05:15 | 显示全部楼层
答案ADF,2楼得奖。

A: 对私有和公有包的子程序的静态调用确实能正确工作。

B: 利用DBMS_SQL来动态调用一个PL/SQL单元对公有单元是允许的,对白名单的私有单元却不然。

这会显示如下错误:

PLS-00904: insufficient privilege to access object QZ_PKG_PRIVATE

C: 利用本地动态SQL来调用一个PL/SQL单元对公有单元是允许的,对白名单的私有单元却不然。

这会显示如下错误:

PLS-00904: insufficient privilege to access object QZ_PKG_PRIVATE

D: 此处的条件编译指令$IF会访问QZ_PKG_PUBLIC.C_ALLOWED常量,这是允许的。受保护的包实际上只会在执行时被访问,利用的是静态PL/SQL调用。
E: 此处的条件编译指令$IF会访问QZ_PKG_PRIVATE.C_ALLOWED常量,这对于白名单的包是不允许的。
这在编译时会报下面的错误:

Errors: PROCEDURE QZ_PROC
Line: 4 PLS-00904: insufficient privilege to access object QZ_PKG_PRIVATE
Line: 3 PL/SQL: Statement ignored
Line: 4 PLS-00174: a static boolean expression must be used

F: 如果不在编译时访问受保护的包,使用条件编译指令本身并不会有任何问题。受保护的包实际上只会在执行时被访问,利用的是静态PL/SQL调用。
G: 从SQL中调用一个来自受保护的包是不被允许的,即使是静态调用也不行。

下列的输出将被显示:

Public
ORA-06553: PLS-904: insufficient privilege to access object QZ_PKG_PRIVATE

注意,PLS-904错误看起来像是编译错误,即使在这种情况下它仅在运行时发生。

此外,如果我们把这个过程的代码写成用单独一个SELECT,如下:

    SELECT CASE p_pkg
             WHEN 'PUBLIC'
               THEN qz_pkg_public.stuff()
             WHEN 'PRIVATE'
               THEN qz_pkg_private.stuff()           
             ELSE 'Error'
           END
    INTO l_result
    FROM DUAL;

上述错误也会出现两次,对每个调用出现一次,因为对受保护函数的调用已经是同一个SQL语句的一部分,即使在运行时由于CASE语句的优化,实际上并不一定被调用到。

使用道具 举报

回复
认证徽章
论坛徽章:
3
秀才
日期:2017-06-29 10:16:48技术图书徽章
日期:2017-06-29 10:17:04秀才
日期:2017-06-29 10:17:04
发表于 2017-6-14 17:08 | 显示全部楼层
答案是A、D、F。
首先感谢楼主出题,让我学到了这方面的知识,Changes in Oracle Database PL/SQL Language Reference 12c Release 2 (12.2)中的ACCESSIBLE BY语法,即白名单,链接如下http://docs.oracle.com/database/ ... 3-96E7-3E85F240FF36。下面做一下答题分析:
A、应该是最简洁的方式,由于qz_pkg_private定义中包含ACCESSIBLE BY,必须用白名单中的qz_proc来call qz_pkg_private包的stuff()函数/方法;
B、C、是一类错误,The ACCESSIBLE BY clause allows access only when the call is direct. The check will fail if the access is through static SQL, DBMS_SQL, or dynamic SQL.在Oracle官方文档中提到由ACCESSIBLE BY声明的对象只能通过白名单内的对象来调用,不可直接调用。报错见在线文档的示例;
D、在条件编译里可以调用未保护的子程序,因qz_pkg_public未使用白名单,因而其中的常量是可以在条件编译中被外部程序调用的;
E、Calls to a protected subprogram from a conditional compilation directive will fail.由qz_pkg_private定义可知,其为受保护的子程序,故其中的常量无法在条件编译中使用。这里$代表条件编译,我也是新学到的,再次感谢楼主;
F、默认的PLSQL_OPTIMIZE_LEVEL 就是2,实质上和A、D一样,所以正确;
G、如B、C选项的解释,无法在SQL中调用受保护的子程序。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票6.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月2日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表