July 10, 2012 at 3:03 am
Hello all,
need a small advice about an issue we have with an web application which connected to a sql 2005.
We have around 60 that we want to connect to a database and a specific table and have only read permissions. Also we want those users to see only the data that belongs to their usernames and that crendential has to do with a column that exists inisde the table
for example:
For the User: testuser
we want to login inside database and read only those records that the column "user" is equal to testuser and not have the ability to see the records from other user in the same table with different name at column "user".
So, is it possible to do something like that with a query or i have to use views per user or something else that has to do with a replica database for each user. Last option is not the best for us.
Regards,
John Kountouriotis
July 10, 2012 at 3:47 am
Possible to do in the query.
Take a look at the SUSER_SNAME(), SUSER_NAME(), USER_NAME() functions which you can pass in as the where clause and filter on the credentials column.
July 10, 2012 at 12:43 pm
How is the web application connecting to SQL Server? IF it is using a shared login (very common) then you need to get the user name from the web server and pass it to the query as a parameter.
The common approach, if using windows authentication or individual SQL logins, is to use a view that uses one of the functions in the WHERE clause.
I prefer to do all database access via stored procedures and then you can either have a user name parameter or usethe functions in the SP.
Be sure to have an index that has the user column as the leading column so it is used.
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply