This example was tested on Oracle 10.2.0.1.0. It shows how to use AUTHID CURRENT_USER and PRAGMA EXCEPTION_INIT. First, login as user John and create a function called COUNT_ROWS. This accepts a table name, uses EXECUTE IMMEDIATE to run a SELECT COUNT(*) on the table then returns the result to the calling process:
SQL> conn john/smith@test10
Connected.
SQL> SHOW USER
USER is "JOHN"
SQL> CREATE OR REPLACE FUNCTION COUNT_ROWS
2 (TAB IN VARCHAR2) RETURN PLS_INTEGER
3 IS
4 ROW_COUNT PLS_INTEGER;
5 TABLE_NOT_FOUND EXCEPTION;
6 PRAGMA EXCEPTION_INIT(TABLE_NOT_FOUND,-942);
7 BEGIN
8 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||TAB
9 INTO ROW_COUNT;
10 RETURN ROW_COUNT;
11 EXCEPTION
12 WHEN TABLE_NOT_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('Cannot find '||TAB);
14 RETURN NULL;
15 END COUNT_ROWS;
16 /
Function created.
SQL>
John then allows Fred to execute this function:
SQL> GRANT EXECUTE ON COUNT_ROWS TO FRED
2 /
Grant succeeded.
SQL>
John creates a table with 2 rows:
SQL> CREATE TABLE CARS AS
2 SELECT 'MORRIS' MANUFACTURER FROM DUAL
3 /
Table created.
SQL> INSERT INTO CARS VALUES('AUSTIN')
2 /
1 row created.
SQL>
Fred cannot see John’s table as he has not been granted SELECT access:
SQL> CONN FRED/BLOGGS@TEST10
Connected.
SQL> SHOW USER
USER is "FRED"
SQL> SELECT COUNT(*) FROM JOHN.CARS
2 /
SELECT COUNT(*) FROM JOHN.CARS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
But Fred can see the table if he executes John’s COUNT_ROWS function as it run with John’s privileges:
SQL> SET SERVEROUTPUT ON
SQL> SELECT JOHN.COUNT_ROWS('JOHN.CARS') FROM DUAL
2 /
JOHN.COUNT_ROWS('JOHN.CARS')
----------------------------
2
SQL>
Fred does not even need to fully qualify the table name:
SQL> EXEC DBMS_OUTPUT.PUT_LINE (JOHN.COUNT_ROWS('CARS'))
2
PL/SQL procedure successfully completed.
SQL>
John might decide that he would prefer Fred to run this function with his (i.e. Fred’s) privileges. John can do this by specifying AUTHID CURRENT_USER when he creates the function. You should also look to see how PRAGMA EXCEPTION INIT associates ORA-00942 with an exception called TABLE_NOT_FOUND. This exception is then used to trap the error and display a message. I hope to cover the subject of error handling more fully in future posts:
SQL> CONN JOHN/SMITH@TEST10
Connected.
SQL> SHOW USER
USER is "JOHN"
SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE FUNCTION COUNT_ROWS
2 (TAB IN VARCHAR2) RETURN PLS_INTEGER
3 AUTHID CURRENT_USER
4 IS
5 ROW_COUNT PLS_INTEGER;
6 TABLE_NOT_FOUND EXCEPTION;
7 PRAGMA EXCEPTION_INIT(TABLE_NOT_FOUND,-942);
8 BEGIN
9 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||TAB
10 INTO ROW_COUNT;
11 RETURN ROW_COUNT;
12 EXCEPTION
13 WHEN TABLE_NOT_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('Cannot find '||TAB);
15 RETURN NULL;
16 END COUNT_ROWS;
17 /
Function created.
SQL>
Now when Fred executes the function, he cannot see John’s CARS table:
SQL> CONN FRED/BLOGGS@TEST10
Connected.
SQL> SHOW USER
USER is "FRED"
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.PUT_LINE(JOHN.COUNT_ROWS('CARS'))
Cannot find CARS
PL/SQL procedure successfully completed.
SQL>
Even fully qualifying the table name does not help:
SQL> EXEC DBMS_OUTPUT.PUT_LINE(JOHN.COUNT_ROWS('JOHN.CARS'))
Cannot find JOHN.CARS
PL/SQL procedure successfully completed.
SQL>
If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.
