SQL Server 2005: User security management: User Ch

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server 2005: User security management: User Ch

Author

Topic

Maxer
Yak Posting Veteran

51 Posts

Posted - 2007-06-25 : 08:45:09

How do you handle user level security with SQL Server 2005?

Say I have an HR database.

In Active Directory I have two groups: Managers, Employees.

Now in this HR Database I want to setup permissions in such a way that Managers can see all employees under them (but not other managers) and the employees can only see themselves.

(I'd have various levels of management defined in a table somewhere, so that each employee has a manager ID that links to another employee so that the CEO would be manager of everyone by working down the chain).

What I'm trying to understand is the best way to handle the permissions.

I'm not entirely clear on how to deal with that.

Would I use user chaining to do that, I wouldn't need impersonation (that's just for instances where you want dynamic SQL and it won't execute with user chaining, correct?)

Anyway, just looking for some general direction on this (obviously I need to get a good book it would seem).

Would I create a stored procedure that runs with EXECUTE AS permissions so that I'd have a non-interactive login it uses that has table access then all the other users have permission to execute the sproc?

So that sproc runs, pulls back a SELECT * FROM tbl_HRINFO and using a WHERE constraint limits who is returned WHERE SupervisorID = CurrentLoggedInEmployeeID ?

Also: How can I determine who is logged in and running the procedure, would the sproc use the SELECT USER_NAME command to see who was running it?

As you can see, I'm working from square one on all of this.
Not sure if my posting entirely made sense, but hopefully someone can get me pointed in the right direction, thanks!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2007-06-25 : 09:51:57

Makes some sense... see if this is any help:


select system_user()
create table CheckEm
	(i int	identity(1,1),
	salary	money,
	emp		varchar(255))
go

insert into checkem (salary,emp) values (100000,system_user)
insert into checkem (salary,emp) values (200000,'RR49')

select * from CheckEm
select * from CheckEm where emp = system_user
create view vwCheckEm as select * from CheckEm where emp = system_user go

select * from vwCheckEm

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Maxer
Yak Posting Veteran

51 Posts

Posted - 2007-06-25 : 10:16:48

It looks like you are creating a table, for your example then in one of the fields you are storing the system's user information.

So then in my HR table I would need to store their login/user name and then use a view to feed data back to the users that restricts by that user's system login info in the table.

However, can I setup actual permissions on the table as well, or would that be a waste of resources to setup field level permissions (since they have a view or a stored procedure that would handle the data pulling so they aren't allowed to just SELECT * from the table on their own?)

What about a supervisor wanting to see everyone below him/her? Would I have the supervisor first pull their record in the view and then that view could also search the table for anyone else who had that supervisor listed in their "SupervisorID"?

Last question, more general: Would someone be able to give me some general direction on owner ship chaining and user securities?

I know I shouldn't give users access to the tables, but should instead use views and stored procedures.

However, I'm having trouble finding "real world" examples of this practice. I guess I'm after a general discussion on user securities best practices as well.

Thank you again for your help!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2007-06-25 : 12:35:15

I was thinking you would need a schema that caters for Manages,ManagedBy relationship - I was simply showing the possibility of using the view with system user. If unaware of a way to manage this, other than having explicit sets of permissions per user, which will be quite a lot of dba maintenance

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources