10g恢复CONNECT角色权限

在10g开始，Oracle回收了CONNECT角色的绝大部分权限，只保留了CREATE SESSION这一个系统权限。

在9i中的connect角色权限：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select privilege
  2  from dba_sys_privs
  3  where grantee = 'CONNECT';

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

8 rows selected.

在10g中：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> select privilege
  2  from dba_sys_privs
  3  where grantee = 'CONNECT';

PRIVILEGE
----------------------------------------
CREATE SESSION

Oracle在进行了角色权限修改的同时还考虑兼容性，为了避免对用户产生太多的影响，在10g/11g中，仍然存在恢复CONNECT角色权限的方法，通过调用脚本$ORACLE_HOME/rdbms/admin/rstrconn.sql就可以完成这个操作。

这个脚本也简单到不能再简单的地步了：

Rem
Rem $Header: rstrconn.sql 10-aug-2004.14:24:52 pthornto Exp $
Rem
Rem rstrconn.sql
Rem
Rem Copyright (c) 2004, Oracle. All rights reserved. 
Rem
Rem    NAME
Rem    rstrconn.sql - SQL*Plus script. to grant all the
Rem                   pre-10gR2 privielges back to CONNECT Role.
Rem
Rem    DESCRIPTION
Rem    This script. should be run by a user who is a SYSDBA or has the
Rem    DBA role granted to them.
Rem
Rem    NOTES
Rem    By default, 10gR2 and higher only grants CREATE SESSION
Rem    to CONNECT. This script. can be used to restore
Rem    pre-10GR2 CONNECT privileges
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pthornto    08/10/04 - pthornto_sqlbsq_connect_deprecate
Rem    pthornto    08/10/04 - Created
Rem

GRANT create session, create table, create view, create synonym,
  create database link, create cluster, create sequence, alter session
  TO CONNECT;
commit;

脚本中注释比可执行语句要长得多，而真正有意义的只有GRANT一句，最后还添加了一个毫无意义的commit语句，而GRANT语句本身就是ddl，会自动执行隐式commit，没想到Oracle自己的脚本也这么不专业。

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