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
Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the

Truth Tables: However in propositional logic - here we are restricted to expressing sentences and where the propositions are true or false - so we can check where a particular

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

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

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

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