oracle 存储过程 execute immediate 'create table' 报 insufficient privileges错误解决及用户权限的理解-开发系列（二）

存储过程 动态创建表 ORA-01031：insufficient privileges错误及oracle用户权限的理解。

1 缘由：

最近有个需求 需要从别的系统 抽取过来数据量比较大的月表 ，但是项目这边的分析库 表空间已经不足了，所以决定在分析库只存一个月的数据  把历史数据转储到历史库去。

由于源系统的月表 是每个月会新建一张表 表名格式为 xx_201407  所以 ,为了跟源系统保持一致 同时也为了方便清理 历史月表数据 ，在 历史库也采用每月1号新建表的方式 然后把

数据 从分析库抽取到历史库表，完成后再从源系统把最新数据全量同步到分析库。

2 说明：

举个例子 来说明 月数据表的数据流转情况：

假设 现在时间是20140901 源系统是每个月月初生成上个月数据，即出八月份数据 表名：tb_201408 而我们分析库 表名为tb 存的是7月份数据 历史库有tb_201406(6月份数据)、tb_201405(5月份数据)、等等。

现在要做的事情：

1 先在历史库新建表：tb_201407

2 把分析库tb表7月份数据全量同步到》 历史库新建的表 tb_201407

3 待2完成后 把源系统 8月份数据的表tb_201408 全量同步到分析库 tb表

以上三个步骤就可以把 需求完成。

3 具体做：

因为这个是每个月月初都要做的，所以准备写个存储过程 在过程里面做创建表的操作 然后定时每月1号执行。这个存储过程的作用是在每月1号在历史库创建新表

下面是过程的内容：

create or replace procedure CREATE_TB_B_FT_BD_TAB_1  is
/*******
用途：每月月初建两个月前edw月数据宽表 表名格式如TB_B_FT_BROADBAND_201407
lww 20140806
*****/
count_var number;
table_name varchar2(40);
begin
count_var:=0;
table_name:='TB_B_FT_BROADBAND_'||to_char(add_months(sysdate,-2),'yyyyMM');--创建的表名
select count(*) into count_var from user_objects t where t.OBJECT_NAME=table_name;--判断是否已经存在
if(count_var)>0
then 
  execute immediate 'drop table '||table_name||' purge' ;--存在drop
else null;
end if;
execute immediate--创建
'create table '||table_name||'
(
  LATN_ID             NUMBER(3),
  MONTH_ID            NUMBER(6),
  PRD_INST_ID         NUMBER(12) not null,
  SERV_NBR            VARCHAR2(30),
  PRD_ID              NUMBER(12),
  CRM_PRD_ID          NUMBER(9),
  PRD_INST_STAS_ID    VARCHAR2(6),
  PRD_INST_NAME       VARCHAR2(100),
  PRD_INST_DESC       VARCHAR2(250),
  INSTALL_DATE        DATE,
  PAY_METH_ID         VARCHAR2(15),
  USER_ACCOUNT        VARCHAR2(50),
  SPEED_ID            NUMBER(8,2),
  SPEED_TYPE          VARCHAR2(15),
  BIND_NBR            VARCHAR2(250),
  IF_PREPAY           NUMBER(1)
)'
;
end;

调用执行此过程 在create table TB_B_FT_BROADBAND_ 的地方 会报 ORA -01031 ： insufficient privileges 权限不足 错误。

但是直接 在sql窗口 执行 create table TB_B_FT_BROADBAND_201407（..） 语句可以创建表。

从网上找的一些解决办法 发现：

1  加上Authid Current_User即可 即

create or replace procedure CREATE_TB_B_FT_BD_TAB_1  Authid Current_User is

就可以创建了。

下面分析下原因：

当前用户的权限是这样的：

这是从 网上找的原因 加了点我自己的理解：

自身拥有一个resource角色，而

resource角色包含以下系统权限：

create cluster

create indextype

create table

create sequence

create type

create procedure

create trigger

其中就有create table 所以单独在SQL中使用没有问题。进入到procedure之后，这个role的角色权限就被剥离掉了。因为该 procedure 存在被其他用户调用生成数据表的可能。所以会在运行时报错权限不足。

这里使用“authid Current_user”将存储过程转化为调用者权限。每次调用存储过程，都是动态根据调用者的权限构成去判定是否有权限，这样就回避了该问题的出现。

总结：

用户拥有的role权限在存储过程是不可用的。实际上，oracle给我们提供了在存储过程中使用role权限的方法：修改存储过程，加入Authid Current_User时存储过程可以使用role权限

2  用dba权限用户给当前创建存储过程的用户赋create table 权限 即可

如下图所示：

这是在iodso用户下创建报错：

用odso用户给iodso用户赋予 create table这个系统权限

创建成功：

4 现在总结下oracle用户的权限

从左边的图可以很直观的看到oracle的用户 有三种权限：对象权限、角色权限、系统权限。

本来想从网上找资料 加上自己理解加工下 记在这里。但是正好找到了 一些比较详细的大师的博客 就在这 直接引用了。

1 关于 系统和对象权限 见乐大师详细的描述：

http://blog.csdn.net/leshami/article/details/5688875

2 关于角色权限（转自http://www.cnblogs.com/BeautyOfCode/archive/2010/09/28/1837239.html）：

一、概述

角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。假定有用户a，b，c为了让他们都拥有权限

1、连接数据库

2、在scott.emp表上select，insert，update

如果采用直接授权操作，则需要进行12次授权。

如果采用角色就可以简化

首先将create session，select on scott.emp，insert on scott.emp，update on scott.emp授予角色，然后将该角色授予a，b，c用户，这样就可以三次授权搞定。角色分为预定义角色和自定义角色两类。

 

二、预定义角色

预定义角色是指Oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba。

1、connect角色

connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有以下系统权限：

alter session

create cluster

create database link

create session

create view

create sequence

 

2、resource角色

resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。

resource角色包含以下系统权限：

create cluster

create indextype

create table

create sequence

create type

create procedure

create trigger

 

3、dba角色

dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system他们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

 

三、自定义角色

顾名思义就是自己定义的角色，根据自己的需要来定义，一般是DBA来建立，如果用的别的用户来建立，则需要具有create role的系统权限，在建立角色时可以指定验证方式（不验证，数据库验证等）。

1、建立角色（不验证）

如果角色是公用的角色，可以采用不验证的方式建立角色

sql>create role 角色名 not identified;

 

2、建立角色（数据库验证）

采用这样的方式时，角色名、口令存放在数据库中，当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。

create role 角色名 identified by tiger;

 

3、角色授权

当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。

给角色授权

给角色授予权限和给用户授权没有太多的区别，但是要注意，系统权限的unlimited tablespace对对象权限with grant option选项是不能授予角色的。

sql>conn system/manager;

sql>grant create session to 角色名 with admin option;

sql>conn scott/tiger;

sql> grant select on scott.emp to 角色名;

sql>grant insert,update,delete on scott.emp to 角色名;

通过上面的步骤，就给角色授权了。

 

4、分配角色给某个用户

一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求用户必须具有grant any role的系统权限。

sql>grant 角色名 to blake with admin option;

因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。

 

5、删除角色

使用drop role，一般是dba来执行的，如用其他用户则要求该用户具有drop any role系统权限。

sql>drop role 角色名;

 

6、显示角色信息

（1）显示所有角色

sql>select * from dba_roles;

 

（2）显示角色具有的系统权限

sql>select privilege,admin_option from role_sys_privs where role='角色名';

 

（3）显示角色具有的对象权限

通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。

 

（4）显示用户具有的角色及默认角色

当以用户的身份连接到数据库时，Oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的橘色。

sql>select granted_role,default_role from dba_role_privs where grantee='用户名';