June 11, 2003 at 1:31 am
Hi Anyone,
I need to setup a MS SQL Server 2K to provide a DB-hosting service for customers who want make their dynamic web-site with MS SQL DBs. Who know what's the best setup security to provide this type of service? Considering that the users of each DB must have the right permission for:
CREATE NEW TABLE, ALTER his TABLE, CREATE NEW SP and excute it, CREATE NEW DEFAULT and use it, all of this as a db_owner but without permission on the following: him cannot view the pannel property of his DB; him cannot view and alter directly the system tables of his DB; him cannot excute the system procedure but can create new of his own use ecc.
Just a observation: I connot use the db_owner role because it is most large permission and it's no good to assing it to a webmaster that must administer his DB! The public role by default permit to excute system store procedure tha can inform the user on system private data (data of server, data of other DBs ecc.).
Sorry but I'm very truble with it!
Have someone done something about this type of setup?
Thank you.
June 16, 2003 at 12:00 pm
This was removed by the editor as SPAM
June 16, 2003 at 10:15 pm
Hello.
One possibility is to use a stored procedure for the more sensitive roles. A stored procedure can have more rights than the User executing it, and the stored procedure can be used to ensure appropriate use (assuming individual users can be identified, User A only can create tables on database A).
Everett
Everett Wilson
ewilson10@yahoo.com
June 17, 2003 at 7:04 am
You can explicitly grant statement permission rights within a database. For instance:
GRANT CREATE TABLE TO HostedCustomer
However, prohibiting acccess to system tables and system stored procedures is a bit more problematic. By default, the public role has access to many of these, especially in the master database. Since the guest user must be present in the master database, that means the user will have rights.
The easiest way, probably, to handle this is a web site that only performs the functions you want them to have. Kind of like a MyPHPAdmin (for MySQL), but for SQL Server.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply