[SQL Anywhere 126.96.36.19924]
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?
The answer to this question is more complex than it would seem at first glance.
Yes, of course it would be reasonable and expected for SQL Anywhere server to build (and verify permissions for) a query with only the column and attribute references that are explicitly required.
With a computed column, however, things are not that simple. The whole idea of a computed column is to be able to substitute the computed column for a (possibly complex) expression that occurs within the query. Consequently, the server builds each computed column for every table referenced in the query in case the server detects that one or more complex expressions can be replaced by a corresponding computed column (and, possibly, utilize an index on that computed column).
At the moment, when building any expression involving a user-defined function, SQL Anywhere verifies that the user has permission to call that function. Permission checking at query compile time, too, is a conscious decision because it avoids situations where different invocations of seemingly equivalent statements get different behaviour (one works, the other gets a permission error at runtime).
I would agree that it's expected that you would need to GRANT EXECUTE permissions on the underlying function to have this configuration work correctly ( http://dcx.sybase.com/index.html#1201/en/dbadmin/permission-db-objects.html ), although I do agree that it's odd that we're requesting permission on this object when we don't explicitly require it for the current operation. This error should probably only happen with DML operations when the current user is actually trying to insert/update/delete against the base table and we require the function permission to update the column(s).
I have opened internal issue CR #691747 to investigate this issue further. I will update this answer once we can confirm the intended behaviour.
Edit: Engineering has confirmed that Glenn's answer is the correct behaviour description.
Thank you for the behaviour report.
Based on Glenn's explanation, I would add another approach that
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():
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...
answered 11 Dec '11, 15:04