分类目录归档：Oracle

in/exists和not in/not exists语意探讨

发表于 2011 年 12 月 10 日由惜分飞

本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境

C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table t1 (c1 number,c2 number);

表已创建。

SQL> create table t2 (c1 number,c2 number);

表已创建。

SQL> insert into t1 values (1,2);

已创建 1 行。

SQL> insert into t1 values (1,3);

已创建 1 行。

SQL> insert into t2 values (1,2);

已创建 1 行。

SQL> insert into t2 values (1,null);

已创建 1 行。

SQL> insert into t1 values (1,null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t1;

        C1         C2
---------- ----------
         1          2
         1          3
         1

SQL> select * from t2;

        C1         C2
---------- ----------
         1          2
         1

Note:t1和t2表都有null，且t1比t2多一条记录

2、t2做内部表

SQL> select * from t1 where c2 in (select c2 from t2 );

        C1         C2
---------- ----------
         1          2

SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          2

SQL> select * from t1 where c2 not in (select c2 from t2 );

未选定行

SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          3
         1

3、t1为内部表

SQL> select * from t2 where c2 in (select c2 from t1 );

        C1         C2
---------- ----------
         1          2

SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          2

SQL> select * from t2 where c2 not in (select c2 from t1 );

未选定行

SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1

3、结论
in和exists结果相同(都会排除掉null,无论内部表中有无null)
not in会过滤掉外部表中的null(即使内部表中无null)
not exists不会过滤掉外部表的null(即使内部表有null)
由于篇幅关系,括号中的部分实验过程未展现出来

发表在 Oracle 开发 | 评论关闭

回收dba中alter system处理方法

发表于 2011 年 12 月 10 日由惜分飞

今天在pub上看到一个问题，一个朋友想回收dba的alter system权限，直接回收这个系统权限从dba的做法是不推荐使用，因为修改了系统默认的dba角色所具有的系统权限，可能会导致未知的后果。好的做法是创建新的角色，使其有dba中除alter system之外的所有权限。
1、数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2、创建角色并授权

SQL> create role xifenfei;

角色已创建。

SQL> declare
  2  cursor cur is select privilege from role_sys_privs where role='DBA'
  3  AND PRIVILEGE NOT IN ('ALTER SYSTEM','ADMINISTER RESOURCE MANAGER');
  4  BEGIN
  5    FOR C in cur loop
  6    EXECUTE IMMEDIATE  'grant '||c.privilege||' to xifenfei';
  7    END loop;
  8  END;
  9  /

PL/SQL 过程已成功完成。

SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  2  WHERE ROLE ='DBA' AND PRIVILEGE NOT IN(
  3  SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  4  WHERE ROLE ='XIFENFEI'
  5  );

PRIVILEGE
----------------------------------------
ALTER SYSTEM
ADMINISTER RESOURCE MANAGER

说明：授予创建角色出ALTER SYSTEM和ADMINISTER RESOURCE MANAGER系统权限之外的所有权限

3、创建用户并授权角色

SQL> create user chf_xff identified by xifenfei;

用户已创建。

SQL> grant xifenfei to chf_xff;

授权成功。

4、单独授予ADMINISTER RESOURCE MANAGER权限

SQL> exec dbms_resource_manager_privs.grant_system_privilege(
   2 grantee_name => 'CHF_XFF',admin_option => false);

PL/SQL 过程已成功完成。

SQL> CONN chf_xff/xifenfei
已连接。

SQL> SELECT * FROM SESSION_PRIVS WHERE
  2  PRIVILEGE ='ADMINISTER RESOURCE MANAGER';

PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER

说明:
1)通过授权xifenfei角色和ADMINISTER RESOURCE MANAGER权限，完成回收dba中的alter system权限要求。
2)如果只有个别用户有这样的需求，那么可以直接生成批量授权语句实现，而不用建立类此xifenfei这样的角色。

5、为何单独授予ADMINISTER RESOURCE MANAGER权限

SQL> CONN / AS SYSDBA
已连接。
SQL> GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF;
GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效

说明：ADMINISTER RESOURCE MANAGER这个系统权限在10g及其以后版本中，就不能直接使用GRANT/REVOKE直接授权/回收权限,而必面使用dbms_resource_manager_privs.grant_system_privilege和revoke_system_privilege过程进行处理。

发表在 Oracle | 标签为 ORA-00990 | 评论关闭

创建视图提示ORA-01031

发表于 2011 年 12 月 9 日由惜分飞

1、重现问题

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看相关权限情况

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

发表在 ORA-xxxxx | 标签为 ORA-01031 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

in/exists和not in/not exists语意探讨

回收dba中alter system处理方法

创建视图提示ORA-01031

文章分类

最近发表

分类目录归档：Oracle

in/exists和not in/not exists语意探讨

回收dba中alter system处理方法

创建视图提示ORA-01031

标签云

文章分类

最近发表