Secure part of table

Question

Secure part of table

TRACEY-320982

SSChampion

Points: 13653
More actions
September 30, 2007 at 5:58 am

#374928

http://msdn2.microsoft.com/en-us/library/ms998292.aspx
Here it mentions the following
Deployment Considerations
When using Windows authentication to connect to SQL Server in production
environments, consider the following:
Use a custom service account.
Create a SQL Server login for a Windows group.
Assign database permissions to a database role.
Use a Custom Service Account
Trying to use Windows Authentication.
So im still confused.
1. ASP.NET uses the service account WEBGROUP
2. Assign WEBGROUP to database PERSONEL
Table Employees
Salary
Lets say Tracey logs into the web page.....and i have two pages
1. Employess
2. Salary
As the WEBGROUP is data reader viola Tracey sees all salary information.
Im trying to figure out that only Tracey can see Employees
But im not seeing the relationship when using DOMAIN\WEBGROUP and how i would know tracey accessed page and then put in database role
for Tracey (so she does not see salary information)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I think you might need two roles... one for WebGroup and one for WebGroupAdmin which is where you'd put Tracey. Then maybe do some correct grants?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

The web page is only using one Service account WEBGROUP

Then web guy would have no way to determine tracey belongs to WEBGROUP or belongs to WEBGROUPADMIN.

How would they pass one or the other to the SQL ?

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 3

If your web application is configured to use only a single identity rather than windows authentication you're going to run into trouble differentiating between users.

Ideally, you can set your web application to depend on windows authentication to grant access to appropriate resources (e.g. when usera visits the site the identity of the user is maintained/used to grant access to resources, etc.) At which point you can then grant each user membership in the appropriate groups (e.g. usera is in groupa only, userb is in groupa + groupb). You can then grant permissions to the objects in your database to the appropriate groups...

Joe

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

From what i can understand he sets the Application Pool to use a domain\WEBGROUP which in AD is service account....then some users are in a group and assigned to this WEBGROUP.

In sql this is DOMAIN\WEBGROUP

He is using windows authentication....How does he set up the different pools in IIS for different groups to come in to SQL i.e i would have DOMAIN\GROUPA and GROUPB in SQL (which is exactly what i want).

For him how does he determines this in his pages?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

You set up the IIS app pools to use different service accounts which then map to different app roles in SQL. You can then set up different app pools on separate IIS directories (virtual or physical).

the directory structure is the key - you specify windows access by (virtual or physical) directory as well (in IIS). Therefore you'd have:

/Salary holding salary info, service account 1, windows access WebGroup

/employee holding other employee info, service account 2, windows access Tracey only

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

Think im with you.

On the IIS can this be on virtual directory and (Many Groups defined here).

GROUP A GROUP B etc

Or would it have to be a one to one relationship on IIS

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

there's two sides to the IIS: the "user to IIS" side and the "IIS to SQL" side. The article is talking about mapping the IIS App pool service account (i.e. the IIS to SQL side) to a single AppRole, which then determined the security level allowed for that group. So - it's a one to one from IIS to SQL.

In order for there to be two access levels to "SQL things" using this model, you'd then need TWO IIS App pool service accounts, meaning two app pools. And since the most granular level you can tie an App Pool to is a folder (whether virtual or not) in IIS, you would need to split the forms into two separate folders. One folder for all of the things the "salary only" group can do, and one for the stuff the "employee group" can do.

Now as to the users - you simply control that through IIS-level ACL's (or the membership stuff in ASP.NET). That means if you're running your site using Windows Auth, you could have one or more Windows groups, one or more Windows users and/or any other number of users (authenticating through whatever other method is allowed through IIS security) "map" into the SQL App role you've established. So on this side, it's a many-to-one on the "outside to IIS" part of this trip.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184393 More actions · Answer 8

When you are using Integrated Security from an ASP.NET Web application the application connects to the SQL Server using the IIS App Pool user (WEBGROUP), the SQL Server has no way of knowing who with that group is actually connected to it. In order to limit access to the data in the database based on the actual user you would need to either include security in the application/database where the Web app passes the actual user name to the database and then you have code that controls access to functionality either within SQL Server or the Web App. Alternatively you can research ASP.NET impersonation and delegation in order to determine how to pass the actual User information to SQL Server. For impersonation I believe you need to be using Kerberos.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Thanks for replying. Im the DBA and i done some web programming not to the iis pool level. So i will play around with the groups and pools with the web guy.

I keep you posted if i find out more information.