Sql Server modify select

Question

I'm pretty sure there is no way, but i'm putting this out there for those expert beyond my knowledge.

What i am looking to do is to somehow alter SELECT statements before they are executed, at the database level. For a seriously pared-down example, i'd like to do something like the following... when someone executes the following SQL

SELECT * FROM users.MESSAGES

i'd like to catch it, before it executes, and alter the statement to something like

SELECT * FROM users.MESSAGES WHERE RECIPIENT = ORIGINAL_LOGIN()

allowing me to enforce user limitations on the data in a fashion similar to ORACLE's VPDs, without needing to resort to creating views on top of all my tables that might need this.

Thanks in advance.

I'm using Sql Server 2005.

Answer 1

Sadly, this is not possible.

Even the Microsoft SQL Server row-level security features (e.g. in the security catalogs) are implemented using views.

So, if you really need the feature, you're going to have to set up views with SUSER_NAME() or some similar individual- or role-identifier in the WHERE clauses.

Sorry!

Answer 2

Look into using a VIEW.

Answer 3

Use views (or inline table-valued functions), generate the views automatically and remove rights from the tables.

Answer 4

You could use dynamic SQL to construct your SELECT statement.

i.e.

create proc MySproc

@User   varchar(100)

AS    

--holy  dynamic sql injection attack, Batman!
sp_executesql 'SELECT * FROM users.MESSAGES WHERE RECIPIENT = ' + @User

--this would be much better
SELECT * FROM users.MESSAGES WHERE RECIPIENT = @User

This sounds too simple though.

Answer 5

There used to be a round-about unethical way in SQL 2000. You could create a trigger on master..sysprocesses table for INSERT and do this kind of manipulation. Thankfully, this is not possible, at least AFAIK, in SQL 2005, as master..sysprocesses is a fake table.

For the benefit of some of US still using SQL 2000, here is how to do this in SQL 2000:

1. In the console right click on servername
2. In the properties go to server settings tab
3. Then check allow modifications to be made directly to the system catalogs checkbox.
4. Selected the sysprocesses table in sysobjects and change it's xtype from S(system) to U(user)
5. Now go to Master DB, tables - right click on sysprocesses-All tasks-Manage Triggers
6. Then you can write your trigger
7. After you are done, turn back everything to it's original state.

Even with all of this, I still doubt if you can change the Select statement.

Raj

Disclaimer: Try this at your own risk. Be warned that you are making changes to system objects which could lead to undesirable results.

Answer 6

First, grant no direct table access to the users so they can't see the data from an ad hoc query. Make them use a stored proc to access the table and one of the parameters of the proc is the user login. Then write the code so that it only selects records for that login.