September 11, 2013 at 3:14 pm
I've created the following trigger to track logons:
Use master
Go
Create database AuditDB
Go
USE [AuditDb]
GO
CREATE TABLE [dbo].[ServerLogonHistory](
[SystemUser] [varchar](512) NULL,
[DBUser] [varchar](512) NULL,
[APP_NAME] [varchar](512) NULL,
[SPID] [int] NULL,
[LogonTime] [datetime] NULL,
[HOST_NAME] [varchar](512) NULL
) ON [PRIMARY]
GO
Grant insert, update, select on ServerLogonHistory to public
Go
CREATE TRIGGER [Tr_ServerLogon]
ON ALL SERVER FOR LOGON
AS
BEGIN
if
( (ORIGINAL_LOGIN() <> 'sa')
and APP_NAME() not like 'SQLAgent%'
and APP_NAME() not like '%IntelliSense'
)
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()
END
GO
When I run this all is good, table is in place, etc. but I tested it by logging in as another user and I'm unable to logon. I then granted permission to the table explicitly to that user and am still unable to logon. When I check the user it is a member of public.
When I check the table under permissions-effective tab I get the following for public: "cannot execute as the database principal because the principal "public" does not exist, this type of principal cannot be impersonated, or you do not have permission"
For the other user it says: "the server principal "domain\username" is not able to access the database "AuditDb" under the current security context"
I'm missing something here - but am stumped as to what it is. Can anyone point out the obvious to me? 🙂
Oracle is my usual forte, and implementing a logon trigger is a little simpler process.
September 11, 2013 at 4:12 pm
The reason that it does not work is that the plain user does not have access to that other database. You could achieve this by enabling the guest user, but I am not keen on that.
I think it is better to use certificate signing. That is sign the trigger with a certificate, and then move that certificate to the other database and create a user from the certificate, and grant that user permissions on the table. You don't need to grant anything to public.
I will have to admit that I am not sure whether this can be achieved without a stored procedure in the audit database, but that is no major obstacle.
I have an article on my web site where discuss this technique in detail, although I don't have an example for this particular situation.
http://www.sommarskog.se/grantperm.html
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 11, 2013 at 4:39 pm
Erland Sommarskog (9/11/2013)
The reason that it does not work is that the plain user does not have access to that other database. You could achieve this by enabling the guest user, but I am not keen on that.I think it is better to use certificate signing. That is sign the trigger with a certificate, and then move that certificate to the other database and create a user from the certificate, and grant that user permissions on the table. You don't need to grant anything to public.
I will have to admit that I am not sure whether this can be achieved without a stored procedure in the audit database, but that is no major obstacle.
I have an article on my web site where discuss this technique in detail, although I don't have an example for this particular situation.
Tack,
The problem is, there are many users, some that run services on an application that logs onto this database, and I need all users to be able to connect to it without this trigger blocking any of them.
So, are you saying that if I create the certificate (something I know nothing about) on the AuditDb database and assign a user to it, then all users will be able to insert to the table via the trigger?
I am still assuming that the users are not able to insert rows into the table and that is why they are being blocked, even though on the properties of the table both public and the specific user I assigned have insert, select and update privileges on it.
September 11, 2013 at 4:47 pm
The way I usually recommend is to use impersonation on the trigger.
CREATE TRIGGER ...
WITH EXECUTE AS ....
And execute it as a login that has insert permissions into that audit table. No guest needed, no permissions to public.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2013 at 4:53 pm
GilaMonster (9/11/2013)
The way I usually recommend is to use impersonation on the trigger.CREATE TRIGGER ...
WITH EXECUTE AS ....
And execute it as a login that has insert permissions into that audit table. No guest needed, no permissions to public.
Thanks - so in theory if I put in with execute as caller it would work when insert permission is granted to public?
Or, as you suggest, WITH EXECUTE AS <login> where <login> is one that has insert permissions, probably the better solution..
I'm still getting my head wrapped around these server logins & database users as opposed to just users in Oracle.
September 11, 2013 at 5:19 pm
Not EXECUTE AS CALLER. EXECUTE AS and specify an explicit login that has insert permissions on the audit table, then you don't need permissions to public.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2013 at 1:29 am
Glenstr (9/11/2013)
So, are you saying that if I create the certificate (something I know nothing about) on the AuditDb database and assign a user to it, then all users will be able to insert to the table via the trigger?
I suggest that you read my article. The article also discusses EXECUTE AS, as suggested by Gail, and you will also learn why that solution will not work, at least not on first attempt.
And, yes, it is a 50-page article, but it is packed with examples.
[/quote]I am still assuming that the users are not able to insert rows into the table and that is why they are being blocked, even though on the properties of the table both public and the specific user I assigned have insert, select and update privileges on it.[/quote]
As I said, the problem is that they don't have access to the database.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 16, 2013 at 2:52 pm
GilaMonster (9/11/2013)
Not EXECUTE AS CALLER. EXECUTE AS and specify an explicit login that has insert permissions on the audit table, then you don't need permissions to public.
Where exactly would I put the "WITH EXECUTE AS 'domain\dbuser' clause in my example?
I'm trying to test it out but no matter where I put it I'm getting parsing errors.
This is giving me incorrect syntax near keyword EXECUTE
CREATE TRIGGER [Tr_ServerLogon]
ON ALL SERVER FOR LOGON
WITH EXECUTE as 'domain\dbuser'
AS
BEGIN
if
( (ORIGINAL_LOGIN() <> 'sa')
and APP_NAME() not like 'SQLAgent%'
and APP_NAME() not like '%IntelliSense'
)
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()
END
September 16, 2013 at 3:05 pm
Glenstr (9/16/2013)
Where exactly would I put the "WITH EXECUTE AS 'domain\dbuser' clause in my example?
That is exactly what you use the syntax graphs in Books Online for.
But as I said, I don't think it will work fpr the reasons I explained in my post.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 16, 2013 at 3:16 pm
Erland Sommarskog (9/16/2013)
Glenstr (9/16/2013)
Where exactly would I put the "WITH EXECUTE AS 'domain\dbuser' clause in my example?That is exactly what you use the syntax graphs in Books Online for.
But as I said, I don't think it will work fpr the reasons I explained in my post.
thanks - I got the syntax correct(ed), I'm testing this on a test DB right now and just want to see the behavior.
I'm in the process of reading your article right now as well.. very informative.
ps. I can see why "with execute as" may not be the correct solution here, if my trigger is tracking all database logons for auditing purposes, then using "with execute as" is going to insert the same user in the ServerLogonHistory table for every single logon, correct?
September 16, 2013 at 4:05 pm
Glenstr (9/16/2013)
ps. I can see why "with execute as" may not be the correct solution here, if my trigger is tracking all database logons for auditing purposes, then using "with execute as" is going to insert the same user in the ServerLogonHistory table for every single logon, correct?
Correct, SYSTEM_USER returns the name of the impersonated login. But that could be addressed by using origrinal_login() instead.
However, I believe you will get an error saying that the database cannot be accessed, because you are sandboxed.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 17, 2013 at 3:21 am
Erland Sommarskog (9/16/2013)
However, I believe you will get an error saying that the database cannot be accessed, because you are sandboxed.
You don't. Every time I've implemented login triggers I've used EXECUTE AS with a login that has access to whatever resources the login trigger needs (and nothing else), along with Original_Login to pick up the login name that was used by the login attempt.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2013 at 3:01 pm
GilaMonster (9/17/2013)
You don't. Every time I've implemented login triggers I've used EXECUTE AS with a login that has access to whatever resources the login trigger needs (and nothing else), along with Original_Login to pick up the login name that was used by the login attempt.
Interesting, normally you cannot access objects outside the current database when you have an EXECUTE AS clause, unless the database is trustworthy and the database owner has the appropriate AUTHENTICATE permission. And master is not trustworthy by default - at least not listed as such. That's why I did not expect it to work, but I tested and indeed does.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply