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
At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Functions The function is a subprogram which can take parameters and be invoked. Normally, you can use a function to calculate a value. The function has 2 sections: the specifi

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Expression: This is a randomly complex combination of constants, variables, literals, operators, & function calls. The simplest expression consists of a single variable. If th

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed