Row-level security and MSSQL

[Bradley.. .. . . .. ..]

Requirement: managers can login and only see employee data for the
department they manage and any sub-ordinate departments.

The departments are in a table that defines a simple tree structure with
DepartmentID and ParentDepartmen tID.
The Employee table holds the DepartmentID that the employee belongs to.

First issue is how to tie an Access or MSSQL login to an employee
record. In Access this is fairly simple as you just make it part of the
procedure of creating new users to add the login name to a field on the
Employee table. But what about in MSSQL?

I've created Stored Procedures to which I pass the Employee's
DepartmentID. It returns all the employee records for employees
belonging to that department or any sub-ordinate departments. The
problem is a user could pass any value to this stored procedure and get
access to data they should have access to. Any ideas?

Also, I'm wondering what is the best method of applying security. Have a
basic/common MSSQL security level but then use my own username/password
field on the employee table? Or use full MSSQL users/roles? Being able
to add/remove MSQL users from Access would help as is makes it easier to
tie the login to an employee record....

Thanks.
--
regards,

Bradley

[Tony Toews]

"Bradley.. .. . . .. .." <no:spam@noemai l:com> wrote:

First issue is how to tie an Access or MSSQL login to an employee
record. In Access this is fairly simple as you just make it part of the
procedure of creating new users to add the login name to a field on the
Employee table. But what about in MSSQL?
A bit of digging around found me the following in Books Online.

DECLARE @sys_usr char(30)
SET @sys_usr = SYSTEM_USER
SELECT 'The current system user is: '+ @sys_usr
GO

which returned me

The current system user is: LAPTOP\ttoews

Not exactly what you are looking for but close. Hmm, ah, LAPTOP is the name of my
domain as I'm logged on locally. So that's even better. But I'm sure there's a bit
of string manipulation you can do in T-SQL and get rid of what's to the left of the
"\".
Also, I'm wondering what is the best method of applying security. Have a
basic/common MSSQL security level but then use my own username/password
field on the employee table? Or use full MSSQL users/roles? Being able
to add/remove MSQL users from Access would help as is makes it easier to
tie the login to an employee record....

Nah, use Windows authentication. Which is what the above T-SQL code is. Use SQL
Server security and such, about which I know next to nothing, to grant access to the
database and queries and such.

Using your own username and password is very much duplicating what is already
available to you and built in to the system. Thus it should be much more secure.
Finally it's one less password for the user to remember and to not write down and put
in the back of their bottom desk drawer. (They used to write down the passwords and
put them in their top desk drawer but after they took a corporate security course
they decided they had to get a bit more secure. You laugh? Not me. That's how
some RCMP members have done things.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Wayne Gillespie]

I worked at a naval base a few years ago (pre PC days) where the typists in the typing pool were required to remove
their typewriter ribbons each night and lock them in safe in case people like you started reading their ribbons!! :-)

On Wed, 14 Jul 2004 14:33:28 GMT, Chuck Grimsby <c.*******@worl dnet.att.net.in valid> wrote: While at a client site awhile back, I was "assigned" a desk to sit at
so I could do my work. While waiting for some query results to pop
up, I idlely started flipping through the rolodex the previous user of
this desk had left. Got to the "P" section and the first card was...
You guessed it... Passwords!

Wayne Gillespie
Gosford NSW Australia

[Tony Toews]

Wayne Gillespie <be*****@NObest fitsoftwareSPAM .com.au> wrote:

I worked at a naval base a few years ago (pre PC days) where the typists in the typing pool were required to remove
their typewriter ribbons each night and lock them in safe in case people like you started reading their ribbons!! :-)

Nowadays they'd be required to remove their PC hard drives and lock them in the safe.
Which is reasonable when you think about it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

Tony Toews <tt****@teluspl anet.net> wrote in
news:8m******** *************** *********@4ax.c om:

Wayne Gillespie <be*****@NObest fitsoftwareSPAM .com.au> wrote:

I worked at a naval base a few years ago (pre PC days) where the
typists in the typing pool were required to remove their
typewriter ribbons each night and lock them in safe in case people
like you started reading their ribbons!! :-)

Nowadays they'd be required to remove their PC hard drives and
lock them in the safe. Which is reasonable when you think about
it.

There was a recent story here in NYC where a private school
discarded a bunch of administrative PCs without wiping the hard
drives. They were sitting out on the curb, somebody checked them out
and was able to find SSNs and grade records for nearly 15K students,
covering decades.

Amazing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote:

There was a recent story here in NYC where a private school
discarded a bunch of administrative PCs without wiping the hard
drives. They were sitting out on the curb, somebody checked them out
and was able to find SSNs and grade records for nearly 15K students,
covering decades.

Amazing.

Ouch.

1) Americans use SSN way too much.

2) Someone should be charged and do jail time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm