一、 引言
1、 目的
最近有很多人通过各种方式和途径来咨询我在使用Oracle的过程中，明明用户拥有create table的权限，却无法在pl/sql完成动态建表和索引的工作，并经常出现类似如下提示：
ERROR 位于第 1 行:
ORA-01031: 权限不足
ORA-06512: 在"TOMS.PRIV_TEST", line 5
ORA-06512: 在line 1
SQL>
而此时用户具有如下权限：
PRIVILEGE
-----------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
本文，作者将带着你解开这个疑团，并通过DEMO的方法，让大家了解在Oracle9i、10g中在PL/SQL中动态建表/索引的部分权限知识。
2、 说明
本指导范文，仅在公司内部公开使用，并仅针对在pl/sql中动态建表/索引权限这一专题做介绍，其他的系统权限使用情况差不多都是这个原理，大家如有时间，请参阅 Oracle的相关资料。
二、 背景知识
在进入本文的主题知识前，我先给大家摘要的介绍一下Oracle中的权限和角色的概念。算是普及知识吧。
为了管理复杂系统的不同用户，Oracle系统提供了角色和权限。权限可以使用户能访问对象或执行程序。而角色是一组权限的集合，同样，角色被授予用户后，用户也具有某些权限。
Oracle有三种类型的权限：
· 对象权限（Object）
· 系统权限（System）
· 角色（Role）
常见的系统权限比如：
|
Category
|
Examples
|
|
index
|
Create index
Create any index
Alter any index
Drop any index
|
|
Table
|
Create table
Create any table
Drop any table
Select/updte/delete any table
|
|
Session
|
Create session
Alter session
Restricted session
|
|
Tablespace
|
Create tablespace
Alter tablespace
Drop tablespace
Unlimited tablespace
|
|
……
|
……
|
常见的对象权限比如：
|
Object priv
|
Table
|
View
|
Sequence
|
Procedure
|
|
Select
|
√
|
√
|
√
|
|
|
Delete
|
√
|
√
|
|
|
|
Update
|
√
|
√
|
|
|
|
Insert
|
√
|
√
|
|
|
|
Alter
|
√
|
√
|
√
|
√
|
|
references
|
√
|
|
|
|
|
Execute
|
|
|
|
√
|
|
Index
|
√
|
√
|
|
|
|
......
|
……
|
……
|
……
|
……
|
常见的系统角色有：
|
Role Name
|
Descriptior
|
|
Connect
Resource
dba
|
These roles are provided for backward compatibility
|
|
Exp_full_database
|
Privileges to export the database
|
|
Imp_full_database
|
Privileges to import the database
|
|
Select_catalog_role
|
select privilege on data dictionary tables
|
|
Execute_catalog_role
|
execute privilege on data dictionary packages
|
|
Delete_catalog_role
|
delete privilege on data dictionary tables
|
三、 权限测试
在这部分，我分别针对Oracle9i和Oracle10g做一个对比测试，下面先丛Oracle9i开始。
1、 Oracle 9i 版本的测试
实验环境：
Windows xp + Oracle 9.2.0.1
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL>
n 创建一个测试用户
SQL> create user toms identified by toms default tablespace toms_study;
用户已创建
SQL> grant connect to toms;
授权成功。
SQL> alter user toms quota 10m on toms_study;
用户已更改。
SQL>
到这里我们创建了一个具备连接、建表的用户，下面我们通过一个procedure 和一个功能目的一样的匿名块分别进行测试
n 测试过程一：用户拥有CONNECT角色
SQL> conn toms/toms
已连接。
SQL> create or replace procedure priv_test is
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
create or replace procedure priv_test is
*
ERROR 位于第 1 行:
ORA-01031: 权限不足
SQL> conn /as sysdba
已连接。
SQL> grant create procedure to toms;
授权成功。
SQL> conn toms/toms;
已连接。
SQL> create or replace procedure priv_test is
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
过程已创建。
SQL> exec priv_test;
BEGIN priv_test; END;
*
ERROR 位于第 1 行:
ORA-01031: 权限不足
ORA-06512: 在"TOMS.PRIV_TEST", line 5
ORA-06512: 在line 1
SQL> declare
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ------------------
NO NUMBER(38)
SQL>
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
已选择9行。
SQL>
测试证明：此时用户虽然拥有create table权限[connect角色中包含]，但在存储过程中还是没有权限执行动态建表，但在匿名块里可以完成。
n 测试过程二：用户拥有CONNECT、RESOURCE角色
SQL> conn /as sysdba
已连接。
SQL> grant resource to toms;
授权成功。
SQL> conn toms/toms
已连接。
SQL> drop table test;
表已丢弃。
SQL> exec priv_test;
BEGIN priv_test; END;
*
ERROR 位于第 1 行:
ORA-01031: 权限不足
ORA-06512: 在"TOMS.PRIV_TEST", line 5
ORA-06512: 在line 1
SQL> declare
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------
NO NUMBER(38)
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
已选择14行。
SQL>
测试证明：此时用户虽然拥有更多的权限，但和测试过程一的效果是一样的。
n 测试过程三：用户拥有CONNECT、RESOURCE、DBA角色
SQL> conn /as sysdba
已连接。
SQL> grant dba to toms;
授权成功。
SQL> conn toms/toms
已连接。
SQL> drop table test;
表已丢弃。
SQL> exec priv_test;
BEGIN priv_test; END;
*
ERROR 位于第 1 行:
ORA-01031: 权限不足
ORA-06512: 在"TOMS.PRIV_TEST", line 5
ORA-06512: 在line 1
SQL> declare
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- --------------------
NO NUMBER(38)
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
PRIVILEGE
----------------------------------------
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
PRIVILEGE
----------------------------------------
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE RULE
PRIVILEGE
----------------------------------------
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
PRIVILEGE
----------------------------------------
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
PRIVILEGE
----------------------------------------
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
PRIVILEGE
----------------------------------------
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
PRIVILEGE
----------------------------------------
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE SNAPSHOT
CREATE ANY SNAPSHOT
ALTER ANY SNAPSHOT
DROP ANY SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
PRIVILEGE
----------------------------------------
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
PRIVILEGE
----------------------------------------
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
PRIVILEGE
----------------------------------------
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
ON COMMIT REFRESH
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
PRIVILEGE
----------------------------------------
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
已选择140行。
SQL>
测试证明：看到了吧，这个时候用户toms已经具备了DBA的角色，权限不可谓不大吧。但在存储过程中，还是不能建表。哈哈。
如果觉得不过瘾，我们给toms SYSDBA权限看看效果
n 测试过程四：用户拥有CONNECT、RESOURCE、DBA、SYSDBA角色
SQL> conn /as sysdba
已连接。
SQL> grant sysdba to toms;
授权成功。
SQL> conn toms/toms;
已连接。
SQL> drop table test;
表已丢弃。
SQL> exec priv_test;
BEGIN priv_test; END;
*
ERROR 位于第 1 行:
ORA-01031: 权限不足
ORA-06512: 在"TOMS.PRIV_TEST", line 5
ORA-06512: 在line 1
测试证明：你还有什么话说？
n 测试过程五：用户拥有create session,create procedure,create table权限
SQL> drop user toms cascade;
用户已丢弃
SQL> create user toms identified by toms default tablespace toms_study;
用户已创建
SQL> grant create session,create procedure,create table to toms;
授权成功。
SQL> alter user toms quota 10m on toms_study;
用户已更改。
SQL> conn toms/toms
已连接。
SQL> create or replace procedure priv_test is
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
过程已创建。
SQL> exec priv_test;
PL/SQL 过程已成功完成。
SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NO NUMBER(38)
SQL> drop table test;
表已丢弃。
SQL> declare
2 v_sql varchar2(255);
3 begin
4 v_sql:='create table test (no int)';
5 execute immediate v_sql;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NO NUMBER(38)
SQL>
测试证明：通过对比前面的测试过程，不难发现，其实用户只有具有显式的create table的权限，就可以在存储过程中完成动态建表的过程。
这也是pl/sql在解析时判断权限的要求。
这也是我要证明的观点。
下面我们转到Oracle 10g里看看是个什么情况？在Oracle 10g中，我采取和9i一样的测试过程和步骤。大家对比一下看看。