Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to grant users permission to view the definition of stored procedures only in oracle, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Generally speaking, there are two kinds of permissions for ORACLE account:

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

Object permissions: allow users to manipulate specific objects, such as reading views, updating certain columns, executing stored procedures, etc.

The permissions defined for viewing stored procedures like this are object permissions, but let's first take a look at the system permissions for stored procedures:

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, DEBUG ANY PROCEDURE. What about the object permissions for stored procedures? As shown in the following example, create a stored procedure PROC_TEST under user ESCMUSER

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

Create a user TEMP using the system user, as shown below

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

All permissions for the stored procedure PROC_TEST are granted to the user TEMP under user ESCMUSER. Then we find that the object permissions of stored procedures are only EXECUTE and DEBUG permissions.

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 >

Withdraw the permissions of the stored procedure PORC_TEST from the TEMP user, and then grant the user TEMP DEBUG permissions on the stored procedure PROC_TEST

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

Then the TEMP user reported insufficient permissions to execute the stored procedure at this time.

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, it will cause the problem of insufficient ORA-01031 permissions. However, you can view the definition of the stored procedure in the PL/SQL Developer tool or using the view below. As shown below.

SELECT * FROM ALL_SOURCE WHERE NAME='PROC_TEST'

Therefore, only by granting the DEBUG permission of the stored procedure to a user, you can only grant the user the right to view the definition of the stored procedure, and restrict the user from modifying and executing the stored procedure. Thus, only the user is authorized to view the stored procedure definition. But it always makes me feel a little weird when it comes to this.

Thank you for reading this article carefully. I hope the article "how to only grant users permission to view stored procedure definitions in oracle" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.