When to mention authID as current_user

Question

I'm mostly an oracle novice, so forgive me if this is a stupid question...

I have read lot of articles already on authID, My understanding is that AUTHID clause instructs Oracle as to whether the routine is to be run with the invoker's rights (CURRENT_USER), or with the Owner rights (DEFINER). If the clause is not specified, Oracle will default to using the AUTHID DEFINER.

Can you please give me a simple example for better understanding of authID?

Answer 1

Here the example:

User1 has DEPARTMENTS table.

User2 has DEPARTMENTS table also.

If we create this procedure within User2 schema

    -- Connected as User2    
    CREATE OR REPLACE PROCEDURE create_dept (

       v_deptno NUMBER,
       v_dname  VARCHAR2,
       v_mgr    NUMBER,
       v_loc    NUMBER) 
    AUTHID CURRENT_USER AS
    BEGIN
       INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
    END;
    /

Then , if we call this procedure - after give appropriate privileges- from User1,

-- Connected as User1
CALL create_dept(44, 'Information Technology', 200, 1700);

The record will be inserted on:

User1.DEPARTMENTS

Because procedure was executed AS CURRENT USER - User1 the caller.

If we omitted AUTHID CURRENT_USER when procedure was created, the record will be inserted on User2.DEPARTMENTS...as usual.

HTH