Re: Multiple Database Security - How to handle
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 09/17/03
- Next message: Sue Hoegemeier: "Re: MAX_ALLOWED Read/Write Objects"
- Previous message: M Sandico: "MS03-031 installation error :112"
- In reply to: Eric Brasher: "Multiple Database Security - How to handle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 17 Sep 2003 08:24:06 -0500
> Maybe if the stored procedure has execute permissions
> then the procedure is executed as admin.
There is no 'execute as' in SQL Server but you can simplify security
admin by using ownership chains and database roles. SQL Server will
bypass permission checking on referenced objects as long as all objects
involved are owned by the same login (unbroken ownership chain). The
exception to this is dynamic SQL so you'll need to grant direct
permissions on tables if you build and execute SQL dynamically.
Assuming the DM database contains tables that are accessed only by
stored procedures in the MIP and UWAY databases, you can setup security
as follows:
1) If all objects are owned by 'dbo' ensure all 3 databases are owned
by the same login so that the 'dbo' users map to the same login.
Execute sp_changedbowner if necessary. If objects are owned by on-dbo
users, ensure the object owners map to the same login.
2) Add database roles to the MIP and UWAY databases and add users to
these database roles
3) Grant execute permissions on procs to the above roles.
4) Add users to the DM database but do not grant any object
permissions. Alternatively, you can add the 'guest' user to the DM
database.
5) If you are running SQL 2000 SP3, enable cross-database chaining in
the 3 databases.
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Eric Brasher" <longday24@hotmail.com> wrote in message news:5a0301c37cd0$41f08770$a601280a@phx.gbl... > I would like to get ideas on how dba's are controlling > security for multiple databases. > > We have a datamart (DM) containing all of the Human > Resources information from pay information to sick leave. > > We have databases on the same server that house > processing data for applications. Like a Manager system > (MIP) that allows managers to control an incentive pay > program. Another is a United Way contributions > application (UWAY) > > The problems are security maintenance. I will have 5 > users using MIP that will need access to a stored > procedure that accesses DM to get employee info. To get > the security setup the 5 users need execute permissions > to the store procedure and permissions to the DM for the > tables accessed by the stored procedure. > > Now add UWAY. 5 more users using UWAY will need access to > a stored procedure that accesses DM to get employee info. > To get the security setup the 5 users need execute > permissions to the store procedure and permissions to the > DM for the tables accessed by the stored procedure. > > The security for the DM is getting crowded. Is there a > method to simplify this? Maybe if the stored procedure > has execute permissions then the procedure is executed as > admin. I want to maintain the most secure method > possible, but I can give >
- Next message: Sue Hoegemeier: "Re: MAX_ALLOWED Read/Write Objects"
- Previous message: M Sandico: "MS03-031 installation error :112"
- In reply to: Eric Brasher: "Multiple Database Security - How to handle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
