ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 对Oracle View授权和where查询需要注意的地方

对Oracle View授权和where查询需要注意的地方

原创 Linux操作系统 作者:尛样儿 时间:2010-01-09 22:03:35 0 删除 编辑

授权需要注意:

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>

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

请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    483
  • 访问量
    5261045