Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Usages of CONTEXT_INFO Expand / Collapse
Author
Message
Posted Thursday, December 21, 2006 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:34 AM
Points: 173, Visits: 105
We are using the technique with connection pooling (although a WinForms app) for user/application context information required for logging purposes (used in triggers).  By always setting the CONTEXT_INFO as part of our connection logic, we do not have to worry about "leftovers" from previous users.  But in any case, I believe the connection reset that takes place when reusing a connection in the pool clears the previous user's CONTEXT_INFO (but you should verify this yourself).


Regards,

Tore Bostrup
Post #332232
Posted Thursday, December 21, 2006 9:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:13 PM
Points: 311, Visits: 526

>>As long as the trigger is enabled you can make sure that the logic will work.

Maybe could also create a database-level trigger using the same technique, to make sure that the table wasn't altered or dropped outside of an approved way, to prevent this?

Great article, thank you...

>L<

Post #332236
Posted Thursday, December 21, 2006 10:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:13 PM
Points: 311, Visits: 526

I thought maybe we could do this with EVENTINFO() in a trigger, but the calling proc isn't included in the schema (would be nice!).

Looking at the docs, it seems maybe you could use context info more pervasively in a system and then do something like this:

SELECT context_info AS MyCtxInfo

FROM sys.dm_exec_requests

WHERE session_id = @@SPID

AND request_id = CURRENT_REQUEST_ID();

... ? but I am not sure, I don't understand how CURRENT_REQUEST_ID() works or whether you will ever see more than one result from the above code. 

Anyone??

>L<

Post #332244
Posted Thursday, December 21, 2006 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 5:35 AM
Points: 1, Visits: 18
Very Good Article, I was not aware of it...
Post #332251
Posted Thursday, December 21, 2006 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 27, 2006 2:51 PM
Points: 2, Visits: 1

Very useful article ! I don't know about this until now.

Thank you. David N Nguyen.

Post #332266
Posted Thursday, December 21, 2006 12:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553

This procedure wouldn't work for me in SQL 2005. Casting the Context_Info as varchar did not work. I had to cast the string as binary(128).

 

I had to rewrite the trigger as following:

IF (Select CONTEXT_INFO()) <> Cast('uspModifyEmployees' as binary(128))

Also, in SQL 2005, Context_Info can be Null which it is for an ad hoc query. A Null Context_Info would not be caught by the trigger since Null is neither equal nor not equal to anything. So, I further had to modify it to account for null:

IF (Select IsNull(CONTEXT_INFO(), Cast('' as binary(128)))) <> Cast('uspModifyEmployees' as binary(128))

Of course, this doesn't really stop me from doing ad hoc queries, as I can still do this:

Set Context_Info 0x7573704D6F64696679456D706C6F79656573

Delete From Employees

Where EmpID = 1

SET CONTEXT_INFO 0x0





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #332277
Posted Thursday, December 21, 2006 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:27 PM
Points: 126, Visits: 137
Interesting, but please all do not forget that this is what PERMISSIONS are for in SQL Server. I find it's best to use a thing according to its intended purpose first, and only tweak/hack it when absolutely necessary. This trick is not as secure, it's harder to understand, it would potentially make life miserable for someone working on the system who is unfamiliar with it, etc. etc.

If you want a table to be read only, then:

1. Set permissions for the user(s) or role(s) as Select for the table

2. Set permissions to grant execute on the stored proc.

This works perfectly.
Post #332290
Posted Thursday, December 21, 2006 1:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
I should also add that I can disable or drop the trigger as well.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #332304
Posted Thursday, December 21, 2006 6:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 9:12 PM
Points: 2, Visits: 67
I agree totally, in fact I go so far to say that this article is going to confuse a lot of people new to SQL Server. What the author is proposing really should be done through permissions. Simply no permissions should be granted to the table directly and execute access granted to the stored procedure itself. I'm not sure if the article was intended as an academic exercise but it seems fairly impractical approach to me.
Post #332397
Posted Thursday, December 21, 2006 11:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136

Informative article... i was not aware of the CONTEXT_INFO.

I feel that use of CONTEXT_INFO in the scripts will be creating dependencies with the developers i.e., it will be mandatory to use this in each script. Setting up the permissions will be the correct and secure option.

Post #332429
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse