- 浏览: 788721 次
- 性别:
- 来自: 广州
文章分类
最新评论
在数据库系统中存储过程是必不可少的利器,存储过程是预先编译好的为实现一个复杂功能的一段Sql语句集合。它的优点我就不多说了,说一下我碰到的问题吧。我在项目开发的过程中需要用存储过程来实现一个功能,其中涉及到判断一张表是否已经建立,没有建立就由存储过程来建立这张表。
CREATE OR REPLACE PROCEDURE TestProc
IS
flag number;
BEGIN
select count(*) into flag from all_tables where table_name='TEMP3';
if (flag=0) then
execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
else
execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';
end if;
END ;
写这段存储过程比较简单,在测试执行的过程中,系统出现如下提示:
ora -01031 :权限不足
从错误提示我们定位到错误,发现存储过程在执行 Create table语句时,权限不足。我尝试着把存储过程改成匿名存储过程在PL/SQL中执行,语句既然通过了。这说明这段语句没有问题,问题出现在执行存储过程中。我使用的是DBA帐号登录系统,按理应该不存在权限不足的问题呀。问题出现再哪里呢?通过上网查阅资料,发现Oracle对于执行存储过程有和Sql-Server不一样的规定,这个规定造成了执行建表语句权限不足。
Oracle规定,在默认的情况下,在调用存储过程用户的角色不起作用,即在执行存储过程时只有Public权限。所以在调用Create table时,会有权限不足的提示。
存储过程分为两种,即DR(Definer's Rights ) Procedure和IR(Invoker's Rights ) Procedure。为什么会有两种存储过程呢?其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table(),当用户hrch调用时,即删除用户hrch下的表tar_table;如果是另一个用户scott调用呢?是删除用户scott下的tar_table表呢,还是删除用户hrch下的tar_table呢?另外,如果存储过程中包含建表语句,不管是用户hrch还是用户scott调用都会失败,因为Public没有建表权限,除非为Public grant建表权限。所以,存储过程的调用者会面临两个问题:
存储过程的名称解析环境
存储过程的执行权限
这两个问题可以在定义存储过程时,通过指定AUTHID 属性,即定义DR Procedure 和IR Procedure来解决。
DR Procedure
1、定 义
CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID DEFINER as
...
BEGIN
...
END DEMO;
2、名称解析环境为定义该存储过程的用户所在的Schema。
3、执行该存储过程时只有Public权限。
IR Procedure
1、定 义
CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID CURRENT_USER as
...
BEGIN
...
END DEMO;
2、名称解析环境为调用该存储过程的用户所在的Schema。
3、执行该存储过程时拥有调用者的所有权限,即调用者的Role是有效的。
我们碰到的问题只需要使用IR Procedure就能很快解决问题啦,我把代码修改为如下:
CREATE OR REPLACE PROCEDURE TestProc AUTHID current_user
IS
flag number;
BEGIN
select count(*) into flag from all_tables where table_name='TEMP3';
if (flag=0) then
execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
else
execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';
end if;
END ;
执行存储过程,顺利通过。
CREATE OR REPLACE PROCEDURE TestProc
IS
flag number;
BEGIN
select count(*) into flag from all_tables where table_name='TEMP3';
if (flag=0) then
execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
else
execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';
end if;
END ;
写这段存储过程比较简单,在测试执行的过程中,系统出现如下提示:
ora -01031 :权限不足
从错误提示我们定位到错误,发现存储过程在执行 Create table语句时,权限不足。我尝试着把存储过程改成匿名存储过程在PL/SQL中执行,语句既然通过了。这说明这段语句没有问题,问题出现在执行存储过程中。我使用的是DBA帐号登录系统,按理应该不存在权限不足的问题呀。问题出现再哪里呢?通过上网查阅资料,发现Oracle对于执行存储过程有和Sql-Server不一样的规定,这个规定造成了执行建表语句权限不足。
Oracle规定,在默认的情况下,在调用存储过程用户的角色不起作用,即在执行存储过程时只有Public权限。所以在调用Create table时,会有权限不足的提示。
存储过程分为两种,即DR(Definer's Rights ) Procedure和IR(Invoker's Rights ) Procedure。为什么会有两种存储过程呢?其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table(),当用户hrch调用时,即删除用户hrch下的表tar_table;如果是另一个用户scott调用呢?是删除用户scott下的tar_table表呢,还是删除用户hrch下的tar_table呢?另外,如果存储过程中包含建表语句,不管是用户hrch还是用户scott调用都会失败,因为Public没有建表权限,除非为Public grant建表权限。所以,存储过程的调用者会面临两个问题:
存储过程的名称解析环境
存储过程的执行权限
这两个问题可以在定义存储过程时,通过指定AUTHID 属性,即定义DR Procedure 和IR Procedure来解决。
DR Procedure
1、定 义
CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID DEFINER as
...
BEGIN
...
END DEMO;
2、名称解析环境为定义该存储过程的用户所在的Schema。
3、执行该存储过程时只有Public权限。
IR Procedure
1、定 义
CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID CURRENT_USER as
...
BEGIN
...
END DEMO;
2、名称解析环境为调用该存储过程的用户所在的Schema。
3、执行该存储过程时拥有调用者的所有权限,即调用者的Role是有效的。
我们碰到的问题只需要使用IR Procedure就能很快解决问题啦,我把代码修改为如下:
CREATE OR REPLACE PROCEDURE TestProc AUTHID current_user
IS
flag number;
BEGIN
select count(*) into flag from all_tables where table_name='TEMP3';
if (flag=0) then
execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
else
execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';
end if;
END ;
执行存储过程,顺利通过。
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1238Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1108RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
oracle 分析函数
2013-04-09 23:25 1185分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合 ... -
批量执行 bulk collect与forall用法
2013-04-08 23:49 1400BULK COLLECT 子句会批量检 ... -
pl/sql集合类型
2013-03-26 10:12 1578--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1113表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1620About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8495转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2932insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30891. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2915LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6445ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1440I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5647用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1398Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1399Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2729RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3067空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1539我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1508闪回事务查询有别于闪回查询的特点有以下3个: (1) ...
相关推荐
首先,`Oracle存储过程`是一种预编译的SQL和PL/SQL代码集合,可以被多次调用以执行特定的任务。它们提高了性能,降低了网络流量,并简化了数据库管理。在存储过程中调用外部脚本可以扩展Oracle的功能,比如自动化...
Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中,为了保护敏感代码或数据,开发人员有时会选择对存储过程进行加密。然而,当需要查看、调试或恢复这些加密的存储过程...
Oracle存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列SQL语句和PL/SQL块,形成可重复使用的代码单元。这篇博客“oracle存储过程-帮助文档”可能提供了关于如何创建、调用和管理Oracle存储过程...
Oracle存储过程是数据库管理系统Oracle中的一个关键特性,它允许开发者编写一组预编译的SQL和PL/SQL语句,以实现特定的业务逻辑或数据库操作。这篇教程将深入讲解Oracle存储过程的各个方面,帮助你从基础到高级全面...
### Oracle存储过程学习经典 #### Oracle存储过程基础知识与实践 Oracle存储过程是SQL与PL/SQL结合的强大功能,用于封装复杂的数据操作逻辑于数据库内部,从而实现高效的事务处理和数据管理。以下是对Oracle存储...
Oracle存储过程是数据库编程的重要组成部分,它允许程序员在数据库中执行复杂的业务逻辑和数据操作。这个"ORACLE存储过程学习源码"集合包含了从基础到高级的30个示例,是学习和掌握Oracle存储过程的理想资源。下面,...
本篇文章将深入探讨如何在Oracle存储过程中使用临时表,包括会话级临时表和事务级临时表。 ### 会话级临时表 会话级临时表(Session-Level Temporary Tables)只在创建它的会话内可见,并且在会话结束时自动删除。...
### Oracle存储过程学习文档知识点详解 #### 一、Oracle存储过程概述 **1.1 存储过程定义:** Oracle存储过程是一种存储在数据库中的PL/SQL代码块,它可以接收输入参数并返回输出参数。存储过程能够执行复杂的业务...
Oracle存储过程是数据库管理系统Oracle中的一个重要特性,它允许开发者编写一系列的SQL和PL/SQL语句,形成一个可重复使用的程序单元。这个“Oracle存储过程案例”提供了丰富的学习材料,帮助用户从入门到精通掌握...
这个"Oracle存储过程学习经典(实例)"资源显然是为初学者设计的,旨在帮助他们掌握如何创建、执行和管理存储过程。 存储过程在数据库管理中扮演着关键角色,它可以提升系统的性能,通过减少网络流量和提供预编译的...
- 执行存储过程需要相应的执行权限,如`EXECUTE`或`EXECUTE ANY PROCEDURE`权限。 - **示例**: 授予用户执行存储过程的权限: ```sql GRANT EXECUTE ON MY_PROCEDURE TO Jelly; ``` - **调用示例**: ```sql ...
Oracle存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列SQL和PL/SQL语句,形成一个可重用的程序单元。存储过程在数据库中预先编译并存储,这样在执行时能提高效率,因为它避免了每次调用时的重新...
在Windows上,我们需要创建一个批处理文件(.bat文件)来执行调用Oracle存储过程的命令。这通常涉及到使用SQL*Plus或者ODBC连接到Oracle数据库,执行`EXEC`语句来运行存储过程。例如,一个简单的批处理文件可能包含...
Oracle存储过程是数据库管理系统Oracle中的一个重要特性,它允许开发者编写一系列复杂的SQL和PL/SQL语句,形成可重用的代码块。这些代码块可以执行数据处理、事务控制、错误处理等多种任务,极大地提高了数据库应用...
Oracle存储过程是数据库管理系统Oracle中的一种重要特性,它允许开发者编写一组SQL和PL/SQL语句,形成一个可重用的程序单元。存储过程的概念类似于高级编程语言中的模块,通过参数传递来实现数据的交互,体现了模块...
Oracle存储过程是数据库管理系统Oracle中的一个重要特性,它允许程序员编写SQL和PL/SQL代码块,以便在需要时执行。这些过程可以包含复杂的业务逻辑,数据处理和计算,从而提高应用程序的效率和性能。然而,出于安全...
使用`CREATE OR REPLACE PROCEDURE`语句更新存储过程时,原有执行权限不会丢失。 10. **注意事项** - 数据表别名不应使用`as`。 - `SELECT`后跟字段必须用`INTO`指定变量,除非使用游标。 - `SELECT...INTO...`...
### Oracle存储过程、函数和包的关键知识点 #### 1. 存储过程和函数的认识 - **定义**:存储过程和函数是特定类型的PL/SQL块,它们被存储在数据库中,作为命名的对象存在。 - **命名存储**:与普通的PL/SQL块不同,...