RE: Hiding MS SQL databases in Enterprise Manager

• Previous message: Mason, Samuel: "IIS traffic"
• In reply to: Floyd Russell: "RE: Hiding MS SQL databases in Enterprise Manager"
• Next in thread: Panos Athanasiou: "Re: Hiding MS SQL databases in Enterprise Manager"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

To: <focus-ms@securityfocus.com>
Date: Thu, 20 Nov 2003 20:44:00 -0500

I think Floyd did a good job of explining the EM stuff. I wanted to chime in
here and earlier you asked a question about whether to let developers access
via EM and any security risks.

If we take a step back here, I think it was your first post that said that
you thought it was a security issue to allow the users to see the other
databases in EM. This being said, I firmly do not allow developers' access
to production servers but I can understand every environment is different.
If you do not want users that are connecting to the database server to see
the other DB's then you don't want to use EM period, I would suggest looking
at some of the web based products out there, you can check out
www.sqlmag.com for some suggestions.

What ever you decide, even if it is that users seeing the databases in EM,
just make sure you are 100% with the assignment of permissions on the server
and you should be pretty safe.

JimR

-----Original Message-----
From: Floyd Russell [mailto:floyd@neospire.net]
Sent: Wednesday, November 19, 2003 12:50
To: focus-ms@securityfocus.com
Subject: RE: Hiding MS SQL databases in Enterprise Manager

I think somewhere there is some mis-information begin spread.

Using Enterprise Manager does not inheritly mean that the user
is connecting with the sa account. Enterprise Manager can be
used by any SQL login with enough privileges to connect to the
server.

As for the whole hiding database problem, this is something that
I have fought for close to four years with no good solution. The
problem is that the stored procedure, sp_MSdbuseraccess, is first
called to get a list of databases that the user has access to.
But then immediately following this command is executed:
[Forgive the nastiness]

select name, DATABASEPROPERTY(name, N'IsDetached'), (case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name,
N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name,
N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name,
N'IsInRecovery') end), (case when DATABASEPROPERTY(name,
N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name,
N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status,
category, status2 from master.dbo.sysdatabases

This command takes no permissions into account, it just generates
the full list of databases in sysdatabases and their state. This list
is then used by Enterprise Manager to create the list of databases.

The stored procedure mentioned earlier, sp_MSdbuseraccess, is a very
ugly chunk of code. If I recall it does a double iteration of the
databases for no reason - slowing the overall initial connection. I
did some *tweaking* of it a long time ago to be overall more efficient.

It would be conceivable to limit the user access if Enterprise Manager
didn't use that nasty chunk of ad hoc SQL to get the list of databases. But,
as long as that code is executed all databases are going to be listed
for a particular SQL Instance.

Also, keep in mind that running multiple SQL instances on SQL Standard
Edition and making them accessible to web applications may not be permitted
by your license from Microsoft.. of course YMMV.

Floyd R.

---------------------------------------------------------------------------
---------------------------------------------------------------------------

---------------------------------------------------------------------------
---------------------------------------------------------------------------

• Previous message: Mason, Samuel: "IIS traffic"
• In reply to: Floyd Russell: "RE: Hiding MS SQL databases in Enterprise Manager"
• Next in thread: Panos Athanasiou: "Re: Hiding MS SQL databases in Enterprise Manager"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]