SQL Server 2012: Passing through a user context on a connection.

Question

SQL Server 2012: Passing through a user context on a connection.

Skynet5

Old Hand

Points: 347
More actions
March 3, 2014 at 5:01 am

#290539

Hi,
I would like to know if we can have a generic "customer database account" to connect to the database (from a PHP layer) and then pass through the current web logged in user name for auditing purposes.
In the oracle world I would be using the setClientIdentifier function on any connection returned from a pool, but I cannot see anything obvious (to me) on the APIs to support this.
Our backup plan, because we only access table data through stored procedures, is to extend the API to have the username passed through - but this is a little ugly and less than transparent.
Any advice appreciated.
Sky

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I don't know Oracle so I might be wrong here, but if you are using windows authentication so each user is using his real login, you can use the functions suser_sname() to get the login's name or user_name() to get the user's name.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Skynet5 Old Hand Points: 347 More actions · Answer 2

Hi,

Our user base is public facing and very large, so we cannot/do not want to create Window accounts for each and every user. They will be stored in an LDAP server hosting on a linux environment along with the website etc.

Longer term we will be using connection pooling (to reduce connection time and cost) and of course they will all be connected as a connection pool user of some description.

Therefore, we need some way of passing the username across with each and every invocation (to enable us to audit who is doing what).

Hope this clarifies the help that I need! I really want something more transparent that adding run-as/username/caller as a parameter to every function.

Sky.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

You can try and use the set context_info statement (but I have to admit that I'm not sure that this is what you are looking for). This statement gets a value of type varbinary(128) and stores it as part of the session's data. You can get the information for this session by running queries on sys.dm_exec_session or using the function context_info(). Bellow a small script that shows how to use it:

--Inserting the data

declare @vb varbinary(128)

set @vb = cast('This is a small demo' as varbinary(128))

set context_info @vb

go

--Getting the data

select cast(context_info as varchar(128)) from sys.dm_exec_sessions where session_id = @@spid

select cast(context_info()as varchar(128))

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 4

there's a session-level value named CONTEXT_INFO you could set, but you would have to modify your application to set it and use it;

here's an example or two:

DECLARE @var VARBINARY(128)

SET @var = CAST(N'Hello World' AS VARBINARY(128))

SET CONTEXT_INFO @var

SELECT CONVERT(NVARCHAR(64),CONTEXT_INFO())

GO

-- Select the context information

DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

SET @sess = (SELECT context_info FROM master.dbo.sysprocesses

WHERE spid = @@spid)

SET @var = CAST(@sess AS NVARCHAR(64))

print @var

SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses

WHERE spid = @@spid

DECLARE @var VARBINARY(128),

@res NVARCHAR(64)

SET @var = CAST(N'Hello World' AS VARBINARY(128))

PRINT @var

--results: 0x480065006C006C006F00200057006F0072006C006400

SET @res = CAST(@var AS NVARCHAR(64))

PRINT @res

--results: Hello World

--The same but using CONVERT:

SET @var = CONVERT(VARBINARY(128), (N'Bananas and Oranges'))

PRINT @var

--results: 0x420061006E0061006E0061007300200061006E00640020004F00720061006E00670065007300

SET @res = CONVERT(NVARCHAR(64),@var)

PRINT @res

--results: Bananas and Oranges

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ftpex1 SSC Enthusiast Points: 148 More actions · Answer 5

I think you would have to do this from the code side. The user hits the php pages which authenticates them through ldap. You could change your connection string at that point to pass the user name and creds from a session var or cookie. Assuming you are not just getting a hash code returned. If you use the generic user SQL should only see the userName and password from the PHP script unless you pull a http header and include it in your update script for sql.

Skynet5 Old Hand Points: 347 More actions · Answer 6

Thanks guys..the Context_Info led me to some more googling and I found this which I very close to what I was hoping for..

http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit/