Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 27, 2003

Definer and Invoker Rights for stored routines in Oracle - Page 2

By DatabaseJournal.com Staff

Restriction in using Invoker rights

1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.

2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.

3. Definer rights will always be used to resolve any external references when compiling a new routine.

4. Maintain extra caution on privileges being assigned to a different user. If the wrong privileges are assigned, a routine with invoker rights may have a mind of its own! Such issues would be difficult to debug. So ensure that the grants are perfectly in place.

5. For an invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.

E.g.

SQL> DOC The above Procedure is created in user A and user B refers it. 

SQL> conn a/a@oradata1 
Connected. 
SQL> grant execute on update_par to B; 

Grant succeeded. 

SQL>  declare 
  2    l_status varchar2(200); 
  3   begin 
  4    a.update_par('updated by', 'User ' || user, l_status); 
  5    commit; 
  6   end; 
  7  / 

PL/SQL procedure successfully completed. 

SQL> select * from a.appparmst; 

PARCOD               PARVAL 
-------------------- ---------------------------------------------------- 
updated by           User A 

SQL> conn b/b@oradata1 
Connected. 
SQL> declare 
  2   l_status varchar2(200); 
  3  begin 
  4   a.update_par('updated by', 'User ' || user, l_status); 
  5   commit; 
  6  end; 
  7  / 
declare 
* 
ERROR at line 1 
ORA-00942 table or view does not exist 
ORA-06512 at "A.UPDATE_PAR", line 6 
ORA-06512 at line 4 

SQL> DOC the error occurred because table APPPARMST does not exist for user B. 
DOC> I create it for user B and then call update_par again 

SQL> CREATE TABLE APPPARMST 
  2  (PARCOD   VARCHAR2(20) NOT NULL, 
  3   PARVAL   VARCHAR2(200)); 

Table created. 

SQL> insert into appparmst values('updated by', null); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> declare 
  2   l_status varchar2(200); 
  3  begin 
  4   a.update_par('updated by', 'User ' || user, l_status); 
  5   commit; 
  6  end; 
  7  / 

PL/SQL procedure successfully completed. 

SQL> select * from b.appparmst; 

PARCOD               PARVAL 
-------------------- --------------------------------------- 
updated by           User B 

SQL> DOC example over. 

Invoker rights is a powerful option, to be used with caution. To reduce code maintenance, this option should be thought of in the design stage, based on the need to share code across schemas with a similar setup.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM