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 |
|
Kbalz
Yak Posting Veteran
94 Posts |
 Posted - 2010-01-26 : 09:26:20
|
| Hello, I have a sql server user which is used by my public website. I wish to limit this user to only SELECT from one tables.The sql user is mapped to a database user. Currently the user is in the default roles of db_datareader, db_datawriter, and public. The user has a default schema of dbo. I can log in to the server as this user, and perform update/delete/select/insert on any table.I don't fully understand how schemas, roles, and user permissions interact to control security. Currently, I have to grant users permissions to execute stored procedures/functions.Where do I start? Thanks! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 09:32:30
|
| remove the user from database roles db_datareader and db_datawriter.then GRANT SELECT on [TABLE_NAME_HERE] to [USER_NAME_HERE] |
 |
|
|
Kbalz
Yak Posting Veteran
94 Posts |
Posted - 2010-01-26 : 09:37:37
|
| russell, thanks!What would cause the need for me to add stored procs and functions to each user? I do this by right clicking the user, securables, then add the proc to the list, then grant execute.This security was setup before I started, I'm just trying to figure out where that requirement is setup. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 09:39:37
|
If the user will only use Stored Procedures the problem is most easily solved. You grant EXECUTE permission on the SProcs the user needs. The user does not need ANY permission to Select, Update, Delete, etc. tables directly. (When you Execute a Stored Procedure it itself runs with the permissions of the person who created the Sproc).Thus the user may be able to run an Sproc that, say, deletes a single record - but they cannot delete the whole table (well ... they have to delete the records one-by-one using the SProc  )If you provide SELECT / UPDATE / DELETE permissions on the table the user can use those to perform any task they like. So its much less controlled.One way to provide "selective" control is to provide SELECT permissions ONLY on Views. The VIEWs can restrict the data displayed to only the subset of data you want the user to be able to see.You can provide a view owned by "kristen" - e.g. kristen.stock (so a view called "stock" owned by "kristen").You can also have an underlying table also called "stock" - usually owned by "dbo" - so the actual table would be dbo.stock.When I login and do "SELECT * FROM STOCK" I will actually see data from kristen.stock, rather than the underlying table. So that can also be used to "partition" or "restrict" the viewing of table data.Or you can do it in an application. Allow the application to login (i.e. using SQL User ID and Password that the user doesn't not know) and then restrict what the user can do in the application - so the application itself would have a User table, a Login page, and some sort of security system to decide what the user can, and cannot, do. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 09:39:56
|
Well, the user has no permission to do anything until explicitly granted. Adding users to database roles is a shortcut so you don't have to explicitly grant permission on each object.If you have several users that need the same permissions on multiple objects, create a new role, grant permissions to that role, then add users to it. i see i was typing at the same time as Kristen  |
|
|
|
Kbalz
Yak Posting Veteran
94 Posts |
Posted - 2010-01-26 : 09:49:07
|
| Thank you both. The only security I've ever managed was just on a personal express version, which I'm always connecting as sa or db_owner roles, so this is all new to me :)We're handing this application and sql login off to a sister company, so these tips will help limit them. Thanks! |
|
|
|
|
|
|
|
|
Very limited account settings