Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / January 2008

Tip: Looking for answers? Try searching our database.

Hosting / List of databases in Management studio question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Softlion - 05 Jan 2008 12:46 GMT
Hi,
I'm trying to set up a login and a database, in order that :
* The login can do anything inside its database, except dropping it or using
'dangerous' sql commands
* Once logged using Management Studio, express or not, the login can see its
database only (select * from sysdatabases should return only one database)

I've checked MS hosting white papers.
I've tryed 2 differents configurations without full success.
Lastly I thought I could change the sys.databases view for my needs, but it
seems it can't be modified...

The hosting security white paper says you just add a user with this login in
one database and make him db_owner.
I tryed, it don't work: the database does not appear in "select * from
sys.databases" (nor in Management Studio browser)

One blog post says you just have to set the database owner to the login.
I tryed, the database appears correctly in the browser, but the login can do
any dangerous operations ... (changing database files locations, dropping
its database,  ...).
Also the "sa" login appears in Management Studio, along with master and
tempdb ... (probably because it is required). So what's the need to change
the admin login name from sa to anything else as stated in the security
white paper if it is still displayed to every customers ?????

Any solution from my main question ?

Thanks,
Benjamin.
Erland Sommarskog - 05 Jan 2008 15:57 GMT
> I'm trying to set up a login and a database, in order that :
> * The login can do anything inside its database, except dropping it or
> using 'dangerous' sql commands
> * Once logged using Management Studio, express or not, the login can see
> its database only (select * from sysdatabases should return only one
> database)

The only way to achieve this is to make the login the owner of the database
and then DENY VIEW ANY DATABASE to that login. Or REVOKE VIEW ANY DATABASE
from public.

However, even if the login cannot see the database in sys.databases or
the Mgmt Studio dropdown, the user can still do "USE db" from a query
window.

As long as the login has only one database, this is not that big deal:
make the database the default database for the login.

If the user has access to several databases, it becomes more difficult,
not the least in Object Explorer. I have a request on Connect to add a
VIEW DEFINITION permission per database:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackI
D=273830

> The hosting security white paper says you just add a user with this
> login in one database and make him db_owner. I tryed, it don't work: the
[quoted text clipped - 5 lines]
> can do any dangerous operations ... (changing database files locations,
> dropping its database,  ...).

It does not matter whether you own the database or just belong to the
db_owner role, you can still drop the database. In your case, the
user should have CONTROL on the dbo schema, and also have the
rights to execute commands like CREATE TABLE, CREATE PROCEDURE etc.

I can't but agree that the security model for SQL Server is not very
well suited for a hosting scenario.

> Also the "sa" login appears in Management Studio, along with master and
> tempdb ... (probably because it is required). So what's the need to change
> the admin login name from sa to anything else as stated in the security
> white paper if it is still displayed to every customers ?????

You still make it difficult for intruders without own access to the
server to bang their way in.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Softlion - 06 Jan 2008 12:09 GMT
Thanks I added my vote the the connect entry.
B.
___________________
http://www.softlion.com/
Erland Sommarskog - 06 Jan 2008 14:54 GMT
> Thanks I added my vote the the connect entry.

I forgot to say one thing: it's possible that you can constrain the
user from dangerous actions with server-level triggers. For instance,
you could have a server-level trigger for DROP DATABASE that checks if
the user is permitted to drop databases according to your own security
table. In this way you could permit clients to be db_owner of their own
databases and restrict them from doing things that affect things outside
their database.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Softlion - 23 Jan 2008 23:39 GMT
> I forgot to say one thing: it's possible that you can constrain the
> user from dangerous actions with server-level triggers. For instance,
[quoted text clipped - 3 lines]
> databases and restrict them from doing things that affect things outside
> their database.

Nice idea, I don't know it was possible - well I don't know that much about
triggers - I will check that.
___________________
http://www.softlion.com/
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.