This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated (see final test case), another module cannot be signed without knowing the password for the Certificate, and if anyone changes any module that you sign (such as the Trigger), then the signature is automatically removed, alerting you to that change and then you can decide whether to resign it with the current changes or reject the changes ;-).
Also, trapping ApplicationName / ProgramName in a Trigger is not reliable as it is easy to pass in that value in a ConnectionString.
Please note that the Audit table is in a different Schema -- Auditing -- than the main table -- in dbo -- to prevent ownership-chaining, assuming that most Stored Procedures would also be in the dbo Schema.
The Setup
USE [...];
GO
CREATE CERTIFICATE [AuditCert]
ENCRYPTION BY PASSWORD = 'Password Goes Here.'
WITH SUBJECT = 'Restrict Insert Test';
GO
CREATE USER [AuditUser]
FROM CERTIFICATE [AuditCert];
-- no DEFAULT_SCHEMA for Certificate-based Users
GO
CREATE SCHEMA [Auditing]
AUTHORIZATION [AuditUser];
GO
-- DROP TABLE [Auditing].[AuditLog];
CREATE TABLE [Auditing].[AuditLog]
(
AuditLogID INT IDENTITY(1, 1) NOT NULL,
AuditDate DATETIME2 NOT NULL
CONSTRAINT [DF_AuditLog_AuditDate] DEFAULT (SYSDATETIME()),
ImportantStuffID INT,
Column2 VARCHAR(50),
CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED (AuditLogID ASC)
);
GO
CREATE TABLE [dbo].[ImportantStuff]
(
ImportantStuffID INT IDENTITY(1, 1) NOT NULL,
Column2 VARCHAR(50),
CONSTRAINT [PK_ImportantStuff] PRIMARY KEY CLUSTERED (ImportantStuffID ASC)
);
GO
CREATE TRIGGER [dbo].[AuditImportantStuff]
ON [dbo].[ImportantStuff]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
SELECT ins.[ImportantStuffID], ins.[Column2]
FROM inserted ins;
END;
GO
ADD SIGNATURE TO [dbo].[AuditImportantStuff]
BY CERTIFICATE [AuditCert]
WITH PASSWORD = 'Password Goes Here.';
GO
CREATE PROCEDURE [dbo].[AttemptDirectInsert]
(
@ImportantStuffID INT,
@Column2 VARCHAR(50)
)
AS
SET NOCOUNT ON;
INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
VALUES (@ImportantStuffID, @Column2);
GO
CREATE PROCEDURE [dbo].[ImportantStuff_AddData]
(
@ValueForColumn2 VARCHAR(50)
)
AS
SET NOCOUNT ON;
INSERT INTO [dbo].[ImportantStuff] ([Column2])
VALUES (@ValueForColumn2);
GO
CREATE USER [TestUser]
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = [dbo];
GO
GRANT EXECUTE ON [dbo].[AttemptDirectInsert] TO [TestUser];
GRANT EXECUTE ON [dbo].[ImportantStuff_AddData] TO [TestUser];
GO
The Test
SELECT SESSION_USER, ORIGINAL_LOGIN();
INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-1, 'test 1');
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER, ORIGINAL_LOGIN();
INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-2, 'test 2');
-- Msg 229, Level 14, State 5, Line 102
-- The INSERT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.
EXEC [dbo].[AttemptDirectInsert]
@ImportantStuffID = -3,
@Column2 = 'test 3';
-- Msg 229, Level 14, State 5, Procedure AttemptDirectInsert, Line 115
-- The INSERT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.
INSERT INTO [dbo].[ImportantStuff] ([Column2]) VALUES ('test 4');
-- Msg 229, Level 14, State 5, Line 114
-- The INSERT permission was denied on the object 'ImportantStuff', database '...',
-- schema 'dbo'.
EXEC [dbo].[ImportantStuff_AddData]
@ValueForColumn2 = 'test 5';
-- woo hoo!
SELECT * FROM [Auditing].[AuditLog];
-- Msg 229, Level 14, State 5, Line 122
-- The SELECT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.
REVERT;
SELECT SESSION_USER, ORIGINAL_LOGIN();
SELECT * FROM [Auditing].[AuditLog];
EXECUTE AS USER = 'AuditUser';
-- Msg 15517, Level 16, State 1, Line 143
-- Cannot execute as the database principal because the principal "AuditUser" does not
-- exist, this type of principal cannot be impersonated, or you do not have permission.
UPDATE
Additional notes:
- As @Paul mentioned in his answer, this method (as shown) does not prevent privileged Users from doing direct inserts. However, it is still possible to block DML actions not initiated from code signed with the Certificate via a Trigger on the Audit table **. But that mostly prevents accidental inserts since anyone in the
db_owner fixed database role should be able to disable the Trigger, and there is potentially at least 1 work around that could be used by someone in the db_datawriter fixed database role if they are fairly crafty.
- The method described and shown above doesn't need to use a Certificate. It would be possible to do the same setup using an Asymmetric Key. The nice thing about Certificates is that they are more portable since they can be backed up to a file or you can, starting in SQL Server 2012, extract the Certificate and its Private Key using the CERTENCODED and CERTPRIVATEKEY functions. This allows the exact same Certificate to be created in other databases and even other instances. This is quite helpful when there is cross-database functionality and you don't want to enable Cross-database Ownership Chaining and/or
TRUSTWORTHY.
- Since it is not apparently obvious from any of the test cases shown in either of the answers here, I will point out a fundamental difference between the two methods (which also happens to be the reason why I prefer Module Signing):
- Using
EXECUTE AS changes the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B INSTEAD OF mine.
- Using Module Signing will add permissions to the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B IN ADDITION TO mine.
** I do have mostly-complete example code (about 75% done) for a Trigger on the Audit table that would disallow updates from anything but code signed by the Certificate, but ran out of time to complete it. The concept is that a lock is taken on the Certificate during the process, and the lock entry includes the Certificate ID. You can verify that the Certificate ID is the desired Certificate and ROLLBACK if it isn't or no Certificate is used in the Transaction. The problem was that VIEW SERVER STATE is needed to use sys.dm_tran_locks. However, that is a fairly easy problem to solve as it can be granted via a Certificate-based Login, which can even be the same Certificate. In that case, the Certificate can be backed-up and restored into master for the purpose of creating the Login from it. Then just grant that Login the VIEW SERVER STATE permission, and finally sign the Trigger on the Audit table with that same Certificate (already in that DB as it was used to sign the Trigger on the base table).
