Using invoker rights in pl sql, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Using invoker rights in pl sql

Features of pl/sql, Main features of PL/SQL A good way to get familiar ...

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Recursion, Recursion The Recursion is a powerful method for simplify th...

Recursion The Recursion is a powerful method for simplify the design of the algorithms. Principally, the recursion means the self-reference. In the recursive mathematical serie

Use the nocopy compiler hint - performance of application, Use the NOCOPY C...

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Example of using aggregation on nested tables, Example of Using Aggregation...

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Semidifference and not - sql, Semidifference and NOT - SQL In this sec...

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

Blocks, Blocks: The fundamental program unit in the PL/SQL is the bloc...

Blocks: The fundamental program unit in the PL/SQL is the block. The PL/SQL block is defined by the keywords BEGIN, DECLARE, EXCEPTION, and END. These keywords partition the b

Relational shema.., Find the account numbers of all customers whose balance...

Find the account numbers of all customers whose balance is more than 10,000 $

Fetching from a cursor variable, Fetching from a Cursor Variable The F...

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Parameter and keyword description - procedures, Parameter and Keyword Descr...

Parameter and Keyword Description: procedure_name The user-defined procedure is declared by this construct. parameter_name: This identifies the formal parameter t

Understanding nested tables, Understanding Nested Tables Within the data...

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd