General security questions....

Question

General security questions....

horace-244264

Old Hand

Points: 326
More actions
January 7, 2008 at 9:37 am

#377893

Hi,
I'm trying to set up an internet project, but have little experience of SqlServer. I've read Microsofts SqlServer DBA Survival Guide, but am still a little confused. I'd appreciate your valuable help here.
Apologies if any of this seems dumb.......
I've installed SqlServer 2005 and created a Database called 'Horace'. The owner of this database is 'sa'. I've created 50 tables in the database and code that interacts with these tables.
Users will log onto the database remotely and be able to select, update, insert and delete from some of these tables.
I believe I need a user-defined database role to tie down access.
How do I create this role, how do I add users to it, and how do i tie down the appropriate permissions?
Please resist the temptation to flame, I need advise.
Many thanks.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 1

This sounds like a job for...duh duh duh dah...SCHEMAS! Check your SBO for schemas [SQL Server] and read up a bit on schemas and how to create/manage them. To me it sounds like what you need. Schemas will allow you to create different views for your users and gives you the flexibility to lock down certain tables/accesses depending on what they need.

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 2

When u create a database user u can give that user certain roles like db_owner ,db_ddladmin etc.These roles are fixed and u cannot add any more roles to that,out of these roles u can tick those u want fot that particular user.

horace-244264 Old Hand Points: 326 More actions · Answer 3

thanks.

I've looked at the standard database user roles, and they are not bespoke enough.

The system will have hundreds of users, so I need to create a role with the correct permissions, then just add new users to the role. How do I create such a bespoke role?

GSquared SSC Guru Points: 260824 More actions · Answer 4

Books Online has an article on creating roles on a server:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b0cd54ad-e81d-4d71-acec-8a6d7261ca08.htm

That should give you the data you need on how to do this. It's pretty simple.

(If you use domain security instead of SQL security, you need to create the role in the domain and then add it to the security of the server and so on from there.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

horace-244264 Old Hand Points: 326 More actions · Answer 5

thanks for the advice.

Had a rethink how I want to go about this. As SA owns the database, I want to create a new single user, then get SA to grant specific permissions to that user. This new user will require 'select', 'update' and 'insert' permissions, but not 'delete'.

So.....

I create a new user in Management Studio Security/Logins called 'ServiceUser'

In the Login Properties, this user has a default profile of 'Public

In the User Mapping, I select database 'Horace'

Questions

So that's my new user created, how do I go about locking the permissions down for that new user in Management Studio? I'm guessing I need to create a script as SA with, for example 'grant select on TableA to ServiceUser;' but this is the bit im unsure about

horace-244264 Old Hand Points: 326 More actions · Answer 6

horace-244264

Old Hand

Points: 326

January 8, 2008 at 3:20 am

#765638

Is this the best way of going about this?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

It depends on what you are planning to do with the user that was created. The best way would be to add the user to a role and then give access to the role to stored procs. That would mean no T-SQL statements can be executed using this user. The user can only execute stored procs.

Another thing you can do is add this user to the role Data_reader. This gives the user select permission.

Or you can grant select to the objects that is needed.

-Roy

GSquared SSC Guru Points: 260824 More actions · Answer 8

In Management Studio, open up the database, open up the Security branch, open Users, right-click the user you want to edit permissions for, open the Securables tab, use Add to grant permissions on tables, procs, functions, etc. That or a script. Either one works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

horace-244264 Old Hand Points: 326 More actions · Answer 9

Thanks again for your input.

If I go Server/Security/Logins

then right click on the ServiceAccount (that I created earlier), then select Properties.

I select the Securables Page, and click on the 'Add' button

It then asks me 'What objects do you wish to add?' but only gives me options of object types for Servers, Endpoints and Logins.

I want to select tables in the Midas Database, but I can't get to them this way.

Help!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

You probably did not give access for this user to Midas DB. Add this user Public rights to the Midas DB.

Once you have done that, in the DB node (Midas), there is a Security/Users as well. There you should be able to see this user. You can right click on the user and take properties and do what ever you want.

Or you can just give permission to the ServiceUSer to Midas DB as just Public and Open up a New Query window and give permission using T-SQL.

Grant Select on to ServiceUser.

-Roy

horace-244264 Old Hand Points: 326 More actions · Answer 11

Thanks, I'm making progress. I can now get a list of the tables (50 of them), but I dont want to click 150 times for insert, update, and select privileges.

How do I script this dynamically?

In Oracle I would

select 'grant select on '||table_name||' to serviceuser;'

from user_tables;

Not sure how to do this using Management Studio

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 12

horace (1/8/2008)
Thanks, I'm making progress. I can now get a list of the tables (50 of them), but I dont want to click 150 times for insert, update, and select privileges.
How do I script this dynamically?
In Oracle I would
select 'grant select on '||table_name||' to serviceuser;'
from user_tables;
Not sure how to do this using Management Studio

You can script it using GRANT command:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm

Simplified syntax for GRANT

GRANT { ALL [ PRIVILEGES ] }

| permission [ ( column [ ,...n ] ) ] [ ,...n ]

[ ON [ class :: ] securable ] TO principal [ ,...n ]

[ WITH GRANT OPTION ] [ AS principal ]

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 13

USE MIDAS;

Go

select 'grant select on ' + name + ' to serviceuser' from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

GO

I think that should work

-Roy

horace-244264 Old Hand Points: 326 More actions · Answer 14

thanks guys, great advice and help.

is there a spool command in sqlserver? Im not being lazy, it's just that I find BOL so un-intuitive right now being an (ex) oracle chap

in oracle it would be

spool c:\my_dynamic_sql.txt

sql statement;

spool off