on 2011 Nov 22 9:44 AM
[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?
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
"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
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...
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
@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?
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.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:)
@Jeff: thanks for the post
@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.
Thanks Volker - I accidentally CC'ed the CR number from the other thread I replied to. Fixed in the edit now.
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():
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
75 | |
9 | |
9 | |
8 | |
8 | |
7 | |
7 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.