查看: 387|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-12-31 角色

[复制链接]
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2019-1-10 03:49 | 显示全部楼层 |阅读模式

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

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

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

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

作者: Chris Saxon

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

你的应用在 qz_data_owner用户下存储数据。应用是以qz_app_user连接的。这个用户被授予一个角色,允许它对qz_customers表具有SELECT和INSERT权限。但是这个角色被屏蔽了,如下:

grant create table, unlimited tablespace
  to qz_data_owner
  identified by qz_data_owner;
  
grant create session
  to qz_app_user
  identified by qz_app_user;

create role qz_customer_read;
create role qz_customer_write;
create role qz_customer_read_write;

create table qz_data_owner.qz_customers (
  customer_id   integer not null primary key,
  customer_name varchar2(20) not null
);

grant select
  on qz_data_owner.qz_customers
  to qz_customer_read;
  
grant insert, update, delete
  on qz_data_owner.qz_customers
  to qz_customer_write;
  
grant qz_customer_read, qz_customer_write
  to qz_customer_read_write;

grant qz_customer_read_write
  to qz_app_user;

alter user qz_app_user
  default role none;

你以qz_app_user连接之后执行下列哪些选项,随后再执行这个INSERT语句以及查询:

insert into qz_data_owner.qz_customers
  values ( 1, 'First customer!' );

select * from qz_data_owner.qz_customers;

查询会返回这一行:

CUSTOMER_ID CUSTOMER_NAME
----------- --------------------
          1 First customer!

(A)
set role all;

(B)
set role qz_customer_read;

(C)
set role qz_customer_read,  
         qz_customer_write;

(D)
set role all
  except qz_customer_write;

(E)
set role qz_customer_read_write
  except qz_customer_write;
论坛徽章:
381
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2019-1-10 10:17 | 显示全部楼层
答案:AC

A: set role all;  这个执行后 qz_app_user 拥有
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
QZ_CUSTOMER_READ_WRITE
QZ_CUSTOMER_READ
QZ_CUSTOMER_WRITE
这3个角色权限,所以可以读写操作

B: qz_customer_read 这个只能查询 qz_data_owner.qz_customers ,不能写

C:  这两个角色没有直接赋予,但间接来源于QZ_CUSTOMER_READ_WRITE,可以读写操作

D: set role all  except qz_customer_write; 报错
    ORA-01924: role 'QZ_CUSTOMER_WRITE' not granted or does not exist
   
    字面意义,就算执行成功,排除了写操作,那当然也不能插入了

E: set role qz_customer_read_write except qz_customer_write
ORA-00933: SQL command not properly ended

看来 SET ROLE ... EXCEPT 后面不能接间接继承的角色

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2019-1-11 06:21 | 显示全部楼层
答案ACD, 本期无人得奖。抱歉我忘记说这个题目是12C的。你是不是在11G下面做的测试?

A: (推荐)
是的,这会开启所有赋予qz_app_user的角色。它具有read_write角色。所以它能够对qz_customers进行INSERT和SELECT操作。

B: 这仅仅开启了对qz_customers的查询角色。所以INSERT会报错:
"ORA-01031: insufficient privileges"

C: 是的,qz_app_user通过qz_customer_read_write继承了这些角色。即使你没有直接授予这些角色,你仍然可以分别开启它们。

D: 是的!你在except子句中列出的角色不会被开启。但是qz_customer_read_write是开启的。而qz_app_user通过qz_customer_write具备了这一权限。所以它们仍然具备对qz_customers的读写权限。

E: 你只能够在set role的all选项中使用except子句。这会报错:
"ORA-00933: SQL command not properly ended".
qz_app_user不会有任何开启的角色。

使用道具 举报

回复
论坛徽章:
381
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2019-1-11 16:41 | 显示全部楼层
12.1

执行 D 选项出错啊。。。

SQL> set role all except qz_customer_write;
set role all except qz_customer_write
ORA-01924: role 'QZ_CUSTOMER_WRITE' not granted or does not exist

SQL> set role all;
Role set

SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
QZ_CUSTOMER_READ_WRITE
QZ_CUSTOMER_READ
QZ_CUSTOMER_WRITE

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2019-1-12 15:28 | 显示全部楼层
我测试D选项没问题。你把那些GRANT完整执行一遍?

使用道具 举报

回复
论坛徽章:
381
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2019-1-12 21:47 | 显示全部楼层
newkid 发表于 2019-1-12 15:28
我测试D选项没问题。你把那些GRANT完整执行一遍?

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE        12.1.0.1.0        Production                                                                  0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL>
SQL> grant create table, unlimited tablespace
  2    to qz_data_owner
  3    identified by qz_data_owner;
Grant succeeded
SQL> grant create session
  2    to qz_app_user
  3    identified by qz_app_user;
Grant succeeded
SQL> create role qz_customer_read;
Role created
SQL> create role qz_customer_write;
Role created
SQL> create role qz_customer_read_write;
Role created
SQL> create table qz_data_owner.qz_customers (
  2    customer_id   integer not null primary key,
  3    customer_name varchar2(20) not null
  4  );
Table created
SQL> grant select
  2    on qz_data_owner.qz_customers
  3    to qz_customer_read;
Grant succeeded
SQL> grant insert, update, delete
  2    on qz_data_owner.qz_customers
  3    to qz_customer_write;
Grant succeeded
SQL> grant qz_customer_read, qz_customer_write
  2    to qz_customer_read_write;
Grant succeeded
SQL> grant qz_customer_read_write
  2    to qz_app_user;
Grant succeeded
SQL> alter user qz_app_user
  2    default role none;
User altered

SQL> conn qz_app_user/qz_app_user;
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0
Connected as qz_app_user

SQL>
SQL> set role all except qz_customer_write;
set role all except qz_customer_write
ORA-01924: role 'QZ_CUSTOMER_WRITE' not granted or does not exist

SQL>
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------

SQL> set role all;
Role set

SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
QZ_CUSTOMER_READ_WRITE
QZ_CUSTOMER_READ
QZ_CUSTOMER_WRITE

SQL> set role all except qz_customer_write;
set role all except qz_customer_write
ORA-01924: role 'QZ_CUSTOMER_WRITE' not granted or does not exist

SQL> set role none;
Role set

SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------

SQL> set role all except qz_customer_write;
set role all except qz_customer_write
ORA-01924: role 'QZ_CUSTOMER_WRITE' not granted or does not exist

SQL>

使用道具 举报

回复
认证徽章
论坛徽章:
67
秀才
日期:2019-03-04 14:25:35秀才
日期:2018-12-05 13:58:28秀才
日期:2018-12-05 13:52:27秀才
日期:2018-11-13 15:18:40秀才
日期:2018-11-13 15:18:40秀才
日期:2018-11-13 15:18:40秀才
日期:2018-11-13 15:18:40秀才
日期:2018-11-13 15:13:47技术图书徽章
日期:2018-11-13 15:09:25秀才
日期:2018-11-13 15:07:29
发表于 2019-1-14 11:02 | 显示全部楼层
学习了。角色赋权限,够全活的。

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2019-1-15 04:56 | 显示全部楼层
这个比较奇怪,我在12.2确实可以,但是原题也标注了是12.1。我把问题发给原网站了。

使用道具 举报

回复

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

本版积分规则 发表回复

大数据时代数据库架构演进与实践技术沙龙-北京

活动介绍:
在这个“数据”爆炸的时代,海量数据的处理能力已经成了考量数据库重要的因素之一,通过分布式数据库来扩展信息系统的处理能力,已成为近年来的一种普遍选择。本次沙龙我们邀请了国内外一线技术专家就“大数据时代数据库架构演进与实践”来为大家做分享,谈谈在数据量呈现指数级增长的大数据时代,企业数据库架构所面临的挑战及演进的思考!
主题:大数据时代数据库架构演进与实践
时间:2019年3月30日 9:30-12:15
地点:机械骑士咖啡书店(海淀区中关村大街15-11创业公社中关村国际创客中心B1层,地铁中关村站E口出)

免费报名通道>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表