I am trying to write a stored procedure that will change the schema of the table being selected from depending on who calls the function. My select statement looks like this:
SELECT COUNT (*) FROM user.customers ;
I assumed that Oracle would pick the username from the USER function, but it does not work. I am working on an Oracle8i database.
You have two good (but not equivalent) options:
1. AUTHID CURRENT_USER
Let's say user Scott writes this function:
CREATE OR REPLACE FUNCTION customer_count RETURN PLS_INTEGER AUTHID DEFINER -- DEFINER is default AS return_val PLS_INTEGER; BEGIN SELECT COUNT (*) INTO return_val FROM customers; -- No owner specified RETURN return_val; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / SHOW ERRORS GRANT EXECUTE ON customer_count TO PUBLIC;
This function always returns the number of rows in scott.customer. It doesn't matter if the user executing it is Scott or Miller, or if they run it right after saying "ALTER SESSION SET CURRENT_SCHEMA = king;". Nor does it matter if the user lacks SELECT privileges on scott.customers. AUTHID DEFINER means that when the function is run, Oracle will run it in the procedure owner's schema with the procedure owner's privileges.
The alternative to "AUTHID DEFINER" is "AUTHID CURRENT_USER". If the function above were re-written with just that one line changed. Then it would return the number of rows in scott.customers when Scott ran it, and the number of rows in miller.customers when Miller ran it. If either user executed it right after saying "ALTER SESSION SET CURRENT_SCHEMA = king;" then it would return the number of rows in king.customer (assuming the actual user had SELECT privileges on king.customers).
2. Dynamic SQL
You were on the right track when you tried to select from "user.customers". Unfortunately, the correct way to do this is rather complicated. It involves using the dbms_sql package as explained in part 2 of my previous column on dynamic SQL. In your case, you'd include a statement like:
dbms_sql.parse ( cursor_id , 'SELECT COUNT (*) FROM ' || USER || '.customers' , dbms_sql.native );
Everything about AUTHID still applies. If Scott writes this function with "AUTHID DEFINER" and Miller runs it, causing the dynamic SQL statement to read " ... FROM miller.customers" (or "... FROM king.customers") then Miller will get an "ORA-00942: table or view does not exist" error if Scott does not have SELECT privileges on miller.customers (or king.customers, as the case may be): it won't matter if Miller has the right privileges.
Note that the USER function returns the same value inside or outside of stored procedures, regardless of the AUTHID setting. (If, on the other hand, you need a value that does change inside an AUTHID DEFINER stored procedure, then "SELECT username FROM user_users.")
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.