创建一个只有Select权限的VIEW用户

昨天同事提了个内部需求，要我帮他建一个只有select权限的用户，之前也做过，现在记下笔记，分享给大家。
步骤：sysdba建用户，给connect resource create synonym权限给用户，然后连接到要select 表的所有者，给新用户select any tale 的权限，连接到新用户，创建同义词，基本完成。
详细如下，最后面还有简单测试：


[code]
C:\\Documents and Settings\\whzl>sqlplus a/a@fwd as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 6月 27 09:47:32 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
fwd OPEN

SQL> create user fwd_view identified by fwd account unlock;

用户已创建。

SQL> grant connect,resource to fwd_view;

授权成功。

SQL> grant create synonym to fwd_view;

授权成功。

SQL> conn fwd/fwd@fwd;
已连接。
SQL> grant select any table to fwd_view;

授权成功。

SQL> select 'create synonym '||table_name ||' for fwd.'|| table_name ||';' from
user_tables;

'CREATESYNONYM'||TABLE_NAME||'FORFWD.'||TABLE_NAME||';'
--------------------------------------------------------------------------------

create synonym FORM_VISIT_AFTERSALE for fwd.FORM_VISIT_AFTERSALE;
create synonym VISIT_CHECK for fwd.VISIT_CHECK;
create synonym INFO for fwd.INFO;
create synonym OPERATION_LOG for fwd.OPERATION_LOG;
create synonym ORDERS for fwd.ORDERS;
create synonym STOCK for fwd.STOCK;
。。。。。。。。
。。。。。。。。

已选择30行。

SQL> conn fwd_view/fwd@fwd;
已连接。
SQL> create synonym FORM_VISIT_AFTERSALE for fwd.FORM_VISIT_AFTERSALE;

同义词已创建。

SQL> create synonym VISIT_CHECK for fwd.VISIT_CHECK;

同义词已创建。

SQL> create synonym INFO for fwd.INFO;

同义词已创建。

SQL> create synonym OPERATION_LOG for fwd.OPERATION_LOG;

同义词已创建。

SQL> create synonym ORDERS for fwd.ORDERS;

同义词已创建。

SQL> create synonym STOCK for fwd.STOCK;

同义词已创建。

。。。。。。。。
。。。。。。。。


SQL> select count(*) from users;

COUNT(*)
----------
779

SQL> delete from users where distributor = '88888888';
delete from users where distributor = '88888888'
*
第 1 行出现错误:
ORA-01031: 权限不足


SQL> update users set distributor = '88888888' where distributor = '88888888';
update users set distributor = '88888888' where distributor = '88888888'
*
第 1 行出现错误:
ORA-01031: 权限不足[/code]