TSQL Trigger to return alternate data??? 1

[KirbyWallace]

Is there a way to, at the database level, create an INSTEAD OF trigger (or anything else) that will prevent a query from returning the actual content of a row, but instead, return alternate data?

Specifically, I want to grant developer access to query the "login table, but I want to return "*****" (literally) for the password instead of actually returning the password.

A solution that will simply omit the password column from a "SELECT * FROM..." results would also suffice.

Anyone done something like this before?

Thanks!

 

[bborissov]

Instead of doing something like this why not just store passwords encrypted?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Create a view that mimics the login table. Then grant your developers permissions to the view (and not the table).

Create View dbo.DeveloperLogin
As
Select Col1, Col2, '*****' As Password
From   Login

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

bborissov: That's what I thinking of too. I've already done this in other databases that have the same table.

Problem is, I'm not the sole developer. There are others working in here too. If I encrypt/decrypt at the database (easily doable), then I defeat the purpose because while the password is enrypted in the database, it's not as soon as I take it out... the unencrypted password goes back to the developers.

If I serve encrpyted passwords, the developers will have to be able to decrypt them for the purposes of making the site work and allowing users to log in.

The encryption would only protect the paswords from people poking around in the database with SSMS.

GEORGE: (Can always count on hearing from you! ;-) The cat's outta the bag on this one. The developers know the table exists, and it's perfectly reasonable for them to access it. So I'd be gaining nothing by creating a decoy view. ;-) Creating a view is certainly the easiest route, but the devs know (I'm one of them also) the table name and could still query it unless I get all draconian and start restricting access. Then the egos get all bent out of whack, and trouble starts.


However, if they issued what they thought was a straight-through "SELECT *", and the result set came back without any password column, then I have plausible deniability: Hey, we moved that into a separate table...

har har...

Thanks!

 

[gmmastros]

I would encourage you to get all draconian and start restricting access. If they complain, tell em' I told you to do it!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I'll point out that protecting your users passwords is more important than protecting your devs egos. Passwords should never be stored unencrypted and decrypting them by any thing except the application should be cause for firing. Since most people will use the same password on multiple systems, letting them see the password could help in stealing their identity. Unencrytped passwords are a serious legal liability for your company.

"NOTHING is more important in a database than integrity." ESquared