Enforce business rules in microsoft sql server

susan1978
susan1978 used Ask the Experts™
on
When designing a datbase Is there any way to enforce a programer to query the data base in a certain way for example i made  a login table the table has a username, password and active field. On five different projects with different programmers when they create the logi screen they query the login table for username and password but they tend to forget the and active=1 is there any way for me to force them to say uername='' and password='' and active=1. I would like them to force them to query ifthe user is active. I cant force them to use a stored procedure is there an alternative way to set this type of rule.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
You can restrict access to the tables (raw data) to systems administrators and internal application projects that writing new or updated records etc.  Then only grant select privileges to views.  Consequently, my suggestion would be to have a view for users that only showed those that are active, so that when programmers query for a username and password they only are dealing with those that should be active in the first place.


As an aside, it is a good practice for the password in the database to be kept hashed or encrypted.  The programmers should be querying for the password of a given user, hashing / encrypting the password they are trying to match and then comparing that with the resulted password.  This will prevent in security wholes caused by depending strictly on existence of a record when using username and password in where criteria as well as eliminate someone being able to read your passwords in clear text at the database level or via the transport of password to the client application reading the information from the table.


HTH
--isa

Author

Commented:
I like your idea of a view andrights.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Was hoping that would help.  It works well in some systems I have as you can follow some standard naming convention for developed views like "vw_something" or have them in specific schema, but I have a lot of views that have non-prefixed names so they would not be differentiated from tables in a developers mind.  For example, if the true table is SECURITY_LOGINS_MASTER having a simple view name of USERS would be more user-friendly, give you the opportunity to do business rules upfront and would probably be preferred by the programmers anyway given the shorter name.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial