Allow autosender whitelist only for special users - ORF Forums

Hi
auto-sender whitelist is a nice feature, but I do not like it for all my users.

I would like to share a small snippet of SQL code which you might want to use in order to only allow special users to auto-whitelist recipients after submitting emails.

I am using an Microsoft SQL database according to the official documentation. I have version 12 Express Edition (2014) and also had it running on 2012.

I extend the database by creating a helper table where I store the users' email addresses that are allowed for autowhite-listing. The database name here is ORF-FUSION. The table name for the user administration is "users_for_whitelist" (adapt if necessary)

USE [ORF-FUSION]
GO

/****** Object: Table [dbo].[users_for_whitelist] Script Date: 18.02.2016 16:26:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[users_for_whitelist](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](255) NOT NULL,
CONSTRAINT [PK_users_for_whitelist] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_users_for_whitelist] UNIQUE NONCLUSTERED
(
[user_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


You can insert the email addresses here by directly editing in SQL Management Studio or submitting with insert commands. E.g.
USE [ORF-FUSION]
GO
INSERT INTO [dbo].[users_for_whitelist]
([user_name])
VALUES
(<user_name, varchar(255),>)
GO



Now comes a trigger that only runs for users that are maintained in the "users_for_whitelist" table. Otherwise no submission to the ASWL table (auto sender white-list) can happen.


USE [ORF-FUSION]
GO
/****** Object: Trigger [dbo].[TriggerName] Script Date: 18.02.2016 16:21:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TriggerName] ON [ORF-FUSION].[dbo].[ASWL]
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT w.[user_name]
FROM [ORF-FUSION].[dbo].[users_for_whitelist] AS w
WHERE w.[user_name]=(SELECT [ASWL_SENDER_EMAIL] FROM inserted))
BEGIN
INSERT INTO [ORF-FUSION].[dbo].[ASWL] ([ASWL_SENDER_EMAIL],[ASWL_SENDER_DOMAIN],[ASWL_RECIPIENT_EMAIL],[ASWL_LASTHIT])
SELECT [ASWL_SENDER_EMAIL],[ASWL_SENDER_DOMAIN],[ASWL_RECIPIENT_EMAIL],[ASWL_LASTHIT]
FROM inserted
END
END;


That's all.
brgds, Norbert

@nerdbert: Nice - thanks for sharing.