March 10, 2008 at 10:43 am
I am dynamically creating a select statement in a stored procedure based on the passed arguments. This statement is then executed as exec (@xxxx). Executing the procedure as dbo there are no issues. Executing as a normal user the exec fails because the user does not have select permissions on the tables. The user belongs to a role; this role is granted exec permission on the procedure.
I am relatively new to SQL Server, but a long time Sybase DBA and developer. In Sybase I had the same problem, but got around the problem with temporarily setting the current user's role to a role with dbo permissions just prior to the exec statement. Immediately after the exec the temporary role was rescinded.
I am of the impression that I need to do a similar thing with SQL Server, but I can not find anything that details exactly how to do this and it appears that there is not equivalent to Sybase's "set role" in SQL Server.
Any help would be appreciated.
March 10, 2008 at 9:25 pm
Check out "EXECUTE AS" in Books on Line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 8:48 am
Thank you for the response. I changed the role creation so that role that has execute permission on the procedure could impersonate sa. I then added an "execute as login = ...' prior to the exec immediate. It solved my problem.
March 11, 2008 at 8:53 am
Glad I could be of help. I wouldn't recommend using sa as the account though. You should have an account that just has the necessary permissions. You should make sure you also do the REVERT in the procedure as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2008 at 7:35 am
Why not code the procedure with WITH EXECUTE AS OWNER vs. a specific user id?
If dbo owns the procedure, then the impersonation will occur under the covers. That is what I am doing (for those few cases where I need to code dynamic SQL DML statements).
Works fine.
Summary:
- stored procedure created by dbo
- procedure has WITH EXECUTE AS OWNER
- Role created
- Role can execute procedure (GRANT EXECUTE on sproc to RoleName)
- Role granted to (run-time) database user mapped to LOGIN (integrated or SQL Server)
March 13, 2008 at 6:38 am
"with execute as owner" is the best way around that issue, but be very careful that you can't use that procedure to incorporate SQL INJECTION.
There are some great examples out there but even passing something like a table name can be
Here is a simple example where the SP parmeter is a table name.
Assume:
...
@tableName sysname
...
select @theSQL = 'select id,name from ' + @tableName
insert into tblTheDestTable
exec (@theSQL)
Looks harmless doesn't it?
Assume that someone calls the procedure with
@tableName = 'tblTheTable drop table tblTheTable'
.... 🙁 What will happen?
March 13, 2008 at 6:44 am
FYI a way around the problem above.
Pass the object_ID of the table rather than the name..
...
@theTableID = object_id('tblTheTable')
...
select @theSQL = 'select id,name from ' + quotename(name)
from sys.tables where object_id = @theTableID
March 13, 2008 at 7:14 am
"with execute as owner" is the best way around that issue, but be very careful that you can't use that procedure to incorporate SQL INJECTION.
Agreed. Prevention of SQL injection is an entire topic in itself.
In my case it wasn't user input that I was executing dynamically. It was a DML statement that needed a variable for the column name.
Our application supports "custom (predefined) fields". When a field is deactivated the user has the option of clearing (i.e., setting to NULL) all old values entered into the field (column). Since you can't use a variable for a column name in a dynamic SQL statement and to avoid having 100s of "IF" tests (one for each column) in the stored procedure, I coded the UPDATE MyTable SET = NULL as a dynamic SQL statement. Naturally, it failed when the procedure was run under the context of the application.
Note: Oracle supports the same context switching (Invoker Rights Versus Definer Rights) in PL/SQL via the AUTHID CURRENT_USER | DEFINER syntax. However, the default is reversed. I.e., by default, the statements execute with the privileges of the owner, not the current user.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply