Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check before stored procedure is ran

A function or trigger something along these lines

DECLARE @strtoken varchar(30)

IF NOT EXISTS (SELECT userid from users where token = @strtoken)
BEGIN
--stop any further transactions
END

Sorry, yes I meant to trigger the event of checking the token against the user table before any further processing of the stored procedure. The reason I'm doing this is because I'm using cookies and cookies can be changed. So I encrypted the token with MD5, 3DES and salted it. Right now I'm wrapping the stored procedure like this:

DECLARE @strtoken varchar(30)
DECLARE @userid bigint

IF EXISTS (SELECT userid from users where token = @strtoken AND userid=@userid)
BEGIN
--run stored procedure here WHERE userid = @userid
END

Of course, the user has to log in to use the web app, but I don't want anyone fiddling with the token. So I figured there was a way to check against the db with the token and if not verified, stop any further processing of the stored procedure without wrapping the entire stored procedure like I'm doing now

share|improve this question

migrated from stackoverflow.com Jun 18 '12 at 13:21

This question came from our site for professional and enthusiast programmers.

    
Why is the permissions system inadequate for the task? –  Damien_The_Unbeliever Jun 18 '12 at 12:26
    
In a trigger (AFTER INSERT ?) you could just simply roll back the running transaction to undo any inserts, if your criteria is not satisfied –  marc_s Jun 18 '12 at 12:38
    
I'm with @Damien_The_Unbeliever, this should be handled with permissions. –  Thomas Stringer Jun 18 '12 at 12:38
    
just verify (whatever you mean by that) user in trigger before procedure call. Where is problem exactly? –  Jānis Jun 18 '12 at 12:59
    
@Jānis And how would he fire a trigger prior to stored proc execution? –  Thomas Stringer Jun 18 '12 at 13:21

2 Answers 2

up vote 3 down vote accepted

No you cannot use Triggers for this because Triggers do not fire in response to Read-type events (Execute or Select). Nor should you because, in general, triggers are not an appropriate device for implementing security.

If you want to implement your security token idea on SQL Server(*) there are several general ways to go about it:

  1. Alter your Stored Procedures: Add an additional parameter to your stored procedures, then have your stored procedures themselves check this. This is the recommended method.

  2. Wrap your Stored Procedures: If you do not want to alter all of your stored procedures, you could instead do as one of the commenters suggests and write wrappers for all of your stored procedures. The wrapper has the token as an additional parameter, which it first checks and then calls the actual procedure. You use SQL-object security to prevent the actual procedures from being executed by anything other than the wrapper procedures. This approach is not usually recommended because of the extra work and its higher maintenance. However, this can be alleviated through automation if your database's security, schema and procedure design is structured correctly for it (this is how I do it, though not for this purpose).

  3. A variant of #2 (above) would be to have a single Wrapper Procedure that calls all of the other procedures. This would require that the first two parameters were the token and the actual procedure name and the rest would be the actual parameters shifted over two places. The Universal Wrapper would then check the token and if valid, then call the actual procedure. This is actually surprisingly easy to do (and does not require dynamic SQL either), however, it does also require properly designed security schemas to enable closing other security holes. This approach is not usually recommended because of it's even higher dependence on properly designed security and schema structures and because auditors do not generally understand it and it makes them nervous.

(* - and this may well be something that is better addressed at the web/app server level, since that seems to be where the weakness is.)

share|improve this answer
    
I went with suggestion #2. It wasnt that much different from what I already had. As for the web app I already addressed security and sql injection. I check security client side, server side and on the database. This question was for added protection. Nothing like redundant security and data type verification at every level of the web app –  Patriotec Jun 21 '12 at 18:46

If you don't want to use SQL Servers built-in security and want to do it in a trigger then you need to use something like this:

ALTER TRIGGER <your_trigger_name>
FOR INSERT
AS
IF NOT EXISTS (SELECT userid from users where token = inserted.strtoken)
BEGIN
    -- Stop any further transactions
    RAISERROR ('Place your error message here', 16, 1)
    ROLLBACK TRAN
    RETURN  
END
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.