So you are testing or developing in a Teradata Express 13 or a general Teradata environment and you are anxious to integrate your Java code into your Teradata DB using Java UDF extensibility...just to find yourself in an errors-headache with things like:
- 7583 The secure mode processes had a set up error
- 7566 The protected mode user is not defined; cannot execute UDF/XSP/UDM.
This is probably because you don't have a good setup for the OS user which the Teradata environment uses to execute your Java code. This user is required because it allows the Teradata system to execute the java code in a JRE (Java Runtime Environment) OS thread. So, this guide should take you through the process of getting things a little sorted out...
STEP 1: First check that you have an account on your Windows system called "tdatuser" and which is a member of the group "tdatudf". Check this from start >> control panel >> user accounts >> check the users for this computer table and take note of the "user name" and "group" columns. The user should be only a member of the "tdatudf" group (nothing else).
If STEP 1 is a success ("tdatuser" does exist in "tdatudf") then this guide is not for you. Otherwise, proceed with the following steps if the user does not exist and you don't want to reinstall the Teradata database (which automatically installs the "tdatuser" and configures it):-
STEP 2: Create a new user and assign the user to the "tdatudf" group. Do this by start >> control panel >> user accounts >> Advanced tab >> Advanced (under advanced user management) >> right click users >> select new user and use the any username (EXCEPT "tdatuser" which is the default user we are missing and which you should never edit/create manually)
STEP 3: After the user is created and within the same window from STEP 2, right-click the new user you created >> select properties >> select member of tab >> select add button >> enter the name "tdatudf"
STEP 4: Now that we created the user to use we need to create an AUTHORIZATION object in the Teradata DB which identifies this new user to the system. Login to the Teradata DB with any client (BTEQ, SQL assistant, etc) and with an administrator privileges account, then execute the query: -
CREATE AUTHORIZATION username.authname AS INVOKER
Make sure of the following: the username should be replaced with the TD user you would like to use to run (or in other words invoke) the UDFs from (the TD username you will use to logon when running the UDF functions), the authname should be replaced with any string which identifies the authorization object (select any legal name you want to use), the domain should be the Windows OS domain name (for using the current local user domain you are logged-in with it is the same as the computer name which you can find by right-clicking myComputer >> properties >> Computer Name tab OR by going to the command propmt and typing "ECHO %USERDOMAIN%" without quotes), the user should be the windows OS user you created in STEP2, and the password should be the windows password you assigned to the user.
STEP 5: Edit your UDF function object in the Teradata database by adding the line " EXTERNAL SECURITY INVOKER" which indicates that the UDF should run using the authorization settings defined for the invoker (which we created in STEP 3) instead of using the default "tdatuser" settings (which we don't have in the first place...we already checked this in STEP 1, right?!). Here is an example for a UDF I created (please note the last line which I added to the normal UDF definition I got by following the ECLIPSE JAVA UDF TUTORIAL in the Articles section):
REPLACE FUNCTION tduser.RETURNLASTNAME
(name VARCHAR(256) CHARACTER SET LATIN)
RETURNS VARCHAR(256) CHARACTER SET LATIN
PARAMETER STYLE JAVA
CALLED ON NULL INPUT
EXTERNAL NAME 'ReturnLastNameJarId:judf.ReturnLastName.returnLas
tName(java.lang.String) returns java.lang.String'
EXTERNAL SECURITY INVOKER
STEP 6: run the UDF and it should go fine without any errors...hopefully you should see your result-set output!
SELECT tduser.returnlastname('John Smith') ;
Try using an EXTERNAL SECURITY INVOKER instead of the default. This can be done by going through steps 2 to 5 above.
I first tried the CREATE AUTHORIZATION command to the dbc user:
# CREATE AUTHORIZATION dbc.authname as invoker domain 'teradata' user 'udf' password 'udf';
--> Failure 3524 The user does not have CREATE AUTHORIZATION access to database DBC.
'udf' is the user I created and 'teradata' is the name of the machine
When I then try another user 'woipv':
# create authorization woipv.authname2 as invoker domain 'teradata' user 'woipv' password 'woipv';
--> *** Failure 6937 Authorization 'INVOKER_DEFAULT' already exists.
The user 'udf' seems to work so far:
# create authorization udf.authname3 as invoker domain 'teradata' user 'udf' password 'udf';
But when I add the line
EXTERNAL SECURITY INVOKER
to the end of the
FUNCTION command and save it, the line is automatically removed immediately.
When I try the UDFs with the woipv or udf user nevertheless I get now the error:
Executed as Single statement. Failed [7585 : HY000] The authorization is not valid for secure UDF/XSP picturesTestDB.ttry2.
Elapsed time = 00:00:01.143
STATEMENT 1: Select Statement failed.
I am completely new to Teradata and I hope these information are enough to help me.