UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Permissions - Functions In Computed Columns, Any Versions    
 
   
PDTech
post Apr 20 2017, 06:49 AM
Post#1



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


I'm experiencing some issues trying to give users access to a particular table in my database.

The table is in a schema. I have a role which grants Select and Execute permissions on the schema.

The table contains some computed columns which use scalar valued functions. The functions are also part of the same schema.

Members of the role are able to view the other tables, no problem. But they can't view the table with computed columns that use the functions.

Users are viewing using MS Access. Instead of an ODBC error (which they get on tables to which they have no permissions) the table opens but all the fields display #Name.

Any thoughts as to what is causing this and how I can get around it?

Thanks
Paul

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 07:36 AM
Post#2


UA Admin
Posts: 29,977
Joined: 20-June 02
From: Newcastle, WA


I've not seen this particular problem before.

Are you able to view the table properly via SSMS?

How about a view based on this table? Can you create that view and link to it in Access?

How about a Passthru query based on a SELECT * FROM tblYourTableName? Same problem if you use that? You can write Passthrus that execute Stored Procedures, returning the records that way? Does that work?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 10:02 AM
Post#3


UA Admin
Posts: 29,977
Joined: 20-June 02
From: Newcastle, WA


I've also found that you can only use deterministic functions in persisted Calculated Columns in SS. What are your functions doing?
This post has been edited by GroverParkGeorge: Apr 20 2017, 10:09 AM

--------------------
Go to the top of the page
 
PDTech
post Apr 21 2017, 12:49 AM
Post#4



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


The functions are concatenating several rows from related tables.

In concept, think of a parent 'orders' table that has a number of products in an 'order details' table. The function lists each unique product for a given order as comma separated values by running a cursor over the related table. The fields based on the functions are non-deterministic and therefore cannot be persisted.

The user has Execute rights on the function and Select rights on the parent table and referenced tables. The user can open the other tables to which they have been given Select rights without problems. There is another role with additional rights and when made a member of this role, the user *can* open the table. So the issue is definitely seems permissions related - I've just been unable to figure out what additional permissions are needed.

I think I'm going to change approach and replace the functions with the 'xml for path' trick for concatenating rows, that way I won't need the functions any more and may get a performance boost (the system was originally developed/run on SQL Server 2005 where I think supported the FOR XML command, or at least, I didn't know about that approach at the time).

A couple of quick questions if I may. Assuming there are no DENYs in place:
1. If you give a user rights to Execute a scalar-valued function which selects from other tables - must the user have been given explicit Select rights on those tables for the function to return values?
2. If a user is given rights to Select on a view - must the user have Select rights to all the tables referenced by the View?

My understanding was 'no' to both, but this issue has me doubting my understanding!

I'm finding setting permissions a pain in our corporate environment as I don't have a test account. Is there a handy way of checking if a given role gives the correct permissions to open a given table/view or execute a function? I've looked at 'Execute As' (the user ID) but when I try to GRANT IMPERSONATE for myself to the user I get the error that I can't GRANT for myself. I don't see any way to Execute as *Role* which is what I would really like for testing permissions.

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
GroverParkGeorge
post Apr 21 2017, 09:54 AM
Post#5


UA Admin
Posts: 29,977
Joined: 20-June 02
From: Newcastle, WA


Sorry to say I can't really address your questions regarding permissions definitively.

I will, however, see if I can't get more experienced, knowledgeable eyes on this discussion.

--------------------
Go to the top of the page
 
PDTech
post Apr 21 2017, 10:44 AM
Post#6



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


Think I have found the issue.

I had a default constraint for a 'Created By' field which was calling on a scalar valued function that returns the Windows Login ID and I had not GRANTed EXECUTE permissions on it for Role that was having problems. As this was in the defaults I'd managed to miss it. Still need to test it but think this is likely the problem, even though the Role only had SELECT permissions on the table.

The function was simply stripping the domain from the windows login and I had created a scalar valued function for that even though it is easy enough to write the full expression into the default constraint. Seemed like a good idea at the time...

I'm retrofitting security/schemas/roles onto an existing system that previously had a small audience that could simply be given read or read-write permissions to pretty much everything.

Lessons for myself from this:
1. Limit use of Scalar valued functions where possible.
2. Even if you don't expect a system to grow to where it requires sophisticated permissions, assume it will do so and create schemas from the start, including a 'General' schema into which you can place objects that are not sensitive and ensure all Roles have Select/Execute on the 'General' schema.

I would still be very interested to hear how others test their permissions and ensure everything is working as expected when running in Windows Authentication mode. Is it common practice to use dummy accounts or are there ways of using IMPERSONATE and EXECUTE AS to test if a given ROLE can SELECT/INSERT/UPDATE/DELETE/EXECUTE with a particular object.

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th July 2017 - 09:39 PM