Authentication Of PL/SQL Programs : AUTHID CURRENT_USER or DEFINER

Authentication is a very importantt consideration on writing programs in security side. In Oracle's PL/SQL, default behaviour authentication mode of program units are DEFINER that is when executing program units you get writer's authentication. You can override this by explicitly declering authentication mode.
Example below, i simple show the differences bietween two methods:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop function get_count2;
Function dropped
SQL> drop function get_count;
Function dropped
SQL> SELECT COUNT(*)  FROM user_tables;
  COUNT(*)
----------
       730

get_count is default auth mode. When another user calls this function it will use SYS's user_tables
SQL> CREATE OR REPLACE FUNCTION get_count RETURN NUMBER AUTHID DEFINER IS
  2    table_count NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO table_count FROM user_tables;
  5 
  6    RETURN table_count;
  7  END;
  8  /
Function created


get_count2 is CURRENT_USER auth mode. When another user calls this function it will use its user_tables
SQL> CREATE OR REPLACE FUNCTION get_count2 RETURN NUMBER AUTHID CURRENT_USER IS
  2    table_count NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO table_count FROM user_tables;
  5 
  6    RETURN table_count;
  7  END;
  8  /
Function created
SQL> grant execute on get_count to hr;
Grant succeeded
SQL> grant execute on get_count2 to hr;
Grant succeeded

SQL> conn hr/hr;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as hr

The count is SYS's table count.
SQL> SELECT sys.get_count FROM dual;
 GET_COUNT
----------
       730

The count is HR's table count.
SQL> SELECT sys.get_count2 FROM dual;
GET_COUNT2
----------
        38
SQL>