Using CONTEXT_INFO function–Another useful usage scenario

Πρόλογος

Αυτό που με εξιτάρει περισσότερο στην δουλειά μου και στην ενασχόληση μου με τον SQL Server είναι να βρίσκω λύση σε προβλήματα, αλλά και σε ποιον δεν αρέσει αυτό;

Το Πρόβλημα

Σήμερα ένας συνάδελφος στην εταιρία που εργάζομαι έρχεται με το εξής ερώτημα-πρόβλημα

«Έχω ένα πίνακα και έχω φτιάξει μια stored procedure η οποία περιέχει ένα business logic αρκετά σημαντικό και περίπλοκο με το οποίο κάνω insert στον συγκεκριμένο πίνακα. Θέλω να κάνω insert στο συγκεκριμένο πίνακα μόνο από την συγκεκριμένη stored procedure και μόνο πως μπορεί να γίνει αυτό;»

Στην αρχή ξαφνιάστηκα καθώς ο συνάδελφος είναι εμπειρότατος και η ερώτηση του μου φάνηκε κάπως. Του απάντησα ότι αυτό είναι στο χέρι του καθώς αυτός γράφει τον κώδικα. Φυσικά η απάντηση μου δεν τον ικανοποίησε. Έτσι μετά από μια σύντομη συζήτηση κατάλαβα τι ακριβώς ήθελε να κάνει το οποίο τελικά ήταν:

«Έχω ένα πίνακα και έχω φτιάξει μια stored procedure η οποία περιέχει ένα business logic αρκετά σημαντικό και περίπλοκο με το οποίο κάνω insert στον συγκεκριμένο πίνακα. Θέλω να κάνω insert στο συγκεκριμένο πίνακα μόνο από την συγκεκριμένη stored procedure και μόνο είτε από την εφαρμογή είτε από τον SSMS είτε από οπουδήποτε. Θέλω δηλαδή εκτός από να προστατέψω την ποιότητα των δεδομένων που θα μπαίνουν στον συγκεκριμένο πίνακα θέλω να προστατέψω και εμένα και τους άλλους developers καθώς στην καθημερινότητα μας φτιάχνουμε πολλές stored procedures και υπάρχει πάντα το ενδεχόμενο σε μια νέα που φτιάχνουμε να κάνουμε insert στον συγκεκριμένο πίνακα χωρίς να καλέσουμε την εν λόγο stored procedure με αποτέλεσμα να έχω λάθος στα δεδομένα στον πίνακα. Επίσης αυτό θέλω να μπορεί να γίνει ακόμα και για αυτούς που έχουν δικαιώματα sysadmin στον SQL Server. Πως μπορεί να γίνει αυτό;»

Η Λύση

Μετά από τα παραπάνω το πρόβλημα ήταν σαφές. Ομολογώ ότι κάτι τέτοιο δεν είχα αντιμετωπίσει στο παρελθόν και μου πήρε μερικά λεπτά να συνειδητοποιήσω τι ακριβώς ήθελε να κάνει. Αφού τελικά κατάλαβα ότι στην ουσία ήθελε μόνο μέσα από ένα συγκεκριμένο context να κάνει insert στον πίνακα του τα πράγματα έγιναν αρκετά εύκολα καθώς αμέσως πήγε το μυαλό μου στην CONTEXT_INFO function που έχει ο SQL Server και με την οποία μπορώ να περάσω πληροφορίες στο session που θα δημιουργηθεί ή υπάρχει στον SQL Server και τις οποίες μπορώ να διαβάζω μέσα από την DMV sys.dm_exec_sessions.

Στο κώδικα που ακολουθεί υπάρχει ένα παράδειγμα το οποίο εξομοιώνει την λύση, αλλά πριν από αυτό καλό θα είναι να σας εξηγήσω μερικά πράγματα ώστε να γίνει ευκολότερη η ανάγνωση του κώδικα που ακολουθεί.

Εφόσον όλη η δουλειά θέλουμε να γίνει μέσα από την συγκεκριμένη stored procedure τότε μέσα σε αυτή θα ορίζουμε κάτι μέσα στο session context με την χρήση της context_info το οποίο να δηλώνει ότι γίνεται χρήση της stored procedure. Στο πίνακα που γίνεται το insert θα πρέπει να φτιάξουμε ένα trigger for insert που θα ελέγχει αυτό το κάτι και αν το βρει τότε θα κάνει insert καθώς θα έρχεται από την χρήση της stored procedure ενώ σε άλλες περιπτώσεις θα κάνει rollback. Τόσα απλά είναι τα πράγματα. Το μόνο που θα πρέπει να φροντίσω είναι να κρατάω το context όπως ήταν πριν ορίσω το δικό μου χαρακτηριστικό (ή προσθέσω σε αυτό ) και με το τέλος της stored procedure αν το επαναφέρω το οποίο δεν είναι κάτι δύσκολο.

Η Υλοποίηση

Μετά από τις απαραίτητες εξηγήσεις ο κώδικας που υλοποίει τα παραπάνω είναι

USE tempdb;
GO

CREATE TABLE K (id INT IDENTITY, f1 INT);
GO

CREATE PROC spInsertOnK @p INT
AS
    -- variable to keep old context
    DECLARE @CurrentContextInfo varbinary(128) = CONTEXT_INFO();
    -- variable to build new context
    -- for simplicity send the sp name only
    DECLARE @NewContextInfo varbinary(128);
    SET @NewContextInfo = CAST('spInsertOnK' AS VARBINARY(20) );
    -- set the new context    
    SET CONTEXT_INFO @NewContextInfo;
    -- insert on table K
    INSERT INTO K(f1) VALUES (@p);
    -- restore old context
    SET CONTEXT_INFO @CurrentContextInfo;
    return 1;
GO

CREATE TRIGGER InsertOnKviaspInsertOnK ON K FOR INSERT
AS
    DECLARE @CurrentContextInfoString varchar(20);
    -- get the current info
    SELECT  
            @CurrentContextInfoString = 
            convert(varchar(20), substring(context_info, 1, 20))
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@spid;
    -- check if this insert comes from sp 
    IF @CurrentContextInfoString <> 'spInsertOnK'
        BEGIN
            ROLLBACK;
            RAISERROR ('Inserts on table K allowed only by using the spInsertOnK 
                        stored procedure.',17,1);
        END;
GO 

Η Εφαρμογή

Μετά από τα παραπάνω ας έρθουμε να δοκιμάσουμε την λύσης μας.

Ανοίγω δυο νέα query window και στο πρώτο εκτελώ την stored procedure

EXEC spInsertOnK 1;
GO
SELECT * FROM K;
GO

Παρατηρώ ότι όλα πάνε μια χαρά και παώ στο δεύτερο query window στο όποιο προσπαθώ να κάνω κατευθείαν insert στον πίνακα

INSERT INTO K(f1) VALUES (10);

Όπως θα δείτε θα πάρω ένα ωραιότατο μύνημα λάθους το οποίο θα με ενημερώνει και δεν θα με αφήνει να κάνω insert στο πίνακα

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Msg 50000, Level 17, State 1, Procedure InsertOnKviaspInsertOnK, Line 13
Inserts on table K allowed only by using the spInsertOnK stored procedure.

Επίλογος

Όπως είδαμε με απλές κινήσεις η απαίτηση του συναδέλφου λύθηκε. Πέρα όμως από αυτή υπάρχουν ακόμα πολλά ωραία πράγματα τα οποία μπορώ να κάνω όπως για παράδειγμα να περνάω μέσα σε αυτή από την εφαρμογή μου πληροφορίες όπως computer name, user name ειδικά σε εφαρμογές που χρησιμοποιούν ένα συγκεκριμένο sql user για να μπαίνουν όλοι χρήστες της στον SQL Server 😉

/*antonch*/