DDL and Dynamic SQL

I have been using dynamic SQL extensively for the creation of views on the fly. However, I have encountered a major stumbling block and would like to know if anyone knows a workaround.

We have a procedure that creates a view using dynamic SQL. For the sake of simplicity the SQL statement for the view is as follows:

"CREATE VIEW test_view AS SELECT * FROM test_table"

The procedure is created in the schema of MAIN_USER who also owns test_table. A public synonym exists for test_table and SELECT access is granted to PUBLIC. When the procedure is run by MAIN_USER it works okay.

The real motive behind the dynamic creation of the view was that each user could have a different view on the table. However, when USER1 runs the procedure the view is created in the schema of the owner of the procedure viz. MAIN_USER. When USER2 runs the procedure it simply overwrites the view created by USER1. The code was then modified as follows:

SELECT user FROM dual INTO current_user; sql_string := 'CREATE VIEW ' || current_user || '.test_view AS SELECT * FROM test_table';

When USER1 now runs the procedure Oracle returns error 1031 - insufficient priveleges.
USER1 has the following priveleges (granted explicitly): CREATE VIEW
EXECUTE ON SYS.DBMS_SQL
EXECUTE ON MAIN_USER.TEST_SQL (the procedure creating the view)

The procedure works if I load it in SYS - I am thinking the problem has something to do with DBMS_SQL owned by SYS?

The other alternative which I am not considering is to load the procedure into every user schema - this is too much maintenance and is an inelegant solution.

Has anyone had this problem and worked out a solution?

TIA Nasier. Received on Tue Oct 21 1997 - 00:00:00 CDT