Seeking more elegant form of expression

[groston]

Consider an application for which different classes of users are granted diffeent levels of access to the underlying data. (Not too hard to imagine!)

Now, consider the case where one class of users is permitted to query the database based on some value of some field but another class cannot query based on this field.

One way to formulate this (at the application level) is as follows:

if (userClass = SuperUser) then
strField = " WHERE myField = 'foo'"
else
strField = ""
end if

Then, the SQL query can look like this:

"Select * From myTable" + strField

Alternatively, the value for myField can be passed to a StoredProcedure as a variable (which will be NULL for non-SuperUser's) and an IF clause (which selects between two different SQL statements) can be written in the StoredProcedure based on the value of the variable.

I find both of these approachs to be inelegant and ugly. There is almost certainly a cleaner way to do this, but I have yet to figure it out. Can you please offer a suggestion?


----

Gerry Roston
gerry@pairofdocs.net

 

[nigelrivett]

You could create views to access the tables. Thee views include the usertype and login as in

select fld1, case when usertype is not null then fld2 else null end
from tbl
join userlogin
on userlogin.spid = @@spid
left outer join usertypes
on usertypes.fld2access = 1
and usertypes.usertype = userlogin.usertype

Then the user won't be able to see the ield in the view - can join to the main table if you want to be able to view the field but not query on it.

Will have problems with using indexes though.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[groston]

One answer, which may not be considered 'elegant' is to use dynamic SQL. This method seems to be working well for me.

Another possibility, if the value beign checked is numeric, is to use an AND or OR in the SQL statement. For example, if
SuperUser = 0x8
PowerUser = 0x4
JoeUser = 0x2
VirusUser = 0x1
One could do

SELECT * FROM foo WHERE (@myUserType | 0xe) = 1

To pull all users except VirusUsers.

Please accept my apologies for C syntax for the hex numbers...

----

Gerry Roston
gerry@pairofdocs.net