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

[Former Member · Accepted solution]

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).

[VolkerBarth · Accepted solution]

"Consequently, the server builds each computed column for every table referenced in the query..."

That comes as a surprise to me - does it mean in the above sample I could query

select a_group.a_function('ABCD') from a_group.a_table

and the engine would detect that this value could be delivered by the computed column a_column and would therefore omit the function call and replace it with the existing column's values?

Until now, my idea of a computed column was exactly the opposite: I commonly use them to store values automatically that simply depend on other columns (say col3 = myFn(col1, col2)) and

1. that might be expensive to calculate (so they have to be calculated only once - unless the table is ALTERed, of course) and
2. I want to avoid inconsistencies by preventing application code from independently altering the inherently dependent columns (i.e. to prevent to set col3 without altering col1 or col2 and vice versa).

So I would never call the underlying compute expression in a query (myFn(...)) but would usually query the computed column by name (col3)...

For that type of usage, IMHO the server's expectation to possibly need to replace the compute expression with the column name seems too precautious...

[thomas_duemesnil · Accepted solution]

That are my expectations too...

[Former Member · Accepted solution]

I think you need to read the help on computed columns. See

http://dcx.sybase.com/index.html#1201/en/dbusage/defining-computed-javause.html*d5e994

[VolkerBarth · Accepted solution]

@Glenn: Got me:) - So it's all well documented (and looks somewhat similar to the "View matching" feature w.r.t. materialized views).

Nevertheless, I still do think that my preferred usage of computed columns makes sense, as well - in the end, it's just the fact that I tend to explicitly use the computed columns in queries instead of using the underlying expressions and relying on the query engine to find the matching column...

Or do I have missed your point?

[Former Member · Accepted solution]

You can certainly refer to computed columns explicitly, yes. Also keep in mind that the matching isn't exhaustive (it's computationally difficult), rather it's close to "exact match" - that is, if you have a computed column defined as (T.a + T.b + T.c) then that is not going to match a predicate written as (T.c + T.b + T.a) > 5. The same is true for materialized view expression matching.