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
PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

FORALL Statement The FORALL statements instruct the PL/SQL engine to bulk-bind the input collections before sending them to the SQL engine. Though the FORALL statement consists

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th