UDFs and Granting of Access Rights

Database
Enthusiast

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!!

Tags (2)
6 REPLIES
Enthusiast

Re: UDFs and Granting of Access Rights

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

Teradata Employee

Re: UDFs and Granting of Access Rights

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;

Enthusiast

Re: UDFs and Granting of Access Rights

@Glass,I have revalidated and  the problem description provided by me is correct.

Thanks Fred for the suggestion.It worked!!

Fan

Re: UDFs and Granting of Access Rights

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'

Fan

Re: UDFs and Granting of Access Rights

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;

Teradata Employee

Re: UDFs and Granting of Access Rights

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.