Question Re: Computed column function permission in Technology Q&A

Computed column function permission

Former Member — Tue, 22 Nov 2011 09:44:05 GMT

[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?

Re: Computed column function permission

VolkerBarth — Tue, 22 Nov 2011 10:42:51 GMT

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

Re: Computed column function permission

Former Member — Wed, 23 Nov 2011 07:09:17 GMT

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

Re: Computed column function permission

jeff_albion — Mon, 28 Nov 2011 14:50:56 GMT

Hi Luke,

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.

Regards,

Re: Computed column function permission

VolkerBarth — Tue, 29 Nov 2011 02:02:44 GMT

Well, I had expected I only do need permissions on those database objects I do explicitly refer to...

For Luke's sample, I would think this works more like a trigger, i.e. the DML operation itself runs with the permission of the caller but the COMPUTE clause code runs with the permission of the table owner. - Just my 2 cents:)

Re: Computed column function permission

Former Member — Tue, 29 Nov 2011 04:59:14 GMT

@Jeff: thanks for the post

Re: Computed column function permission

VolkerBarth — Tue, 29 Nov 2011 05:02:24 GMT

@Jeff: Might the noted CR number relate to a different topic?

That CR is documented with:

Misleading error messages would have been returned to the client when opening a connection using an invalid DSN. This problem has been fixed.

Re: Computed column function permission

jeff_albion — Tue, 29 Nov 2011 11:39:10 GMT

Thanks Volker - I accidentally CC'ed the CR number from the other thread I replied to. Fixed in the edit now.

Re: Computed column function permission

Former Member — Thu, 08 Dec 2011 10:01:57 GMT

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

Re: Computed column function permission

VolkerBarth — Fri, 09 Dec 2011 03:32:47 GMT

"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

that might be expensive to calculate (so they have to be calculated only once - unless the table is ALTERed, of course) and
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...

Re: Computed column function permission

thomas_duemesnil — Fri, 09 Dec 2011 06:15:38 GMT

That are my expectations too...

Re: Computed column function permission

Former Member — Fri, 09 Dec 2011 09:44:49 GMT

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

Re: Computed column function permission

VolkerBarth — Fri, 09 Dec 2011 09:58:57 GMT

@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?

Re: Computed column function permission

Former Member — Fri, 09 Dec 2011 10:14:40 GMT

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.

Re: Computed column function permission

VolkerBarth — Sun, 11 Dec 2011 15:04:42 GMT

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

omits the need to grant execute permission on the UDF for the users who simply are allowed to select from the table and
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...