Select statement security

From: Riccardo Piccini (sviluppo_at_edp.it)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 17:15:08 +0200

Hi everybody!
I've a table "SALARY" like this:

NAME SALARY DEPT

John 3000,00 01
Mike 3100,00 01
Angela 2000,00 02
... ... ...
My DB users are USR1 and USR2

I would like that the statement "SELECT * FROM SALARY" return:
1) ALL the rows if the user running the above query is USR1
2) ONLY the rows for DEPT='02' if the user running the above query is USR2

Obviously, this is a security issue!!! (I mean, I know the use of the
"WHERE" clause, this is not my question!)

Is there a way (t-sql, stored procedure, trigger, or anything else) to do
this?!?! I would like that this security level was granted even if the query
is run directly from Query Analyzer by USR2, for example.

Thanks in advance! 

-- 
Riccardo Piccini
Supporto Software
EDP SERVICE SRL

Relevant Pages

  • Re: Select statement security
    ... My DB users are USR1 and USR2 ... ALL the rows if the user running the above query is USR1 ...
    (microsoft.public.sqlserver.security)
  • Re: Enter MULTIPLE Items in a Query (e.g. [Name:] ??
    ... >The user running the query will be prompted for first names. ... Here's the SQL view of that query. ... The field that is being querried on is "route" ... the chance of entry errors will hopefully be ...
    (microsoft.public.access.queries)
  • ENTER MULTIPLE Items in a Query Pop-Up PROMPT e.g. [Name:]
    ... >The user running the query will be prompted for first names. ... Here's the SQL view of that query. ... The field that is being querried on is "route" ... Seperating each by a comma will not be a problem. ...
    (microsoft.public.access.queries)
  • Re: Query Security
    ... >>dynamically creating, then deleting the query. ... and then closed the temporary workspace? ... was owned by a user other-than the user running the code in question. ... that closing the temporary workspace would change the query ownership. ...
    (microsoft.public.access.security)