Q
Problem solve Get help with specific problems with your technologies, process and projects.

Changing schema

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
  2. Dynamic SQL

 

 

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.")

This was last published in December 2003

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close