October 3, 2012 at 2:03 am
Hi Folks,
In local development environment, the DML is logged with CDC etc as per requirement. But the database tables don't have Audit Columns so far and there is a basic requirement to track back SPID to the IPs of developer machines, later on, when the connections are no more active!
Do Database login trigger can serve the purpose? or any other best alternative to make this done!
Thank you!
October 3, 2012 at 6:24 am
Login triggers only fire when the developer first logs in. Therefore, if the developer is logging in and staying connected, this doesn't work for what you're trying to do.
Database-level DML triggers can be used to tie back to the catalog views and get the IP address. This is certainly an option. However, keep in mind that most of the time, simply logging the username is sufficient for most auditing. If logins aren't being shared, is there a need to log back to the actual workstation?
K. Brian Kelley
@kbriankelley
October 4, 2012 at 12:55 am
K. Brian Kelley (10/3/2012)
Login triggers only fire when the developer first logs in. Therefore, if the developer is logging in and staying connected, this doesn't work for what you're trying to do.Database-level DML triggers can be used to tie back to the catalog views and get the IP address. This is certainly an option. However, keep in mind that most of the time, simply logging the username is sufficient for most auditing. If logins aren't being shared, is there a need to log back to the actual workstation?
Many thanks, really helpful!
Actually so far the general columns of database auditing for each table are not present in each table (user_id, date etc) So i think there is the need to really capture the login IP and the user for each session SPID.
Hopefully i may have able to respond to your question.
October 5, 2012 at 10:14 am
Abrar Ahmad_ (10/4/2012)
So i think there is the need to really capture the login IP and the user for each session SPID.
This will give you the caller's IP address in your trigger:
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
Selecting from the sys.dm_exec_connections DMV requires VIEW SERVER STATE permission which public does not have and I would advise against granting that permission to most logins.
A couple ways around this are to sign a module (function or stored procedure) that can retrieve the client's IP address for them (the certificate elevates the caller's permissions but only within that module) and use that module within all your triggers. Similarly you could use EXECUTE AS in a module instead of using a certificate.
Something like this maybe:
CREATE FUNCTION dbo.get_my_net_address ()
RETURNS VARCHAR(48)
AS
BEGIN
RETURN (SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID);
END
GO
Usage:
DECLARE @net_address VARCHAR(48);
SET @net_address = dbo.get_my_net_address();
SELECT @net_address;
Module Signing (Database Engine)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2012 at 11:31 am
I think another way to get the IP address, without granting view server state is some of the newer built in functions that were added in 2008 and above.
SELECT ConnectionProperty('client_net_address')
the above will return the IP address from the current connection, but it could be null if the conection is via named pipes, i believe.
here's most, if not all, of the available connection properties, i have saved.
SELECT
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
October 5, 2012 at 11:40 am
*adds to toolkit*
Thanks Lowell!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2012 at 12:27 pm
opc.three (10/5/2012)
*adds to toolkit*Thanks Lowell!
lol a good portion of my toolkit comes from your posts too! glad we shared a bit!
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply