Re: Security between databases
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 11/18/02
- Next message: Mary Chipman: "Re: Probelms connecting to SQL Server"
- Previous message: Tom Moreau: "Re: Controlling access with custom groups"
- In reply to: Stuart Amos: "Security between databases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Mon, 18 Nov 2002 08:50:15 -0600
Objects must have the same owner in order to maintain an unbroken
ownership chain. When objects reside in different databases, the object
owners need to map to the same login. The user of the application still
needs to be a valid user in all databases involved but permissions on
the referenced objects are not needed; the user needs permissions only
on the directly referenced objects. See the sample script below. Also,
you might consider adding the guest user (without permissions) to DB2 if
objects are accessed only indirectly.
For more information on cross-database ownership chains, see July 2002
SQL Server Magazine article
<http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25145>. You need to
be a subscriber, though. Also, check out
<http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272424>
regarding inter-database ownership chains.
EXEC sp_addlogin 'MySystemLogin','MyPassword'
EXEC sp_addlogin 'MyApplictionLogin','MyPassword'
GO
USE DB2
GO
EXEC sp_adduser 'MySystemLogin', 'DB2_System'
EXEC sp_adduser 'MyApplictionLogin', 'DB2_User'
GO
CREATE TABLE DB2_System.Table2
(MyColumn int NOT NULL PRIMARY KEY)
GO
CREATE PROCEDURE DB2_System.GET_DB2_Table1
AS
SELECT * FROM DB2_System.Table2
GO
USE DB1
GO
EXEC sp_adduser 'MySystemLogin', 'DB1_System'
EXEC sp_adduser 'MyApplictionLogin', 'DB1_User'
GO
CREATE PROCEDURE DB1_System.GET_DB1_Table1
AS
EXEC DB2.DB2_System.GET_DB2_Table1
GO
GRANT EXECUTE ON DB1_System.GET_DB1_Table1 TO DB1_User
GO
-- 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 ----------------------- "Stuart Amos" <Stuart.Amos@Winterthur-life.co.uk> wrote in message news:a9bae80c.0211180305.73c8b9da@posting.google.com... > I'm having problems getting the level of security I want between two > SQL Server databases. > > I have two databases DB1 and DB2. > > DB1 has a table called DB1_Table1. This table is owned by a user > called DB1_System. DB1_System has SELECT rights on the table. The > database also has a stored procedure called GET_DB1_Table1 which is > again owned by DB1_System, this selects records from DB1_Table1. > > A further user called DB1_User has been added to DB1. DB1_User only > has EXECUTE only permission on GET_DB1_Table1 procedure and so cannot > do any changes to DB1_Table without the procedure. > > The above model is repeated for DB2. > > Now I want to modify GET_DB1_Table1 in DB1 so that it selects records > from DB2 by calling a GET_DB2_Table1. > > This only seems possible by granting EXECUTE rights to DB1_User on the > GET_DB2_Table1 procedure. What I want is to grant rights to > DB1_System not the calling account of GET_DB1_Table1. > > The reason is that I want my web server to have execute only rights to > one datasource only. The second database contains sensitive data and > granting the web server access to this is unsecure.
- Next message: Mary Chipman: "Re: Probelms connecting to SQL Server"
- Previous message: Tom Moreau: "Re: Controlling access with custom groups"
- In reply to: Stuart Amos: "Security between databases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
