在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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-680498/