December 15, 2003 at 8:31 am
In my application, all database access is provided through a role called db_spexecute for the web user. This role has only execute permissions on application sprocs.
I have special tables for user-defined fields that I need ambiguous access to through a couple of stored procedures. I am querying the system tables and executing dynamic sql for selects, inserts and updates against the custom field tables.
I don't want to open these custom field tables for direct access outside the context of the stored procedures. So, is there a way to impersonate or elevate permissions within the stored procs?
December 15, 2003 at 10:44 am
There is no way to impersonate or elevate permissions within a stored procedure. Obviously, that's done for security.
I'm not sure what you mean by needing ambiguous access. Are you saying that you want to give only certain users rights to these tables? If you elaborate further, I believe you can accomplish what you wish.
December 15, 2003 at 10:59 am
By "ambiguous" I mean that I don't want to have explicit column names in the stored proc. Because the tables can vary by client, I don't want to code the sproc with explicit columns. Basically, I use OPENXML for inbound and FOR XML AUTO for outbound. The selects are simple, but inserts and updates require run-time discovery of columns in order to create valid sql statements.
The end result is that I need to execute dynamic sql against tables, and I'd prefer not to leave the tables exposed except through these stored procedures. But since executing dynamic sql, even in the context of a stored procedure, requires explicit permissions, I'm stuck leaving the underlying tables exposed to the role all the time.
December 15, 2003 at 11:11 am
How time critical are these? One thing you could do is have the proc insert some values into a temp table, including their user name (suser_sname()) and then have a job that runs every minute (as dbo) picks up new entries in this table and parses and executes the dynamic sql.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 15, 2003 at 11:17 am
These are real-time requirements. Any inserts or updates are immediately requeried by the web application. Otherwise, your suggestion is good. Queuing the updates would require that only select permissions be exposed to the web app account.
December 16, 2003 at 10:27 am
Have you tried using the Application Role functionality. Application roles bypass standard permissions.
December 16, 2003 at 11:09 am
Application roles don't bypass security settings, per se. Rather, they use whatever security settings are for the application role. Problem is, when you turn an application role on for a given connection, there's no way to turn it off.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 16, 2003 at 11:44 am
Are there any concerns with using Application Roles with the .NET managed Sql provider? I noticed several concerns with the loss of connection pooling and therefore performance, but those concerns seemed to center around ADO "Classic" and not ADO.NET.
December 16, 2003 at 3:28 pm
No idea whether or not this would work, but:
Create a user.
Give the user the permissions to do whatever needs to be done with the tables.
Create the stored procedure as belonging to this user.
Give all users who need it permission to execute the stored proc.
Can anyone say whether or not this would work, and (if not in particular) why?
R David Francis
R David Francis
December 17, 2003 at 11:23 am
No. Dynamic SQL executes in a separate batch from the calling stored procedure. Therefore permissions are automatically rechecked.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 17, 2003 at 1:21 pm
If Brian's response is in reply to my suggestion/question, I'm not recommending dynamic SQL....
R David Francis
R David Francis
December 17, 2003 at 1:25 pm
Unfortunately, Dynamic SQL is a key component since I don't want to manage exponential sprocs with the addition of every client. Each client's table is configured to their specs, but I only want one sproc to select and one sproc for inserts and updates regardless of the clients' configurations.
December 17, 2003 at 1:38 pm
My apologies - I'm following what you're saying now.
R David Francis
R David Francis
December 17, 2003 at 1:45 pm
No worries. The ideas have given me some options to consider. However, it appears that I will resign myself to granting select, insert and update permissions on the custom tables. The data is not likely to be sensitive in nature, but the idea is still a bit abhorrent to me.
December 17, 2003 at 2:04 pm
Perhaps you can tailor some of the access through views? It's not everything, but you may be able to restrict some access that way.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply