Is there a way in SQL Server to make a table only able to insert by trigger?

Question

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't want the data at risk of being compromised in any way. The only thing that should ever insert on the table is the trigger I created on the active table to log its changes. In the rare case we may need to manually edit the log table I will turn off (if it exists) the "insert lock"

I am using SQL Server 2012 Enterprise with SQL Management Studio

Answer 1

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:

1. 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.
2. 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.
3. 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).

Answer 2

If you're happy using EXECUTE AS (trusting users that can impersonate), an alternative is:

Tables

CREATE TABLE dbo.Test
(
    TestID integer IDENTITY PRIMARY KEY,
    SomeDate datetime NOT NULL
);
GO
CREATE TABLE dbo.TestArchive
(
    TestID integer PRIMARY KEY,
    SomeDate datetime NOT NULL
);

Users

-- Ordinary user with the ability to insert to the Test table
CREATE USER NormalUser WITHOUT LOGIN;
GRANT INSERT ON dbo.Test TO NormalUser;
GRANT SHOWPLAN TO NormalUser; -- Not required, for testing only
GO
-- User used by the trigger to move rows to the Archive table
CREATE USER ArchiveUser WITHOUT LOGIN;
GRANT SHOWPLAN TO ArchiveUser; -- Required if normal users have this permission
GO
-- Give ownership of the Archive table to the Archive user
-- to prevent ownership chaining skipping permission checks
ALTER AUTHORIZATION 
ON OBJECT::dbo.TestArchive
TO ArchiveUser;

Trigger

This uses EXECUTE AS to perform the archive as the ArchiveUser

CREATE TRIGGER dbo_Test_AI
ON dbo.Test
WITH EXECUTE AS 'ArchiveUser'
AFTER INSERT
AS
BEGIN
    -- Insert deleted rows
    INSERT dbo.TestArchive
    (
        TestID, 
        SomeDate
    )
    SELECT
        D.TestID, 
        D.SomeDate
    FROM 
    (
        -- Remove rows ready to be archived
        DELETE dbo.Test
        OUTPUT Deleted.TestID, Deleted.SomeDate
        WHERE SomeDate <= DATEADD(DAY, -7, GETUTCDATE())
    ) AS D;
END;

Test

EXECUTE AS USER = 'NormalUser';
GO
-- Able to insert Test rows
INSERT dbo.Test (SomeDate)
VALUES 
    (DATEADD(DAY, -6, GETUTCDATE())),
    (DATEADD(DAY, -5, GETUTCDATE())),
    (DATEADD(DAY, -4, GETUTCDATE())),
    (DATEADD(DAY, -3, GETUTCDATE())),
    (DATEADD(DAY, -2, GETUTCDATE())),
    (DATEADD(DAY, -1, GETUTCDATE()));
GO
-- Able to insert a Test row that gets archived
INSERT dbo.Test (SomeDate)
VALUES 
    (DATEADD(DAY, -7, GETUTCDATE()));
GO
-- Not able to insert to the archive directly
INSERT dbo.TestArchive (TestID, SomeDate)
VALUES (100, GETUTCDATE());
GO
REVERT;

Tidy up

DROP TABLE
    dbo.Test,
    dbo.TestArchive;

DROP USER ArchiveInsert;
DROP USER NormalUser;

Note this arrangement does not prevent very privileged users from writing directly to the archive table, for example, members of the db_datawriter role or database owners. Neither does the certificate-based answer. The right solution for you depends on exactly how your permissions are currently set, how much you trust the various users, and your level of paranoia.

Answer 3

Is this something you are looking for,

I am using CONTEXT_INFO for this case.

--Create a current transaction table
CREATE TABLE activetable (
    id INT
    ,NAME VARCHAR(50)
    )
--Log table
CREATE TABLE archivetable (
    trid INT identity(1, 1) PRIMARY KEY NOT NULL
    ,logdate DATETIME
    ,del_id INT
    ,del_name VARCHAR
    ,ins_id INT
    ,ins_name VARCHAR
    )


--Trigger to catch the changes
CREATE TRIGGER logchanges ON activetable
FOR INSERT
    ,UPDATE
    ,DELETE
AS
BEGIN
    SET context_info 0x617263686976657461626C65

    INSERT INTO archivetable (
        logdate
        ,del_id
        ,del_name
        ,ins_id
        ,ins_name
        )
    SELECT getdate()
        ,d.id
        ,d.NAME
        ,i.id
        ,i.NAME
    FROM deleted d
    FULL OUTER JOIN inserted i ON d.id = i.id
END

--Trigger to block the DML to archive table but only able to insert if context_info is used
CREATE TRIGGER stopaction ON archivetable
AFTER INSERT
    ,UPDATE
    ,DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @contextinfo VARBINARY(128)

    SET @contextinfo = CONTEXT_INFO()

    IF @contextinfo = 0x617263686976657461626C65
    BEGIN
        PRINT 'hi'
    END
    ELSE
    BEGIN
        PRINT 'Transaction rollbacked , Cant INSERT/UPDATE/DELETE'
        ROLLBACK TRANSACTION
    END
END

--Testing

    INSERT INTO activetable
    SELECT 1,'sam'
--Success

    INSERT INTO archivetable
    SELECT getdate(),1,'sam',2,'nick'

--fails with The transaction ended in the trigger

Until unless we use the context_info in your session.