Definer and Invoker Rights
PL/SQL subprograms, by default, were executed in the schema of the owner of these objects prior to Oracle8i. This is referred to as definer rights, meaning these programs were executed in and according to the authorization and authentication of the schema defining these programs. Also, any underlying objects to be created, altered, or dropped in the process of execution were in the definer schema. This approach has a disadvantage, however.
The output of these subprograms became available as kind of centralized in the definer schema. That is, even executed by different schemas, the output becomes available in the tables in the owner schema. This centralization of data is unsuitable if each user requires a local disparate copy of the output in his or her own schema.
Invoker Rights Model
Oracle8i introduced the concept of invoker rights, which enables subprograms to be executed in and according to the authorization and authentication of the schema invoking (i.e., executing) the subprogram. Invoker rights are specified by the AUTHID clause. The syntax for this clause is as follows:
AUTHID CURRENT_USER | DEFINER
You can specify the AUTHID clause for a procedure, function, package, or object type only. Here, CURRENT_USER is the schema name of the user executing the subprogram or object type, and DEFINER is the schema name of the user owning the subprogram or object type. DEFINER is the default.
One Application Program, Multiple Schemas
Consider an order entry application that processes orders for different regions. Each region should operate its own separate schema, so there are tables such as ORDERS and ORDER_ITEMS. It isn't so simple, however. These tables have to be created having the region_name as part of the table name and in the schema of each individual region. This means that the table names have to be dynamically generated. Not only this—the DML operations should be performed on these tables in the schema of the individual region. To dynamically generate the table names, the immediate solution seems to be using DBMS_SQL. What about creating the tables and performing the DML on these tables in the schema of the individual region? AUTHID does the job.
For simplicity, let's assume that there are two regions: region1 and region2. The corresponding schema names are region1 and region2. Also, there is a third user, which is the common user whose schema owns the common code. Let's signify this common user with the username "name" and the password "plsql9i/plsql9i."
Here's the code to create the schemas region1 and region2:
connect system/manager;
create user region1 identified by region1;
grant connect, resource to region1;
create user region2 identified by region2;
grant connect, resource to region2;
The following code illustrates a sample procedure implementing both of the preceding requirements:
Create or replace Procedure create_dyn_table
(i_region_name VARCHAR2,
retcd OUT NUMBER,
errmsg OUT VARCHAR2)
authid current_user
Is
cur_id INTEGER;
ret_code INTEGER;
Dyn_string VARCHAR2(1000);
Dyn_Table_name VARCHAR2(21);
Begin
dyn_table_name := 'ORDERS_FOR_'||i_region_name;
dyn_string := ' CREATE TABLE ' ||dyn_table_name||
' (order_id NUMBER(10) PRIMARY KEY,
order_date DATE NOT NULL,
total_qty NUMBER,
total_price NUMBER(15,2))';
cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_id, dyn_string, DBMS_SQL.V7);
ret_code := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);
retcd := 0;
EXCEPTION WHEN OTHERS THEN
retcd := SQLCODE;
errmsg := 'ERR: Creating table '||dyn_table_name ||' - '||SQLERRM;
End;
/
Now this procedure is created in the schema plsql9i/plsql9i, and the individual users region1 and region2 can execute this procedure in their own schemas. The following script does the job:
/* File name authid.sql */
connect plsql9i/plsql9i;
Create or replace Procedure create_dyn_table
(i_region_name VARCHAR2,
retcd OUT NUMBER,
errmsg OUT VARCHAR2)
authid current_user
is
cur_id INTEGER;
ret_code INTEGER;
dyn_string VARCHAR2(1000);
dyn_Table_name VARCHAR2(21);
Begin
dyn_table_name := 'ORDERS_FOR_'||i_region_name;
dyn_string := ' CREATE TABLE ' ||dyn_table_name||
' (order_id NUMBER(10) PRIMARY KEY,
order_date DATE NOT NULL,
total_qty NUMBER,
total_price NUMBER(15,2))';
cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_id, dyn_string, DBMS_SQL.V7);
ret_code := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);
retcd := 0;
EXCEPTION WHEN OTHERS THEN
retcd := SQLCODE;
errmsg := 'ERR: Creating table '||dyn_table_name ||' - '||SQLERRM;
End;
/
grant execute on create_dyn_table to public;
connect region1/region1;
create synonym create_dyn_table for plsql9i.create_dyn_table;
declare
retcd NUMBER;
errmsg VARCHAR2(100);
begin
create_dyn_table('REGION1',retcd, errmsg);
end;
/
select table_name from user_tables where table_name like '%REGION1';
connect region2/region2;
create synonym create_dyn_table for plsql9i.create_dyn_table;
declare
retcd NUMBER;
errmsg VARCHAR2(100);
begin
create_dyn_table('REGION2',retcd, errmsg);
end;
/
select table_name from user_tables where table_name like '%REGION2';
Here's the output of the preceding script:
Thus, you can see that the invoker rights model provides centralization of code (application programs) and decentralization (in fact, localization) of data. Semantic external references in the code are resolved in the schema of the invoker of the code.
| Tip� |
|
| Tip� | Invoker rights checks for privileges at program execution time. This is in contrast to definer rights, where external references are resolved at compile time. Also, only data element references (of tables and views) can be resolved, not those of program elements. |
| Tip� | When you use invoker rights, external references in SQL statements are resolved with the privileges of the invoker. References in PL/SQL statements are still resolved with the privileges of the owner. Granting via roles is allowed as long as it doesn't affect the compile time resolution of the invoker rights routine. |
Invoker Rights and Dynamic SQL
The create_dyn_table used the AUTHID clause to take advantage of invoker rights. This procedure made use of dynamic SQL using DBMS_SQL to explain invoker rights. Once dynamic SQL comes into the picture, many issues are involved, such as
Which user is the owner of the program?
Which user is executing the program?
Under which schema is the application output being created?
As the example in the preceding section illustrates, invoker rights provide the answer to all these questions. As a rule of the thumb, follow the next Tip when you use dynamic SQL, either native dynamic SQL or DBMS_SQL.
| Tip� | When you use dynamic SQL and/or dynamic PL/SQL, either using native dynamic SQL or DBMS_SQL, always specify AUTHID CURRENT_USER for all stored subprograms. |
No comments:
Post a Comment