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

This article shares with you the content of a sample analysis of defined user permissions and caller permissions in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Definer permission: the definer permission refers to using the permissions of its owner, not the current user, to execute the process. Therefore, you can restrict the database operations performed by users and allow them to access data only by running procedures and functions that are authorized by the definer. The default permission for creating procedures, functions, and packages is the definer permission.

Caller permissions: executes the process with the current user permissions in the current user mode. In other words, the caller's permission process is not bound to a particular user or mode. Caller permissions programs make it easy for application developers to centralize application logic, even if the underlying data is divided between users and schemas. You need to explicitly use AUTHID CURRENT_USER to define the caller procedure at creation time.

(note: pay attention to the different users who use sqlplus when reading the following case)

The specific demonstration is as follows:

1. Create two test users, wjq1 and wjq2, and authorize them respectively

SYS@seiang11g > create user wjq1 identified by wjq1 default tablespace seiang

User created.

SYS@seiang11g > create user wjq2 identified by wjq2 default tablespace seiang

User created.

SYS@seiang11g > grant connect,resource to wjq1,wjq2

Grant succeeded.

2. Switch to user wjq1 and create two procedures, proc_definer and proc_invoker

Procedure 1: the definer permission, which is the default permission for the creation process

WJQ1@seiang11g > create or replace procedure proc_definer is

2 begin

3 dbms_output.put_line ('Current User:' | | sys_context (' userenv','current_user'))

4 dbms_output.put_line ('Session User:' | | sys_context (' userenv','session_user'))

5 dbms_output.put_line ('Current Schema:' | | sys_context (' userenv','current_schema'))

6 end proc_definer

7 /

Procedure created.

Procedure 2: use user permissions

WJQ1@seiang11g > create or replace procedure proc_invoker authid current_user is

2 begin

3 dbms_output.put_line ('Current User:' | | sys_context (' userenv','current_user'))

4 dbms_output.put_line ('Session User:' | | sys_context (' userenv','session_user'))

5 dbms_output.put_line ('Current Schema:' | | sys_context (' userenv','current_schema'))

6 end proc_invoker

7 /

Procedure created.

3. View the permissions of the two procedures

WJQ1@seiang11g > select object_name,procedure_name,authid from user_procedures

2 where object_name like'% PROC%'

OBJECT_NAME PROCEDURE_NAME AUTHID

-

PROC_INVOKER CURRENT_USER

PROC_DEFINER DEFINER

4. Execute the process of definer permissions and user permissions under the user wjq1.

WJQ1@seiang11g > set serveroutput on

WJQ1@seiang11g >

WJQ1@seiang11g > exec proc_definer

Current User:WJQ1

Session User:WJQ1

Current Schema:WJQ1

PL/SQL procedure successfully completed.

WJQ1@seiang11g > exec proc_invoker

Current User:WJQ1

Session User:WJQ1

Current Schema:WJQ1

PL/SQL procedure successfully completed.

5. Authorize the two processes created by user wjq1 to user wjq2

WJQ1@seiang11g > grant execute on proc_definer to wjq2

Grant succeeded.

WJQ1@seiang11g > grant execute on proc_invoker to wjq2

Grant succeeded.

6. Call two procedures under the user wjq2, and the results show that under the permission of the caller, the program executes under the permission of the current user.

WJQ2@seiang11g > set serveroutput on

WJQ2@seiang11g > exec wjq1.proc_definer

Current User:WJQ1

Session User:WJQ2

Current Schema:WJQ1

PL/SQL procedure successfully completed.

WJQ2@seiang11g > exec wjq1.proc_invoker

Current User:WJQ2

Session User:WJQ2

Current Schema:WJQ2

PL/SQL procedure successfully completed.

Through the simple demonstration above, we already have a certain understanding and understanding of the definer permissions and caller permissions, but only a preliminary understanding of the actual cases encountered in the production environment is still a little confusing. below through two actual cases to analyze the definer permissions and caller permissions, a more in-depth understanding of how to use them.

In stored procedures, we often encounter such a scenario: user A has a stored procedure (or function body, package body) Proc, and the object obj is referenced in the middle of the procedure. When compiling the stored procedure, user An is required to have the permission of the object obj. If not, the system reports a compilation error. When the compilation is successful, user An assigns user B the right to execute the execute stored procedure Proc. But user B does not necessarily have permission to use the object obj, so the question is, can user B successfully execute the stored procedure Proc at this time?

Let's verify it through experiments:

Then, for the above example, in addition to the permissions of the connect and resource roles for users wjq1 and wjq2, for the purpose of the experiment, we give the system permissions of select any dictionary to the user wjq1.

SYS@seiang11g > grant select any dictionary to wjq1

Grant succeeded.

The system permissions of select any dictionary mean that users can access object data in the view at the data dictionary view level.

WJQ1@seiang11g > select count (*) from dba_objects

COUNT (*)

-

86993

WJQ1@seiang11g > create or replace procedure proc_wjq1 is

2 i number

3 begin

4 select count (*) into i from dba_objects

5 dbms_output.put_line (to_char (I))

6 end

7 /

Procedure created.

WJQ1@seiang11g > set serveroutput on

WJQ1@seiang11g >

WJQ1@seiang11g > exec proc_wjq1

86994

PL/SQL procedure successfully completed.

You can see from the above that the user wjq1 granted to select any dictionary can access the dba_objects view. At the same time, the stored procedure proc_wjq1 can also perform compilation operations.

Case 1: definer permissions

Followed by the above experiment, for wjq2 users, only execute the proc_wjq1 stored procedure under the wjq1 user permission, but no access to the dba_objects view, see the actual effect.

User wjq2 has only basic connect and resource permissions. Authorize the execution rights of the proc_wjq1 process to wjq2

WJQ1@seiang11g > grant execute on proc_wjq1 to wjq2

Grant succeeded.

After that, check the execution of the process proc_wjq1 under the wjq2 user

WJQ2@seiang11g > select count (*) from dba_objects

Select count (*) from dba_objects

*

ERROR at line 1:

ORA-00942: table or view does not exist

The wjq2 user does not have dba_objects permission. An error must be reported in the display access.

WJQ2@seiang11g > exec wjq1.proc_wjq1

86994

PL/SQL procedure successfully completed.

The result is obvious that wjq2 does not have access to dba_objects, but because it has permission to execute proc_wjq1, when executing proc_wjq1, it can also access dba_objects in the method. Obviously, wjq2 applies the permissions of the wjq1 user to the dba_objects, that is, the permissions of the object definer, on the execution of the proc_wjq1.

In order to further prove the accuracy of the results, the following experimental changes will be carried out to demonstrate

When the definer permission is lost, even if the caller has permission, it is useless (that is, the wjq1 user does not have access to dba_objects, the wjq2 user has access to dba_objects)

The select any dictionary permission on the wjq1 user is reclaimed, and the wjq1 permission on the dba_objects object disappears; then, the wjq2 user is given select any dictionary permission so that wjq2 can access dba_objects

SYS@seiang11g > revoke select any dictionary from wjq1

Revoke succeeded.

WJQ1@seiang11g > select count (*) from dba_objects

Select count (*) from dba_objects

*

ERROR at line 1:

ORA-00942: table or view does not exist

SYS@seiang11g >

SYS@seiang11g > grant select any dictionary to wjq2

Grant succeeded.

WJQ2@seiang11g > select count (*) from dba_objects

COUNT (*)

-

86994

WJQ2@seiang11g > exec wjq1.proc_wjq1

BEGIN wjq1.proc_wjq1; END

*

ERROR at line 1:

ORA-06550: line 1, column 12:

PLS-00905: object WJQ1.PROC_WJQ1 is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Through the above experimental results, it is found that wjq2 users have access to dba_objects objects and also have the right to execute proc_wjq1, but they report an error during execution and think that the object is invalid. Why is that? The only reason is that the wjq1 user loses permission to the dba_objects object, while wjq2 uses the permission of dba_objects when calling proc_wjq1.

The lab in case 1 above introduces the "definer permissions" of the permission configuration used by Oracle in stored procedures. To put it simply, when executing a program body (stored procedures, functions, packages, etc.), the permission system used within the method body is the permission system of the current program body definition, regardless of the user who called the method. No matter which user executes the stored procedure proc_wjq1, the authority system is the permission of wjq1, the definer of the stored procedure.

The definer permission is the default permission selection method used by Oracle, which is very convenient when using it. Callers only need to have simple object execution permissions, regardless of whether they have permission to access the objects used in the method.

A pair of definer permissions in the case are analyzed through experiments, and the meaning of caller permissions is relatively easy to understand. The caller authority system is when the method body is executed, the permissions used are judged according to the caller authority system. For the execution of a method, the caller must have permission not only to execute the method, but also to use objects in the method.

The following case 2 makes a detailed analysis of the caller's permissions through experiments:

Case 2: caller permissions

Then the experimental environment of case 1 above. Note that the wjq1 user does not have select any dictionary privileges at this time, while wjq2 does.

WJQ1@seiang11g > create or replace procedure proc_wjq1_1 is

2 i number

3 begin

4 select count (*) into i from dba_objects

5 dbms_output.put_line (to_char (I))

6 end

7 /

Warning: Procedure created with compilation errors.

WJQ1@seiang11g >

WJQ1@seiang11g > select name,line,text from user_errors

NAME LINE TEXT

-

PROC_WJQ1_1 4 PL/SQL: ORA-00942: table or view does not exist

PROC_WJQ1_1 4 PL/SQL: SQL Statement ignored

Check the error message, or because wjq1 does not have permission to dba_objects, so the creation process fails.

At this point, if you add the authid current_user keyword to the method definition, you can change the stored procedure to caller permissions.

WJQ1@seiang11g > create or replace procedure proc_wjq1_1 authid current_user is

2 i number

3 begin

4 select count (*) into i from dba_objects

5 dbms_output.put_line (to_char (I))

6 end

7 /

Warning: Procedure created with compilation errors.

WJQ1@seiang11g >

WJQ1@seiang11g > select name,line,text from user_errors

NAME LINE TEXT

-

PROC_WJQ1_1 4 PL/SQL: ORA-00942: table or view does not exist

PROC_WJQ1_1 4 PL/SQL: SQL Statement ignored

Obviously, the error is still reported because the wqj1 user does not have permission to access dba_objects. After all, no matter what the system is, the current creation process of wjq1 without object permission will not be successful. However, in order for the experiment to be successful, it is necessary for wjq1 to create the proc_wjq1_1 process smoothly.

SYS@seiang11g > grant select any dictionary to wjq1

Grant succeeded.

WJQ1@seiang11g > create or replace procedure proc_wjq1_1 authid current_user is

2 i number

3 begin

4 select count (*) into i from dba_objects

5 dbms_output.put_line (to_char (I))

6 end

7 /

Procedure created.

WJQ1@seiang11g >

WJQ1@seiang11g > grant execute on proc_wjq1_1 to wjq2

Grant succeeded.

Switch to the wjq2 user and notice that it has select any dictionary permissions at this time.

WJQ2@seiang11g > select count (*) from dba_objects

COUNT (*)

-

86995

WJQ2@seiang11g > exec wjq1.proc_wjq1_1

86995

PL/SQL procedure successfully completed.

At this point, the wjq2 execution process is successful. Because both wjq1 and wjq2 have select any dictionary permissions at this time, they will succeed even under caller permissions. At this point, what happens if you revoke the permissions on the wjq2?

SYS@seiang11g > revoke select any dictionary from wjq2

Revoke succeeded.

WJQ2@seiang11g > select count (*) from dba_objects

Select count (*) from dba_objects

*

ERROR at line 1:

ORA-00942: table or view does not exist

WJQ2@seiang11g > exec wjq1.proc_wjq1

BEGIN wjq1.proc_wjq1; END

*

ERROR at line 1:

ORA-06550: line 1, column 12:

PLS-00905: object WJQ1.PROC_WJQ1 is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

At this point, you can see the difference in caller permissions. Wjq1 always has the permission of dba_objects, and wjq2 is later revoked the permission of select any dictionary. If it is the definer permission, there is no problem for wjq2 to call proc_wjq1_1. However, an error is reported at this time, indicating that the caller permission of wjq2 is used here.

Through the above two cases, through experiments to analyze the rights of the definer and the user, we must have a deeper understanding and understanding, but very often, we will use stored procedure Procedure to achieve some scripting functions. Through Procedure to achieve some database-related maintenance, development work, can greatly improve our daily work efficiency. In the following database operation and maintenance process, there is a situation that we need DBA to deal with, and the actual problems are as follows:

There are multiple Schema in the same database that have the same content and are used for different testing purposes. Some development synchronization tasks prompt you to write a program to manipulate objects within or between Schema. From a software version point of view, maintaining a tool script is the best way to avoid version confusion caused by modifications. How to use a stored procedure script to perform differently under different Schema has become a problem that we need to consider.

Simplify the above problem as follows: Schema An includes a stored procedure Proc,An and a data table T1. In the Proc code, the operation on table T1 is included. There is also a datasheet T1 in Schema B, and B has a private synonym synonym that points to A.Proc called Proc. The question is how can Proc access data tables in different Schema depending on the Schema being executed? In other words, if Schema An invokes the Proc package, it operates on the data table T1 in Schema A. If B calls the Proc package, manipulate the datasheet T1 in Schema B.

In order to have a clear way to deal with the above problems, the following example is used to simulate the problem:

SYS@seiang11g > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

SYS@seiang11g > create user wjq1 identified by wjq1 default tablespace seiang

User created.

SYS@seiang11g > create user wjq2 identified by wjq2 default tablespace seiang

User created.

SYS@seiang11g > grant connect,resource,create procedure,create synonym to wjq1,wjq2

Grant succeeded.

SYS@seiang11g > select * from dba_sys_privs where grantee in ('WJQ1','WJQ2')

GRANTEE PRIVILEGE ADM

WJQ1 CREATE SYNONYM NO

WJQ2 UNLIMITED TABLESPACE NO

WJQ2 CREATE SYNONYM NO

WJQ1 UNLIMITED TABLESPACE NO

WJQ2 CREATE PROCEDURE NO

WJQ1 CREATE PROCEDURE NO

6 rows selected.

Create the data table and the corresponding stored procedure under Schema wjq1.

WJQ1@seiang11g > create table tab_wjq (name varchar2 (20))

Table created.

WJQ1@seiang11g > create or replace procedure proc_wjq1 (v_name varchar2) is

2 begin

3 insert into tab_wjq values (v_name)

4 commit

5 end proc_wjq1

6 /

Procedure created.

Schema wjq1 executes stored procedures

WJQ1@seiang11g > exec proc_wjq1 ('wjq')

PL/SQL procedure successfully completed.

WJQ1@seiang11g > select * from tab_wjq

NAME

-

Wjq

Assign the permissions of the procedure proc_wjq1 to Schema wjq2

WJQ1@seiang11g > grant execute on proc_wjq1 to wjq2

Grant succeeded.

In addition, create a Schema wjq2 data table object and include a synonym object.

WJQ2@seiang11g > create table tab_wjq (name varchar2 (20))

Table created.

WJQ2@seiang11g > create synonym proc_wjq1 for wjq1.proc_wjq1

Synonym created.

WJQ2@seiang11g > select * from user_synonyms

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

PROC_WJQ1 WJQ1 PROC_WJQ1

Do the default test and call the stored procedure proc_wjq1 in Schema wjq2 to see which Schema data table is being operated.

WJQ2@seiang11g > exec proc_wjq1 ('seiang')

PL/SQL procedure successfully completed.

WJQ2@seiang11g > select * from tab_wjq

No rows selected

There is no data in the data table tab_wjq in Schema wjq2. Check the data table in Schema wjq1:

WJQ1@seiang11g > select * from tab_wjq

NAME

-

Wjq

Seiang

The above experiment shows that by default, different Schema objects call the same stored procedure, and the objects involved are all the same. That is, the "definer permission" in the Oracle stored procedure. Once the user has permission to execute the stored procedure, it means that the authority system of the definer is used in the executor.

Well, there seems to be no way to solve this problem. The executor points to Schema wjq1's datasheet tab_wjq.

The corresponding to the definer permission is the caller permission. That is to say, whether the user can execute the objects in the program body depends entirely on the execution calling the user's system permissions and object permissions (note: non-role permissions). Take a bold guess, if you use caller permissions, is it possible to directly access objects in your Schema from the perspective of executive user permissions? The following is verified by experiments:

WJQ1@seiang11g > create or replace procedure proc_wjq1 (v_name varchar2) authid current_user is

2 begin

3 insert into tab_wjq values (v_name)

4 commit

5 end proc_wjq1

6 /

Procedure created.

Experiment is carried out in the user wjq1, and the result is:

WJQ1@seiang11g > exec proc_wjq1 ('wjqbest')

PL/SQL procedure successfully completed.

WJQ1@seiang11g > select * from tab_wjq

NAME

-

Wjq

Seiang

Wjqbest

Switch to the user wjq2 and do the experiment:

WJQ2@seiang11g > exec proc_wjq1 ('seiangbest')

PL/SQL procedure successfully completed.

WJQ2@seiang11g > select * from tab_wjq

NAME

-

Seiangbest

From the above experimental results, we can find that in the caller permission mode, we can achieve the priority effect of calling the data table under Schema. What happens if there is no data table tab_wjq in Schema wjq2 at this time?

WJQ2@seiang11g > drop table tab_wjq

Table dropped.

WJQ2@seiang11g > exec proc_wjq1 ('')

BEGIN proc_wjq1 (''); END

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "WJQ1.PROC_WJQ1", line 3

ORA-06512: at line 1

What happens if the wjq2 user has permission to tab_wjq the datasheet under the wjq1 user?

WJQ1@seiang11g > grant all on tab_wjq to wjq2

Grant succeeded.

WJQ2@seiang11g > select * from wjq1.tab_wjq

NAME

-

Wjq

Seiang

Wjqbest

WJQ2@seiang11g > exec proc_wjq1 ('')

BEGIN proc_wjq1 (''); END

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "WJQ1.PROC_WJQ1", line 3

ORA-06512: at line 1

From the above results, it is found that even if wjq2 has the permission of tab_wjq in the data table under wjq1, it can only manipulate objects under its own Schema when using the permission of caller. Therefore, definer permissions and caller permissions are two important conceptual objects in Oracle stored procedures, and applications often have good results in some troublesome scenarios.

Thank you for reading! This is the end of the article on "sample analysis of user permissions and caller permissions in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.