BEGIN DECLARE iDBNAME VARCHAR(30); DECLARE iDOMAINE VARCHAR(30); DECLARE iTABLE_NAME VARCHAR(30); DECLARE stmt1 VARCHAR(500); FOR compteur AS curseur1 CURSOR FOR select DBNAME as db ,DOMAINE as dom, TABLE_NAME as tab_nam from dwh1.source_table DO SET iDBNAME = compteur.db; SET iTABLE_NAME = compteur.tab_nam;
Hi, Thank you for this precision. I read the documentation carefully.
What I understood from bullet one is "if you are the creator of the procedure then you can use Dynamic SQL". What you tell me about bullet one is "if you want to use Dynamic SQL Statement then you have to be the owner/creator of the database". That's it ?
Anyway i'm the creator of the procedure of course but i'm not the owner of the database. So am I the owner of the procedure ?
The owner of the procedure is the database where the procedure was created and not necessarily the creator of the procedure. In other words if you are user A and you create the procedure in database B you are the creator of the procedure but not the owner of it. The owner is database B. User B has the right to get rid of your procedure (if they want) any time since user B owns it, not you, and you can’t take that privilege away from them.
So as is stated, if you want to use dynamic SQL the owner and creator have to be the same database. What you want to do is to set up a user database for creating those sorts of procedures that require the use of the dynamic SQL statement and create those procedures while logged onto that user database. That database would have to have the privileges to the objects the procedures need to access.
The other not so popular workaround is to create a dummy proc under user A which takes a character string as argument and executes it using dynamic sql... and to give execute permission on this proc to database B... and when the proc under database B wants to execute the dynamic sql, it calls the dummy proc under user A with the necessary sql string as argument ....