How to grant users the permission to view Stored Procedure definitions and stored procedure Permissions

How to grant users the permission to view Stored Procedure definitions and stored procedure Permissions

A user asked me how to grant a user the permission to view the definition of certain stored procedures, rather than letting the user modify or execute the stored procedure. This seems a simple problem, but it has never encountered such a requirement. It took some time to sort out and summarize clearly.

ORACLE account permissions are generally divided into two types:

System permissions:Allows users to perform specific database actions, such as creating tables, creating indexes, and creating stored procedures.

Object permission:Allows users to manipulate certain objects, such as reading views, updating certain columns, and executing stored procedures.

For example, the permission defined by the stored procedure is the object permission, but we should first look at the system permission of the stored procedure:

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 

As shown above, there are generally six system permissions for stored procedures: create procedure, create any procedure, alter any procedure, drop any procedure, execute any procedure, and debug any procedure. what are the object permissions of stored procedures? The following example shows how to create the Stored Procedure PROC_TEST under the user ESCMUSER.

CREATE OR REPLACE PROCEDURE ESCMUSER.PROC_TEST  AS  BEGIN   DBMS_OUTPUT.PUT_LINE('It is only test');  END; 

Use the system user to create the user TEMP, as shown below:

SQL> create user temp identified by temp;  User created.  SQL> grant connect,resource to temp;  Grant succeeded. 

Grant all permissions of the stored procedure PROC_TEST to the user TEMP under the user ESCMUSER. Then we found that the object permission of the stored procedure is only EXECUTE and 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> 

 

Revoke the permission of the stored procedure PORC_TEST from the TEMP user, and then grant the user TEMP the DEBUG permission on the Stored Procedure PROC_TEST.

SQL>REVOKE ALL ON PROC_TEST FROM TEMP;  SQL>GRANT DEBUG ON PROC_TEST TO TEMP; 

The TEMP user reports that the stored procedure has insufficient permissions.

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 

At this point, if you modify the Stored Procedure PROC_TEST, there will be a problem with insufficient ORA-01031 permissions. However, you can use the PL/SQL Developer tool or the following view to view the definition of the stored procedure. As shown below.

SELECT * FROM ALL_SOURCE WHERE NAME='PROC_TEST'

Therefore, you only need to grant the DEBUG permission of the stored procedure to a user, so that you can only grant the user the permission to view the stored procedure definition, and restrict the user to modify and execute the stored procedure. In this way, only authorized users can view Stored Procedure definitions. However, this implementation always makes me feel a little strange.

The above section describes how to grant users the permission to view the definition of stored procedures. I hope this will be helpful for everyone's learning.