We are having a new project in my company which is basically an interface to manage some actions in the Teradata.
I'm trying to create a stored procedure that create users (for this interface).
This is the basic code that i have until now-
replace procedure dwd1_generic_macro.create_account(in Usr_Name varchar(10), in Usr_Pswrd varchar(50),in Usr_Owner varchar(50))
declare mycommand varchar(10000);
set mycommand = 'CREATE USER ' || Usr_Name || ' FROM ' || Usr_Owner || ' AS PERM=0 PASSWORD = ' || Usr_Pswrd || ' DEFAULT DATABASE = DWD1_View PROFILE = "PROF-DEVELOP" NO FALLBACK COLLATION = HOST;';
execute immediate mycommand;
when im trying to execute this-
CALL dwd1_generic_macro.create_account('MyUserName', 'aa112233AA', 'develop');
i get the following error-
CALL failed 5611 the user does not have DROP PROFILE right.
what am i doing wrong? what privillege should i give and to who? (the database of the stored procedure, the user that executes it..?
And also i wanted to ask if there is any way to return "status" from the procedure- like 1 if the create account succeeded or 0 if it Failed.
Thanks a lot..
"To submit a MODIFY USER request that assigns or changes a profile, you must have the DROP PROFILE privilege."
So the user under the SP (and the dynamic SQL) is been executed (definer, invoker, owner, creator) must have DROP PROFILE privilege.
You can return OK/NotOK value from SP as an out parameter.
I saw that i need to create the procedure while im connected with DBC (or any other user with the needed rights), and create it under a user and not a database!
Example- connected to the teradata as DBC and run the following command-
Replace procedure MyUser.S_procedure as....
I Still didnt really got the deal with errors- is there any way to catch exceptions? otherwise how should i know when an error occured and when everything was fine?
Do You maybe have an example to show me?
You can (must) handle errors with CONDITIONS and CONDITION HANDLERS.
You can find examples in the TD documentation.