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

[jeff_albion]

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,

[VolkerBarth]

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

[Former Member]

@Jeff: thanks for the post

[VolkerBarth]

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

[jeff_albion]

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