ITPub博客

首页 > 应用开发 > IT综合 > 关于权限的一个疑问

关于权限的一个疑问

原创 IT综合 作者:zhyuh 时间:2005-10-13 11:22:54 0 删除 编辑

用户有dba role后,就会具有dba role所包含的select any table权限,但是这个权限在用户的stored procedure中并不起作用,必须要单独授权。

[@more@]

以上结论是正确的,可以用下面的小实验验证。

1。创建用户aaa并授予connect, resource, dba角色
SQL> create user aaa identified by aaa default tablespace users temporary tablespace temp;
User created

SQL> grant connect,resource,dba to aaa;
Grant succeeded

2。用户aaa登陆后创建一个public synonym,然后在一个存储过程中使用这个synonym

SQL> connect aaa/aaa
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as aaa

SQL> select * from scott.emp where rownum<2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20

SQL> create public synonym emp for scott.emp;
Synonym created

SQL> select * from emp where rownum<2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20

SQL> create or replace procedure test1 is
2 v_ename varchar2(20);
3 begin
4 select ename
5 into v_ename
6 from emp
7 where empno = 7654;
8 dbms_output.put_line(v_ename);
9 end;
10 /
Warning: Procedure created with compilation errors

存储过程编译出错,错误信息为
QL> show error
Errors for PROCEDURE AAA.TEST1:
LINE/COL ERROR
-------- -----------------------------------------------
6/8 PL/SQL: ORA-00942: table or view does not exist
4/3 PL/SQL: SQL Statement ignored
即emp对象不存在,导致编译出错。

3。给aaa用户赋上select any table的权限,则编译成功
SQL> connect system/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> grant select any table to aaa;
Grant succeeded

SQL> connect aaa/aaa
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as aaa

SQL> create or replace procedure test1 is
2 v_ename varchar2(20);
3 begin
4 select ename
5 into v_ename
6 from emp
7 where empno = 7654;
8 dbms_output.put_line(v_ename);
9 end;
10 /

Procedure created
编译成功。

4。但是dba 角色已经具备select any table的权限了
SQL> select * from role_sys_privs where role='DBA' and privilege like '%SELECT%';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
DBA SELECT ANY TABLE YES
DBA SELECT ANY SEQUENCE YES
DBA SELECT ANY DICTIONARY YES
DBA SELECT ANY TRANSACTION YES

再检查aaa用户拥有的角色,确实有dba 角色
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
AAA CONNECT NO YES NO
AAA DBA NO YES NO
AAA RESOURCE NO YES NO

但是下面的实验则有些不明白,目前尚未找到满意的答案。

但是继续上面的测试。

1。收回select any table权限
SQL> connect system/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> revoke select any table from aaa;
Revoke succeeded

2。由scott用户赋给aaa seelct on emp的权限,编译同样成功
SQL> connect scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott

SQL> grant select on emp to aaa;
Grant succeeded

SQL> connect aaa/aaa;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as aaa

SQL> create or replace procedure test1 is
2 v_ename varchar2(20);
3 begin
4 select ename
5 into v_ename
6 from emp
7 where empno = 7654;
8 dbms_output.put_line(v_ename);
9 end;
10 /
Procedure created

3。但是scott用户收回select on emp的权限后,编译还能成功
SQL> connect scott/tiger;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott

SQL> revoke select on emp from aaa;
Revoke succeeded

SQL> connect aaa/aaa
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as aaa

SQL> create or replace procedure test1 is
2 v_ename varchar2(20);
3 begin
4 select ename
5 into v_ename
6 from emp
7 where empno = 7654;
8 dbms_output.put_line(v_ename);
9 end;
10 /

Procedure created

编译仍然成功。
检查一下aaa用户的权限,和第一次测试编译失败时的权限一样

SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
没有记录

SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
AAA UNLIMITED TABLESPACE NO
没有select any table权限

SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
AAA CONNECT NO YES NO
AAA DBA NO YES NO
AAA RESOURCE NO YES NO
相同的3个role

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-807862/,如需转载,请注明出处,否则将追究法律责任。

下一篇: REF CURSOR 小结
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008792