Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
haffej
Starting Member
4 Posts |
 Posted - 2008-08-05 : 16:26:57
|
| We have several databases on our server and want to create unique logins/users for each database. These are users for web applications, so we're looking to have them as secure as possible by only granting specific privileges like select, insert, update, delete, etc. on a specific database. Subsequently this means no access to system tables or other things that administrators or database owners can do. I've looked into schemas and logins and users and roles and it's a bit much to figure out in any timely way... I feel like this has been answered somewhere before, but I haven't come across anything real useful yet.Basically I want to be able to create a new user with basically NO privileges at all, then give the user specific privileges to a specific database and that's it. Can anyone help with a quick and easy way to accomplish this? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-06 : 23:14:46
|
| Did you read books online? It tells you how to create sql login, how to add it as db user abd how to grant permission. |
 |
|
|
haffej
Starting Member
4 Posts |
Posted - 2008-08-07 : 13:32:56
|
| Yes, I've read up a bunch on how to create logins, users and grant/revoke/deny permissions. The problem seems to be that all our tables are in the dbo schema, so giving SELECT, INSERT, etc. permissions on that schema also still allows access to system tables and such. Is the only way around this to actually create the tables using that new user? Or could I setup a default schema for the permissions part of it and then give specific access to users on a particular database using the schema as a permissions base?? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-07 : 22:46:48
|
| You don't have to grant permission in schema, can just grant on objects. |
|
|
|
haffej
Starting Member
4 Posts |
Posted - 2008-08-08 : 10:19:00
|
| But by granting on a schema, won't that stay in effect for any new objects that are added? I don't want to grant permissions on specific tables and have to modify permissions at any time thereafter. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-09 : 00:47:24
|
| That's ok if you like to as long as don't grant control nor take ownership permission to users. |
|
|
|
haffej
Starting Member
4 Posts |
Posted - 2008-08-12 : 09:41:18
|
| So the question is, how do I create that schema with only basic permissions like SELECT, INSERT, UPDATE, DELETE, etc. on the user-created tables (which were actually created in the dbo schema, like dbo.admin, dbo.user, dbo.event, etc.), and how do I apply that schema to a unique database user?Say I have this scenario:Database: testingTables: dbo.admin, dbo.user, dbo.eventUser/login: testingUser1) How do I create the schema with the basic permissions as noted above, with only basic permissions, which I can re-use for any database & user/login combination?2) How do I apply that schema to my testingUser user/login? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-12 : 23:24:18
|
| 1. you can't since schema is object in db.2. you can add user to schema, not other way around. |
|
|
|
|
|
|
|
|
Secure Logins for Web Applications