Invoker_Rights_Versus_Definer_Rights

Invoker_Rights_Versus_Definer_Rights - Invoker Rights...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Invoker Rights Versus Definer Rights By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer-rights subprograms are bound to the schema in which they reside. For example, assume that dept tables reside in schemas scott and blake , and that the following standalone procedure resides in schema scott : CREATE PROCEDURE create_dept ( my_deptno NUMBER, my_dname VARCHAR2, my_loc VARCHAR2) AS BEGIN INSERT INTO dept VALUES (my_deptno, my_dname, my_loc); END; Also assume that user scott has granted the EXECUTE privilege on this procedure to user blake . When user blake calls the procedure, the INSERT statement executes with the privileges of user scott . Also, the unqualified references to table dept is resolved in schema scott . So, even though user blake called the procedure, it updates the dept table in schema scott . How can subprograms in one schema manipulate objects in another schema? One way is to fully qualify references to the objects, as in INSERT INTO blake.dept . .. However, that hampers portability. As a workaround, you can define the schema name as a variable in SQL*Plus. Another way is to copy the subprograms into the other schema. However, that hampers maintenance. A better way is to use the AUTHID clause, which enables stored procedures and SQL methods to execute with the privileges and schema context of their current user. Such invoker-rights subprograms are not bound to a particular schema. They can be run by a variety of users. The following version of procedure create_dept executes with the privileges of its current user and inserts rows into the dept table in that user's schema: CREATE PROCEDURE create_dept ( my_deptno NUMBER, my_dname VARCHAR2, my_loc VARCHAR2) AUTHID CURRENT_USER AS BEGIN INSERT INTO dept VALUES (my_deptno, my_dname, my_loc); END;
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Advantages of Invoker Rights Invoker-rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base. Consider a company that uses a definer-rights (DR) procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, as Figure 8-4 shows, the procedure must also reside at each regional site. This causes a maintenance problem. Figure 8-4 Definer-Rights Problem: Multiple Copies of Identical Procedures Text description of the illustration pls81023_definer_rights_problem.gif To solve the problem, the company installs an invoker-rights (IR) version of procedure analyze at headquarters. Now, as Figure 8-5 shows, all regional sites can use the same procedure to query their own sales tables. Figure 8-5 Invoker-Rights Solution: Single Procedure that Operates on
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/22/2009 for the course DBST dbst 668 taught by Professor Yelena - ta during the Spring '09 term at MD University College.

Page1 / 10

Invoker_Rights_Versus_Definer_Rights - Invoker Rights...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online