April 12, 2012 at 11:45 am
I have an application that I'm the maintainer of. It's a windows desktop application and requires a SQL Server back end. I have no control over the databases I just write the software, people download the application and run it against their SQL Server.
The application basically uses one column of one table to store all it's data(a sql_variant). It has a dynamic GUI designer that allows people to build forms on the fly with no programming or SQL knowledge. So, I use one huge, performance sucking, table to store everything in and then use pivot tables to dig it all out.
In order to make the CRUD operations for the various forms less complex, I create 3 stored procedures for every form they build (For insert, update and delete) as well as a view that just contains the select/pivot query. So this gives the illusion that you're working with a real individual table for each form and not a nasty pivot table query on one table.
What happens is, a user designs the forms, it stores the field descriptions (text areas/drop downs/data types etc..) into a table, then I have a stored procedure that generates dynamic sql to build the procedures and views based off those field descriptions. Anytime a user wants to build a new form, this procedure has to be executed.
Now mostly this is a non issue because for the most part this is a single user desktop application and the user is their own DBA on their personal computer. Recently the application has been being installed in server farms where a DBA controls all the access. They do not like the elevated permissions required for the users to be able to create/drop/alter views and stored procedures. So, what I'm wondering is, is there a way that I could setup the permissions and grant them to ONLY the stored procedure that creates the procedures and views. i.e. the user could run the procedure, it would create the views and procs but they couldn't create views and procs?
currently every user that has an access level high enough to create new forms gets put into one of two roles db_manager or db_writer.
here is what I execute to give them access to do this
EXEC sp_addrolemember 'db_owner', 'db_manager'
GRANT ALTER ON SCHEMA::dbo TO db_manager WITH GRANT OPTION
GRANT CREATE PROCEDURE TO db_manager WITH GRANT OPTION
GRANT CREATE VIEW TO db_manager WITH GRANT OPTION
EXEC sp_addrolemember 'db_owner', 'db_writer'
GRANT ALTER ON SCHEMA::dbo TO db_writer WITH GRANT OPTION
GRANT CREATE PROCEDURE TO db_writer WITH GRANT OPTION
GRANT CREATE VIEW TO db_writer WITH GRANT OPTION
Suggestions?
April 12, 2012 at 11:52 am
SQL 2008, right?
Don't elevate the user permissions. As you already know, the DBA's won't like it and a security audit will probably ding you for it.
Instead use EXECUTE AS in the stored procedures to elevate the permissions without the user needing anything special. Have the DBA create a user with the needed permissions (that noone but you and he will know about).
Then at the parts of the stored procedures that require elevated permissions you can do this:
EXECUTE AS [username]
[run the statements here]
REVERT (very important to be sure the elevated permissions do not continue past what is needed)
April 12, 2012 at 11:59 am
Currently the application is compatible with 2005 and 2008. I can probably drop the 2005 support for future releases if needed. This is actually a big point of contention for this software (for obvious reasons). It was developed as a single user desktop application with multi-user supported added as an afterthought and now agencies are picking it up and wanting to deploy it department wide on SQL Servers buried inside federal government server farms. Thanks for the suggestion, I'll look into it, it looks like it will work. I'll have to script the custom user creation when the database script gets run, for 90% of the installs, there will be no DBA so this stuff has to be automated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply