May 25, 2006 at 2:36 am
Hi
If I need to do the following - do I have to grant db_owner to application users - &, if so, is this generally acceptable practice?
Environment is .NET + SQL Svr 2000 (mix of C# generated SQL & Stored Procs). User access to application is via Windows authentication. The application may share a Server with other databases. Requirements/constraints include:
Functions:
- Create/alter/drop Tables, Views, Stored Procs, Functions
- Select, update, delete, insert Table data
- Select View data
- Select from information_schema tables (& see all objects in database)
- Execute Stored Procs/Functions (including those created)
- Execute dynamic SQL (which may do all/most of the above)
Need to create objects:
- in database (ie: not tempdb)
- object ownership must be "dbo"
Would appreciate your views on what is an appropriate "real world" approach.
Thanks.
May 25, 2006 at 3:23 am
David
Never give a user db_owner access - not if it's your job on the line when it goes wrong.
First, I would question why an application user needs to create objects in the database. If it's one of those off-the-peg applications written for small shops that don't have a DBA then you may not have any choice - except to use a different application. If you put users in the db_ddladmin role then they should be able to do everything you mention above. Don't forget they must qualify objects with dbo when they create them, for example CREATE TABLE dbo.MyTable (col1 int, ...). If the application doesn't have a facility to do this, you can create a job to run every night that looks for objects owned by the wrong owner and changes them to dbo (I have a script to do this if you're interested). If you don't want to do that, give them dbo and sit back and wait for something to go wrong!
Hope that helps
John
May 25, 2006 at 5:18 am
Hi John
Thanks for that and the table qualification point (I'd been struggling to find a solid reason to always specify "dbo." - since it seems redundant - and this is definitely it!).
So regarding the functional requirements, which are:
(a) Create/alter/drop Tables, Views, Stored Procs, Functions
(b) Select, update, delete, insert Table data
(c) Select View data
(d) Select from information_schema tables (& see all objects in database)
(e) Execute Stored Procs/Functions (including those created)
(f) Execute dynamic SQL (which may do all/most of the above)
These could be implemented/constrained as follows:
(a) to (d) would be covered by: db_ddladmin, db_datareader, db_datawriter but ban SP/UDF creation in database (this requirement is a "nice to have" is is not essential)
(e) grant execute permission to all SPs/UDFs (which is no prob)
(f) Limit dynamic SQL to cover functionality in (a) - (d)
This gives me the functionality I need. Any issues with having db_ddladmin, db_datareader, db_datawriter access?
Cheers.
David
PS To answer your point on why object creation - the application deals with table meta-data & instanciates user-defined structures - so is mainly concerned with creatng new tables/altering existing tables etc.
May 25, 2006 at 6:07 am
David
The problem with instantiating user-defined structures is that you may end up with crap table design, poor indexing and missing referential integrity. You, as the DBA, will probably spend more time looking out for, and sorting out, things like this than the developer would have spent designing a properly normalised database.
Rant over - back to your question. I prefer granting permissions on individual tables to putting users in the db_datareader/writer roles. If you ever need to revoke or deny permissions on one table then it's difficult to sort out if all users have blanket permissions. Permissions can be granted as part of the table creation process or by a daily job. The db_dlladmin role will allow users to create stored procedures and functions as well as tables and views - there is no way of separating that out.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply