oracle存储过程的定义者权限与调用者权限

当某个用户定义的存储过程由另一个用户调用时，需要指定存储过程的Definer（定义者权限 )与Invoker（调用者权限）。

一、存储过程的定义者权限

存储过程默认为该权限，也可以在定义存储过程的is|as之前添加authid definer参数，格式如下：

create [or replace] procedure 名称
[(参数1 in | out | in out 类型,
  参数2 in | out | in out 类型, ...)]
  authid definer
  is|as
    --变量声明区间
  begin
     --业务逻辑区间
  exception
     --异常处理
  end;
  /

说明：如果一个存储过程的执行权限为定义者权限，则当另一个用户调用该存储过程时，和存储过程的所有者调用该存储过程是一样的（就相当于是存储过程的所有者调用了该存储过程）。

测试效果：

1、在scott用户下创建一张表t1，并输入数据

create table t1(
    id number(6) primary key,
    name varchar2(20),
    age number(2),
    phone varchar2(20)
);

SQL> select * from t1;

	ID NAME 		       AGE PHONE
---------- -------------------- ---------- --------------------
       101 JACK 			25 13603735588
       102 JERRY			21 13503736645
       103 TOM				32 13037350422
       104 ROSE 			28 13903732266
       105 MARK 			19 15903736633
       106 JIMMI			20 15037355066

6 rows selected.

2、在scott用户下创建一个存储过程，使用默认的definer权限，查看某个用户的姓名和电话

SQL> 
create or replace procedure sp_get_t1
(v_id number,
 v_name out varchar2,
 v_phone out varchar2)
authid definer
is
begin
    select name,phone into v_name,v_phone
    from t1
    where id=v_id;
exception
    when no_data_found then
       dbms_output.put_line('没有该用户信息！');
end;
 15  /

Procedure created.

3、查看该存储过程的信息

该存储过程的执行权限为DEFINER（定义者权限）。

SQL> select object_name,procedure_name,authid from user_procedures where object_name='SP_GET_T1';

OBJECT_NAME		       PROCEDURE_NAME	    AUTHID
------------------------------ -------------------- ------------
SP_GET_T1					                DEFINER

4、调用该存储过程

SQL> 
declare
    v_id t1.id%type;
    v_name t1.name%type;
    v_phone t1.phone%type;
begin
    v_id:=&t1_id;
    sp_get_t1(v_id,v_name,v_phone);
    dbms_output.put_line(
        v_id||'用户的姓名：'||v_name||
        ',电话：'||v_phone);
end;
 12  /
Enter value for t1_id: 103
old   6:     v_id:=&t1_id;
new   6:     v_id:=103;
103用户的姓名：TOM,电话：13037350422

PL/SQL procedure successfully completed.

5、创建一个用户JACK，在该用户下创建表t1，并输入数据

（1）创建用户JACK并授权

SQL> show user;
USER is "SYS"

SQL> 
create user JACK identified by JACK;

User created.

Elapsed: 00:00:00.09

Elapsed: 00:00:00.03
SQL> grant resource,connect to JACK;

Grant succeeded.

（2）以JACK用户身份登录，创建表t1并输入数据

SQL> show user;
USER is "JACK"

SQL> 
create table t1(
    id number(6) primary key,
    name varchar2(20),
    age number(2),
    phone varchar2(20)
  6  );

Table created.

SQL> select * from t1;

	ID NAME 		       AGE PHONE
---------- -------------------- ---------- --------------------
       101 张飞                         25 100801
       102 赵云                         21 200258
       103 关羽                         32 400125

6、以scott用户身份登录，把存储过程sp_get_t1的执行权限授予用户JACK

SQL> show user;
USER is "SCOTT"

SQL> grant execute on sp_get_t1 to JACK;

Grant succeeded.

7、以用户JACK身份登录，调用存储过程sp_get_t1并查看结果

SQL> show user;
USER is "JACK"

SQL> 
declare
    v_id t1.id%type;
    v_name t1.name%type;
    v_phone t1.phone%type;
begin
    v_id:=&t1_id;
    sp_get_t1(v_id,v_name,v_phone);
    dbms_output.put_line(
        v_id||'用户的姓名：'||v_name||
        ',电话：'||v_phone);
end;
 11   12  /
Enter value for t1_id: 103
old   6:     v_id:=&t1_id;
new   6:     v_id:=103;
103用户的姓名：TOM,电话：13037350422

PL/SQL procedure successfully completed.

此时发现，用户JACK调用存储过程sp_get_t1的输出结果和存储过程的定义者scott调用该存储过程的结果是完全相同的。

二、存储过程的调用者权限

可以在定义存储过程的is|as之前添加authid current_user参数，格式如下：

create [or replace] procedure 名称
[(参数1 in | out | in out 类型,
  参数2 in | out | in out 类型, ...)]
  authid current_user
  is|as
    --变量声明区间
  begin
     --业务逻辑区间
  exception
     --异常处理
  end;
  /

说明：如果一个存储过程的执行权限为调用者权限，则当另一个用户调用该存储过程时，就相当于该用户自己创建了一个完全相同的存储过程。

测试效果：

1、在scott用户下创建一个存储过程，使用current_user权限，查看某个用户的姓名和电话

SQL> show user;
USER is "SCOTT"

SQL> 
create or replace procedure sp_get_t1_02
(v_id number,
 v_name out varchar2,
 v_phone out varchar2)
authid current_user
is
begin
    select name,phone into v_name,v_phone
    from t1
    where id=v_id;
exception
    when no_data_found then
       dbms_output.put_line('没有该用户信息！');
end;
 15  /

Procedure created.

2、查看该存储过程的信息

该存储过程的执行权限为CURRENT_USER（调用者权限）。

SQL> select object_name,procedure_name,authid from user_procedures 
where object_name='SP_GET_T1_02';

OBJECT_NAME		       PROCEDURE_NAME	    AUTHID
------------------------------ -------------------- ------------
SP_GET_T1_02					    CURRENT_USER

3、scott用户调用该存储过程

SQL> show user;
USER is "SCOTT"

SQL> 
declare
    v_id t1.id%type;
    v_name t1.name%type;
    v_phone t1.phone%type;
begin
    v_id:=&t1_id;
    sp_get_t1_02(v_id,v_name,v_phone);
    dbms_output.put_line(
        v_id||'用户的姓名：'||v_name||
        ',电话：'||v_phone);
end;
 12  /
Enter value for t1_id: 103
old   6:     v_id:=&t1_id;
new   6:     v_id:=103;
103用户的姓名：TOM,电话：13037350422

PL/SQL procedure successfully completed.

4、以scott用户身份登录，把存储过程sp_get_t1_02的执行权限授予用户JACK

SQL> show user;
USER is "SCOTT"

SQL> SQL> grant execute on sp_get_t1_02 to JACK;

Grant succeeded.

5、以用户JACK身份登录，调用存储过程sp_get_t1_02并查看结果

SQL> show user;
USER is "JACK"

SQL> 
declare
    v_id t1.id%type;
    v_name t1.name%type;
    v_phone t1.phone%type;
begin
    v_id:=&t1_id;
    scott.sp_get_t1_02(v_id,v_name,v_phone);
    dbms_output.put_line(
        v_id||'用户的姓名：'||v_name||
        ',电话：'||v_phone);
end;
 12  /
Enter value for t1_id: 103
old   6:     v_id:=&t1_id;
new   6:     v_id:=103;
103用户的姓名：关羽,电话：400125

PL/SQL procedure successfully completed.

此时发现，用户JACK调用存储过程sp_get_t1_02，就像自己编写了一个存储过程，调用的是自己的t1表。

四、查看存储过程信息

1、查看当前用户所定义的所有存储过程的名称

SQL> select object_name,procedure_name,object_type,deterministic,authid from user_procedures;

OBJECT_NAME		       PROCEDURE_NAME	    OBJECT_TYPE 	DET AUTHID
------------------------------ -------------------- ------------------- --- ------------
SP_EMP_INSERT				    PROCEDURE		NO  DEFINER
SP_SET_EMP_SAL				    PROCEDURE		NO  DEFINER
SP_DISP 					    PROCEDURE		NO  DEFINER
SP_CIRCLE_AREA				    PROCEDURE		NO  DEFINER
SP_UPDATE_EMP_SAL			    PROCEDURE		NO  DEFINER
SP_GET_EMP					    PROCEDURE		NO  DEFINER
SP_UPDATE_EMP_SAL_01		    PROCEDURE		NO  DEFINER
SP_INSERT_T01_FORALL		    PROCEDURE		NO  DEFINER
SP_SCORE_GRADE				    PROCEDURE		NO  DEFINER
SP_DELETE_T1_FORALL			    PROCEDURE		NO  DEFINER
SP_INSERT_T01_FORALL02		    PROCEDURE		NO  DEFINER
SP_UPDATE_T1_FORALL			    PROCEDURE		NO  DEFINER
SP_INSERT_T01				    PROCEDURE		NO  DEFINER
SP_UPDATE_EMP_BULK			    PROCEDURE		NO  DEFINER
SP_UPDATE_T01_FORALL		    PROCEDURE		NO  DEFINER
SP_EMP_ENAME_DEPT			    PROCEDURE		NO  DEFINER
SP_EMP_DEPTNO11 			    PROCEDURE		NO  DEFINER
SP_EMP_DEPTNO				    PROCEDURE		NO  DEFINER
SP_FETCH_EMP				    PROCEDURE		NO  DEFINER
PS_ADD						    PROCEDURE		NO  DEFINER
SP_ADD						    PROCEDURE		NO  DEFINER
SET_SAL 					    PROCEDURE		NO  DEFINER
GET_EMP 					    PROCEDURE		NO  DEFINER
TEST_CREATE					    PROCEDURE		NO  DEFINER
UPDATE_SAL					    PROCEDURE		NO  DEFINER
ADD_SAL 					    PROCEDURE		NO  DEFINER

26 rows selected.

2、查看存储过程的参数信息

SQL> desc get_emp;
PROCEDURE get_emp
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_EMPNO			NUMBER			IN
 V_ENAME			VARCHAR2		OUT
 V_SAL				NUMBER			OUT

3、查看某个存储过程的代码

SQL> select text from user_source where name='SP_UPDATE_EMP_SAL';

TEXT
--------------------------------------------------------------------------------
procedure sp_update_emp_sal
as
    type t_table_emp is table of emp%rowtype
    index by binary_integer;
    v_table_emp t_table_emp;
begin
    update emp set sal=sal*1.1 where deptno=10;
    select * bulk collect into v_table_emp from emp where deptno=10;
    for i in v_table_emp.first..v_table_emp.last loop
        dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
               ',  姓名：'||v_table_emp(i).ename||
               ',  工资：'||v_table_emp(i).sal);
    end loop;
end;

14 rows selected.

五、存储过程应用举例

1、无参数存储过程

把emp表10部分所有人的工资增加10%，并且显示员工编号、姓名及工资信息。

SQL> 
create or replace procedure sp_update_emp_sal
as
    type t_table_emp is table of emp%rowtype
    index by binary_integer;
    v_table_emp t_table_emp;
begin
    update emp set sal=sal*1.1 where deptno=10;
    select * bulk collect into v_table_emp from emp where deptno=10;
    for i in v_table_emp.first..v_table_emp.last loop
        dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
               ',  姓名：'||v_table_emp(i).ename||
               ',  工资：'||v_table_emp(i).sal);
    end loop;
end;
/

Procedure created.

运行存储过程，结果如下：

SQL> call sp_update_emp_sal();
雇员编号：7934,  姓名：MILLER,  工资：1574.21
雇员编号：7782,  姓名：CLARK,  工资：2965.71
雇员编号：7839,  姓名：KING,  工资：6051.21

Call completed.

注意：无参数存储过程调用时要加一对空括号。

2、带输入参数的存储过程

对上例的存储过程进行修改，通过指定一个输入参数，用户在调用时指定某个部门，则把该部门员工的工资增加10%，并显示该部门员工的工号、姓名和工资。代码如下：

SQL> 
create or replace procedure sp_update_emp_sal_01
(v_deptno number)
as
    type t_table_emp is table of emp%rowtype
    index by binary_integer;
    v_table_emp t_table_emp;
begin
    update emp set sal=sal*1.1 where deptno=v_deptno;
    select * bulk collect into v_table_emp from emp where deptno=v_deptno;
    for i in v_table_emp.first..v_table_emp.last loop
        dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
               ',  姓名：'||v_table_emp(i).ename||
               ',  工资：'||v_table_emp(i).sal);
    end loop;
 end;
 /

Procedure created.

调用时指定20部门作为参数，查询结果如下：

SQL> call sp_update_emp_sal_01(20);
雇员编号：7788,  姓名：SCOTT,  工资：2423.63
雇员编号：7876,  姓名：ADAMS,  工资：1334.63
雇员编号：7566,  姓名：JONES,  工资：3603.38
雇员编号：7902,  姓名：FORD,  工资：3633.63
雇员编号：7369,  姓名：SMITH,  工资：971.63

Call completed.

3、指定输出参数

输入一个圆的半径，求圆的面积，代码如下：

SQL> 
create or replace procedure sp_circle_area
(v_radius in number,area out number)
as
begin
    area:=3.1415926*power(v_radius,2);
end;
  7  /

Procedure created.

在代码块中调用该该存储过程，结果如下：

SQL> 
declare
   v_area number(15,8);
begin
   sp_circle_area(4,v_area);
   dbms_output.put_line('圆的面积为：'||v_area);
end;
  7  /
圆的面积为：50.2654816

PL/SQL procedure successfully completed.

4、综合练习

为emp插入一条新记录：

（1）创建一个序列seq_emp，从9001开始编号

SQL> create sequence seq_emp start with 9001;

Sequence created.

（2）创建一个存储过程sp_emp_insert

create or replace procedure sp_emp_insert
(v_ename varchar2,
 v_job varchar2 default 'SALESMAN',
 v_mgr number default 7698,
 v_hiredate date default sysdate,
 v_sal number default 2000,
 v_comm number default 0,
 v_deptno number default 40)
as
begin
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    values(seq_emp.nextval,
           v_ename,
           v_job,
           v_mgr,
           v_hiredate,
           v_sal,
           v_comm,
           v_deptno);
end;
/

执行存储过程：

SQL> call sp_emp_insert('Jerry');

Call completed.

Elapsed: 00:00:00.02
SQL> call sp_emp_insert('John',v_sal=>1800);

Call completed.

Elapsed: 00:00:00.00
SQL> call sp_emp_insert('Merry',v_hiredate=>sysdate-10,v_sal=>2100);

Call completed.

Elapsed: 00:00:00.01
SQL> select * from emp where empno>9000;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ------
      9001 Jerry      SALESMAN	      7698 2020-02-17 01:01:22	     2000	   0	     40
      9002 John       SALESMAN	      7698 2020-02-17 01:02:39	     1800	   0	     40
      9003 Merry      SALESMAN	      7698 2020-02-07 01:03:40	     2100	   0	     40

关于ORACLE账号的权限问题，一般分为两种权限：

系统权限: 允许用户执行特定的数据库动作，如创建表、创建索引、创建存储过程等

对象权限: 允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等

像这种查看存储过程定义的权限为对象权限，但是我们还是首先来看看关于存储过程的系统权限吧：

PRIVILEGE NAME PROPERTY

---

 -140 CREATE PROCEDURE                                  0 

 -141 CREATE ANY PROCEDURE                              0 

 -142 ALTER ANY PROCEDURE                               0 

 -143 DROP ANY PROCEDURE                                0 

 -144 EXECUTE ANY PROCEDURE                             0 

 -241 DEBUG ANY PROCEDURE                               0 

如上所示，关于存储过程的系统权限一般有六种： CREATE PROCEDURE、CREATE ANY PROCEDURE、 ALTER ANY PROCEDURE、DROP ANY PROCEDURE、 EXECUTE ANY PROCEDURE、DEBUG ANY PROCEDURE. 那么关于存储过程的对象权限又有那些呢? 如下例子所示，在用户ESCMUSER下创建存储过程PROC_TEST

CREATE OR REPLACE PROCEDURE ESCMUSER.PROC_TEST

AS

BEGIN

DBMS_OUTPUT.PUT_LINE(‘It is only test’);

END;
使用system用户创建用户TEMP，如下所示

SQL> create user temp identified by temp;

User created.

SQL> grant connect,resource to temp;

Grant succeeded.
在用户ESCMUSER下将存储过程PROC_TEST的所有权限授予给用户TEMP。 那么我们发现存储过程的对象权限只有EXECUTE、DEBUG权限

SQL> COL GRANTEE FOR A12;

SQL> COL TABLE_NAME FOR A30;

SQL> COL GRANTOR FOR A12;

SQL> COL PRIVILEGE FOR A8;

SQL> SELECT * FROM USER_TAB_PRIVS_MADE WHERE GRANTEE=’TEMP’;

GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE

---

TEMP PROC_TEST ESCMUSER DEBUG NO NO

TEMP PROC_TEST ESCMUSER EXECUTE NO NO

SQL>

将存储过程PORC_TEST的权限从TEMP用户收回，然后授予用户TEMP关于存储过程PROC_TEST的DEBUG权限

SQL>REVOKE ALL ON PROC_TEST FROM TEMP;

SQL>GRANT DEBUG ON PROC_TEST TO TEMP;
那么TEMP用户此时执行存储过程报权限不足

SQL> SET SERVEROUT ON;

SQL> EXEC escmuser.proc_test;

begin escmuser.proc_test; end;

ORA-06550: line 2, column 16:

PLS-00904: insufficient privilege to access object ESCMUSER.PROC_TEST

ORA-06550: line 2, column 7:

PL/SQL: Statement ignored
此时，如果修改存储过程PROC_TEST就会ORA-01031权限不足问题。但是你可以在PL/SQL Developer工具或使用下面视图查看存储过程的定义。如下所示。

SELECT * FROM ALL_SOURCE WHERE NAME=’PROC_TEST’

所以，只需要授予存储过程的DEBUG权限给某个用户，就可以实现只授予用户查看存储过程定义的权限，而限制用户修改、执行存储过程。从而达到只授权用户查看存储过程定义的权限。不过这样实现，总让我感觉有点怪怪的。

原文链接:http://www.cnblogs.com/kerrycode/p/4580256.html

【编辑推荐】

Oracle数据库初学者开场篇
通过Oracle来辅助MySQL数据问题的恢复
Oracle免费的便捷Web应用开发框架二则
IOUG主席谈Oracle云服务战略和大数据用途
Oracle 数据库灾难性环境下恢复实例

安卓7.0文件存储权限变更
相关网站
Setting Up File Sharing
首先，你要定义一个FileProvider在你的App的Manifest文件中，这个文件中有一个特殊的一栏就是authority，它是用于生成content URIs，下面是provider的形式

<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.myapp">
    <application
        ...>
        <provider
            android:name="android.support.v4.content.FileProvider"
            android:authorities="com.example.myapp.fileprovider"
            android:grantUriPermissions="true"
            android:exported="false">
            <meta-data
                android:name="android.support.FILE_PROVIDER_PATHS"
                android:resource="@xml/filepaths" />
        </provider>
        ...
    </application>
</manifest>

在这个例子中android:authorities是 FileProvider生成content URIs必备的权限属性
在这个例子中权限是com.example.myapp.fileprovider，对于我们自己的应用通常是由你的包名+字符串“fileprovider”，想查看更改关于权限值，你可以点击这里
（其中android:grantUriPermissions必须为true，android:exported必须为false）
是provider的子元素，它指向你想分享的一个XML文件，这个resource属性就是分享文件的路径和文件名，

Specify Sharable Directories
一旦你添加了 FileProvider在你的app得Manifest中，你就得添加一个相应的文件夹，这个文件中中包含了你想要分享的文件，通过在你的工程res目录下创建xml文件，添加filepaths.xml文件，下面是一个样例

<paths>
    <files-path path="images/" name="myimages" />
</paths>

标签分享了一个文件在你app的内部存储文件夹中，path属性分享了一个在files/目录下的一个images/子目录，name属性告诉 FileProvider给文件files/images添加一个路径myimages给 content URIs。
这个元素有很多子类，不同的标签分享了不同的文件，除了标签外，你也可以用标签，它是分享外置存储文件夹的， 标签用于分享内置缓存文件夹的。
下面来看看他们的类型。

<paths xmlns:android="http://schemas.android.com/apk/res/android">
    <files-path name="my_images" path="images/"/>
    ...
</paths>

标签中必须包含一个或者多个子元素

<files-path name="name" path="path" />

代表文件在你的应用内置存储区域files/下的子目录，这种类型和

Context.getFilesDir().

所返回的路径一样。

<cache-path name="name" path="path" />

这种表示在你应用App内置存储区域下的缓存文件，它和 getCacherDir()返回的路径一样。

<external-path name="name" path="path" />

这种代表在你App的外置存储的根目录下的文件，它和Context#getExternalFilesDir(String) Context.getExternalFilesDir(null).返回的路径一样。

<external-cache-path name="name" path="path" />

这种代表在你app的外置缓存区域根目录下的文件，它和Context.getExternalCacheDir().返回的路径一样。
以上的标签都有相同的属性name和path

其中name表示：一个URI路径，为了安全考虑，这个值隐藏在你要分享的子目录名字下面，这个子目录的名字就包含路径属性。

其中path表示：你要分享的子目录，当name属性是一个URI路径时，这个path的值实际上就是子目录的名字。

你必须指定一个子元素的path给每个你想分享的内容RUIs,例如

<paths xmlns:android="http://schemas.android.com/apk/res/android">
    <files-path name="my_images" path="images/"/>
    <files-path name="my_docs" path="docs/"/>
</paths>

这些XML元素指定了两个文件夹。

Generating the Content URI for a File
给一个文件生成Content URI

为了分享一个文件给任何app使用，你的App必须生成 content URI，
例如：如果你的App提供了一个文件给其他一个有com.mydomain.fileprovider的权限的应用通过FileProvider使用，为了获得URIs给文件default_image.jpg在你的内存存储子目录下的images/，你就得添加下面的代码。

File imagePath = new File(Context.getFilesDir(), "images");
File newFile = new File(imagePath, "default_image.jpg");
Uri contentUri = getUriForFile(getContext(), "com.mydomain.fileprovider", newFile);

通过上面的代码可以得到这样的结果，getUriForFile() 返回的路径是：
content://com.mydomain.fileprovider/my_images/default_image.jpg。

Granting Temporary Permissions to a URI
授予临时权限给URI
通过方法 Context.grantUriPermission(package, Uri, mode_flags) 的形式，添加mode_flags授予临时权限给URI，你可以设置为：

FLAG_GRANT_READ_URI_PERMISSION
或者
 FLAG_GRANT_WRITE_URI_PERMISSION
 或者一起使用

也可以说通过revokeUriPermission() 撤销权限
然后通过Intent的setData()方法将content URI 设置进去，也可以通过 Intent.setFlags() 的方法将FLAG_GRANT_READ_URI_PERMISSION或者 FLAG_GRANT_WRITE_URI_PERMISSION，或者一起，设置为赋予权限。
最后将Intent传递给其他app，经常使用的是setResult()方法。

另外，权限赋予在一个Intent中仍然有效，只要Activity栈中activity是处于激活状态的。当activity结束了，权限赋予会自动移除。