Using invoker rights in pl sql, PL-SQL Programming

Using Invoker Rights:

By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inhabit. For illustration, suppose that the following stand-alone procedure, that can drop any type of the database object, resides in the schema scott:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS

BEGIN

EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;

END;

Also suppose that the user jones has been granted the EXECUTE privilege on this procedure. When the user jones calls drop_it, as shown, the dynamic DROP statement executes with the rights of user scott:

SQL> CALL drop_it('TABLE', 'dept');

Moreover, the unqualified reference to table dept is solved in the schema scott. Therefore, the procedure drops the table from schema scott, not from the schema jones. Though, the AUTHID clause enables the stored procedure to execute with the privileges of its invoker. These procedures are not bound to the particular schema. For illustration, the version below of the drop_it executes with the privileges of its invoker:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)

AUTHID CURRENT_USER AS

BEGIN

EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;

END;

Posted Date: 10/6/2012 8:36:38 AM | Location : United States







Related Discussions:- Using invoker rights in pl sql, Assignment Help, Ask Question on Using invoker rights in pl sql, Get Answer, Expert's Help, Using invoker rights in pl sql Discussions

Write discussion on Using invoker rights in pl sql
Your posts are moderated
Related Questions
Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri