All Forums Database
Kishore_1 208 posts Joined 03/10
17 Jul 2014
UDFs and Granting of Access Rights

1)There are certain UDFs cretaed in my Prod Teradata system.I am trying to grant "EXECUTE FUNCTION" on the UDFs to users.
However when i try granting them , error is encountered:The UDF does not exist. Checked in DBC.functions dictionary view,the function exists .However the specificfunction name column in this table has different name for the same functionname ,some kind of function overloading.Do I need to use specific function name in the grant statements to be able to grant the access rights?
2)If grant is provided to PUBLIC, all users in the system will be able to use that access right.What about databases?If there any dummy database ,to which if I grant the access, it will be provided to all the databases in the system?
Appreciate your help!!

Glass 225 posts Joined 04/10
17 Jul 2014

Kishore,
Double check to make sure you are granting access to the proper database holding the UDF.
"Grant execute funciton on db.fn to uname" should work.
DBC.functions lists all Functions and their database. Try running help on the database holding the function you are looking for to check the name is the same. They match on our system so I'm not sure what you are seeing.
 
Rglass
 

Fred 1096 posts Joined 08/04
17 Jul 2014

Yes, if the function is overloaded then you need to either grant access via the SPECIFIC FUNCTION name or indicate the relevant parameter datatypes after the generic FUNCTION name so the database can resolve to a SPECIFIC FUNCTION.
 
GRANT EXECUTE FUNCTION ON SPECIFIC FUNCTION db.fn_1 TO grantee;
GRANT EXECUTE FUNCTION ON db.fn(integer) TO grantee;

Kishore_1 208 posts Joined 03/10
20 Jul 2014

@Glass,I have revalidated and  the problem description provided by me is correct.
Thanks Fred for the suggestion.It worked!!

Joseto 2 posts Joined 06/16
03 Jun 2016

Hi, I am using the version  Teradata 14.10.0602  14.10.06.02 and when I execute the command:
GRANT EXECUTE FUNCTION ON DataBaseName.FunctionName TO 'UserName';
It shows an error saying that something is expected between the keyword TO and the string 'UserName'

Joseto 2 posts Joined 06/16
03 Jun 2016

Oh, maybe this is also needed to post. The way I created the function was:
 

CREATE FUNCTION DataBaseName.FunctionName ("Var1" VARCHAR(3), "Var2" VARCHAR(3), "Var3" VARCHAR(3), "Var4" VARCHAR(3), "Var5" VARCHAR(3), "Var6" VARCHAR(3)) 

                               

 RETURNS VARCHAR(3)

 SPECIFIC FunctionName 

 LANGUAGE SQL

 CONTAINS SQL

 DETERMINISTIC

 --SQL SECURITY DEFINER  ---------IS IT CORRECT TO COMMENT THIS LINE???

 COLLATION INVOKER

 INLINE TYPE 1

CASE 

..

END;

 

ToddAWalter 316 posts Joined 10/11
03 Jun 2016

Username does not belong in single quotes. If it contains any special characters, it should be enclosed in double quotes or if no special characters then simply say the username.

You must sign in to leave a comment.