对比oracle9i与oracle10g中resource﹐connect角色所拥有的权限(转)-优快云博客

本文介绍在Oracle10g数据库中为用户授予connect和resource角色后遇到的权限不足问题，特别是在创建数据库链接（databaselink）时。文中详细解释了与早期版本相比，Oracle10g中connect角色权限的减少，并提供了如何解决此类问题的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当我们建立一个user时﹐一般情况下我们会给予connect,resource的角色给该用户。今天我在oracle10g下﹐给用户授予这两个角色后﹐在该用户下建立dblink时报ORA-01031: insufficient privileges﹐于是我通过dba_sys_privs视图对比了二个版本数据库的这两种角色﹐发现oracle10g中的connect角色所包含的默认权限由原来的8个减少到只有1个了。在oracle10g中给用户授予这两个角色后﹐会比前两个版本的数据库少了如下权限(所以﹐当我们在oracle10g数据库中需要使用以下权限时﹐要么增加这些权限到connect角色中﹐要么单独授予)﹕

CREATE DATABASE LINK

ALTER SESSION

CREATE VIEW

CREATE SYNONYM

如﹕

SQL> conn system/sys_orarp@t7.11;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as system

SQL> create user pjperson identified by jpjperson default tablespace users temporary tablespace temp;

User created

SQL> grant resource,connect to pjperson;

Grant succeeded

SQL> conn pjperson/jpjperson@t7.11
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as pjperson

SQL> create database link ORCL connect to em_person identified by em_person using 't511';

create database link ORCL connect to em_person identified by em_person using 't511'

ORA-01031: insufficient privileges

SQL> conn system/sys_orarp@t7.11;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as system

SQL> grant CREATE DATABASE LINK to pjperson;

Grant succeeded

SQL> conn pjperson/jpjperson@t7.11
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as pjperson

SQL> create database link ORCL connect to em_person identified by em_person using 't511';

Database link created