Computed column function permission

[Former Member]

[SQL Anywhere 12.0.1.3324]

If I create a computed column that calls a function in its expression then it seems that a user needs both SELECT permission on the table AND EXECUTE permission on that function in order to retrieve the column's value.

This surprised me because the help file ('Recalculating computed columns') states that 'Computed columns are not recalculated under the following circumstances: The computed column is queried[...]'

e.g. if I create a function and table as follows:

CREATE FUNCTION a_group.a_function (some_chars char(4)) RETURNS CHAR(4) 
BEGIN 
    RETURN some_chars 
END
go
CREATE TABLE a_group.a_table ( a_column CHAR(4) COMPUTE(a_group.a_function('ABCD')))
go
GRANT SELECT ON a_group.a_table to a_user

then if I connect as a_user and try

SELECT a_column FROM a_group.a_table

it gives the error 'Permission denied: you do not have permission to execute the procedure "a_function"'

Is this expected behaviour?

[VolkerBarth]

I can reproduce your results with 12.0.1.3484 (and that function explicitly declared as DETERMINISTIC). I would agree that this is not expected behaviour IMHO...

[Former Member]

Even stranger, I just realised that SELECTing any column from the table gives the same error

e.g. if my table definition is now

CREATE TABLE a_group.a_table ( a_column CHAR(4) COMPUTE(a_group.a_function('ABCD')), a_column2 INTEGER)

Then doing

SELECT a_column2 FROM a_group.a_table

Gives the same error, even though the single column I am selecting has nothing to do with the function

[VolkerBarth]

Based on Glenn's explanation, I would add another approach that

1. omits the need to grant execute permission on the UDF for the users who simply are allowed to select from the table and
2. still ensures that the corresponding column gets a calculated value.

For that, instead of using a computed column, you would use a BEFORE trigger to set the column's value "automatically". As trigger code runs with the permission of the table owner, there's no need to grant execute permission for user who simply want to select from the table.

A small (untested) sample with a given UDF named myFn():

Instead of

create table t (
   c1 int primary key,
   c2 varchar(255) compute (myFn(c1))
);

with need to grant execute on myFn to all users that are allowed to select from t, you would declare

create table t (
   c1 int primary key,
   c2 varchar(255)
);

create trigger tr_iu_t
   before insert, update on t
   referencing new as n
   for each row
begin 
   set n.c2 = myFn(n.c1);
end;

Note: I surely don't want to tell that triggers are "easier" than computed columns. I'm just trying to show how you omit the need to grant execute permission here...

---

For a detailed discussion between BEFORE UPDATE triggers and COMPUTED COLUMNs and the dependencies between them, I remember a longer thread discussion with Glenn in the general newsgroup...