Allow autosender whitelist only for special users RSS

1

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
()
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



by nerdbert 3 years ago
2

@nerdbert: Nice - thanks for sharing.

by mike.galbicka 3 years ago
(in reply to this post)

New comment

Fill in the form below to add a new comment. All fields are required. If you are a registered user on our site, please sign in first.

Nickname:
Email address (will not be published):
Your comment:

ORF Technical Support

Configuring, installing and troubleshooting ORF.

News & Announcements

Your dose of ORF-related news and announcements.

Everything but ORF

Discuss Exchange and system administration with fellow admins.

Feature Test Program

Feature Test Program discussion. Membership is required to visit this forum.

ORF Beta

Join the great bug hunt of the latest test release.

Customer Service

Stay Informed