Filtering Results By User

[JHarb52207]

Lets assume I have the following tables:

CUSTOMER:

ID CUSTOMER_NAME COMPANY
=====================================================================
1 Customer 1 1
2 Customer 2 1
3 Customer 3 1
4 Customer 4 2
5 Customer 5 2
6 Customer 6 2
7 Customer 7 1
8 Customer 8 2
9 Customer 9 2
10 Customer 10 2


USERS:

ID USER_NAME
=======================================
1 User 1
2 User 2
3 User 3
4 User 4


COMPANY:

ID COMPANY_NAME
==========================================
1 Company 1
2 Company 2


SECURITY:

USERID COMPANY_ID
========================================
1 1
1 2
2 1
3 2
4 1
4 2


Ok now I have an application that knows that if User 4 is pulling a list from the CUSTOMER table to show the customers for both companies. Likewise if User 2 preformed the same action they should only see customers from Company 1.

My question is how to I get this same functionality from say Query Analyzer? Can this be done with SQL or Windows users accounts, or can security be setup to pull from a table?

I would also like to know if there is a way to force this security through any ODBC that connected to this database.

 

[mrdenny]

Yes you can do something like this. I did something just like this with a view for a stock option system. The view would only pull data from the table when the data was for that user.

When running through Query Analyser you can pull the current name of the person logged in with the CURRENT_USER function. Use that and join your tables together filtering the CURRENT_USER against the USERS table and you should be ok.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JHarb52207]

Sounds like a possible. One question. If I am not writing the Query, say somehow an end user got access to write a query, what would stop them from saying SELECT * FROM CUSTOMER?

Also the other thing we are attempting to filter with this is Crystal Reports.

Our reports when ran through the application have the security and filtering passed to them, but if an end user created a new report outside our system, which they can do, we have lost our security layer.

 

[mrdenny]

Use SQL Permissions and deny the users access to select from the CUSTOMER tables. Then they have to use the view.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JHarb52207]

Ok im with you now, I will give this a try. Thanks.