對Oracle View授權和where查詢需要注意的地方

授權需要注意：

SQL> create user test identified by test default tablespace userdata temporary tablespace temp;

User created.

SQL> create user test111 identified by test111 default tablespace userdata temporary tablespace temp;

User created.

SQL> create user test222 identified by test222 default tablespace userdata temporary tablespace temp;

User created.

SQL> grant create session,create table,unlimited tablespace to test;

Grant succeeded.

SQL> grant create session,create view to test111;

Grant succeeded.

SQL> grant create session to test222;

Grant succeeded.

SQL> connect test/test
Connected.
SQL> create table xiaoyang(name varchar2(20));

Table created.

SQL> insert into xiaoyang values('aaaa');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on xiaoyang to test111;

Grant succeeded.

SQL> connect test111/test111
Connected.
SQL> create view xiaoyang as select * from test.xiaoyang;

View created.

SQL> select * from test111.xiaoyang;

NAME
----------
aaaa

SQL> grant select on test111.xiaoyang to test222;
grant select on test111.xiaoyang to test222
                        *
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST.XIAOYANG'

SQL> connect test/test
Connected.
SQL> grant select on xiaoyang to test222;

Grant succeeded.

SQL> connect test111/test111
Connected.

--即使在上面已經將select on xiaoyang的許可權賦予了test222，這裡依然會報錯。
SQL> grant select on test111.xiaoyang to test222;
 grant select on test111.xiaoyang to test222
                         *
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST.XIAOYANG'

SQL> connect test/test
Connected.

--唯有將test.xiaoyang的with grant option賦予test111，然後test111將檢視xiaoyang賦予test222才不會報錯。
SQL> grant select on xiaoyang to test111 with grant option;

Grant succeeded.

SQL> connect test111/test111;
Connected.
SQL> grant select on test111.xiaoyang to test222;

Grant succeeded.

SQL> connect test222/test222
Connected.
SQL> select * from test111.xiaoyang;

NAME
----------
aaaa


對檢視新增WHERE條件需要注意：

[oracle@blliu ~]$ sqlplus system/manager

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 25 20:37:00 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test as select * from dba_segments;

Table created.

SQL> create index idx_sys_test_pk on test(segment_name);

Index created.

SQL> set autotrace trace explain;
SQL> set linesize 200
SQL> select owner,segment_name,segment_type from test where segment_name='TEST';

Execution Plan
----------------------------------------------------------
Plan hash value: 1310262519

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    70 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST            |     1 |    70 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_SYS_TEST_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SEGMENT_NAME"='TEST')

Note
-----
   - dynamic sampling used for this statement

SQL> set autotrace off            
SQL> create view v_test1 as select owner,segment_name,segment_type from test ;

View created.

SQL> create view v_test2 as select owner,rtrim(ltrim(segment_name)) segment_name,segment_type from test;

View created.

SQL> set autotrace trace explain;
SQL> select * from v_test1 where segment_name='TEST';

Execution Plan
----------------------------------------------------------
Plan hash value: 1310262519

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    70 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST            |     1 |    70 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_SYS_TEST_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SEGMENT_NAME"='TEST')

Note
-----
   - dynamic sampling used for this statement

SQL> select * from v_test2 where segment_name='TEST';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    70 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    70 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(RTRIM(LTRIM("SEGMENT_NAME"))='TEST')

Note
-----
   - dynamic sampling used for this statement

SQL>