一、 引言
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一樣的測試過程和步驟。大家對比一下看看。