Working with SQL Server File Tables – part 2

Working with SQL Server File Tables – part 1

I will create the four FileTables:

  • USERS
  • Documents
  • Confidential
  • HR

use FILESERVER
GO
CREATE TABLE USERS AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE Documents AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE Confidential AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE HR AS FileTable;
GO

14

16-1

The corresponding folders are created in the MSSQLSERVER\FILESERVER share:16

Then we can create several personal user folders in the USERS folder:18

Here’s the corresponding new rows in the USERS file table:
21 21-1

As you can see each row corresponds  to a file (is_directory = 0) or a folder (is_directory = 1) in the file share.

What permissions should users have to be able to create  files/folders in the “SQL” file share? First of all, we must create a server login (at least with public role) and a database user login (public + other permissions or roles) for each user  – otherwise he/she won’t even be able to access the share – for example I tried to access \\sql1\mssqlserver share as User2 which has neither sql server login no db login:
21-3

For the purpose of this test I will create two sql server logins that will map to the corresponding database users – User1 and User2:
21-16

and make them members of the db_datareader role:21-12 21-13

Now User2 can browse the share:
21-6 21-7

..but not to create new file/folders (i.e.  insert new rows) or modify them (i.e. update the table):
21-8 21-9 21-10

Consider the following situation: User1 should be able to insert and update information in Confidential file table, User2 should have only read access. All insert/update operations on the Confidential file table (or creating/modifying file/folders from the file share’s perspective) must be audited.

Since User2 already has read access I will grant User1 the nesessary permissions:
30

Next I should create two new tables (the ordinary tables, not File Tables!) that will contain the auditing information, for example:

1) AuditCONFIDENTIALinsert table

use FILESERVER
CREATE TABLE AuditCONFIDENTIALinsert
(
ID int IDENTITY NOT NULL,
InsertTime    DateTime,
ServerLogin nvarchar(50),
DBuser nvarchar(50),
FileName nvarchar(50),
CONSTRAINT PKAuditConfidentialInsert
PRIMARY KEY (ID)
)
GO

191

2) AuditCONFIDENTIALupdate table

use FILESERVER
CREATE TABLE AuditCONFIDENTIALupdate
(
ID int IDENTITY NOT NULL,
UpdateTime    DateTime,
ServerLogin nvarchar(50),
DBuser nvarchar(50),
FileName nvarchar(50),
CONSTRAINT PKAuditConfidentialUpdate
PRIMARY KEY (ID)
)
GO

192

The tables are created:
193

To fulfill the auditing requirements I will add the two triggers to Confidential table:

1) Trigger for the insert operations

use FILESERVER
go
IF OBJECT_ID (‘CONFIDENTIALinsertTR’,’tr’) IS NOT NULL
DROP TRIGGER ‘CONFIDENTIALinsertTR’
go
CREATE TRIGGER ‘CONFIDENTIALinsertTR’
ON dbo.CONFIDENTIAL
AFTER INSERT
NOT FOR REPLICATION
AS
BEGIN
   INSERT INTO dbo.AuditCONFIDENTIALinsert (InsertTime, ServerLogin, DBuser, FileName)
VALUES ((SELECT Top 1 Creation_Time FROM dbo.Confidential), SUSER_SNAME(),
USER_NAME(), (SELECT Top 1 name FROM dbo.Confidential))
END
GO

194

 

2)  Trigger for the update operations

use FILESERVER
go
IF OBJECT_ID (‘CONFIDENTIALupdateTR’,’tr’) IS NOT NULL
DROP TRIGGER CONFIDENTIALupdateTR
go
CREATE TRIGGER CONFIDENTIALupdateTR
ON dbo.CONFIDENTIAL
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO dbo.AuditCONFIDENTIALupdate (UpdateTime, ServerLogin, DBuser, FileName)
VALUES ((SELECT Top 1 Creation_Time FROM dbo.Confidential), SUSER_SNAME(),
USER_NAME(), (SELECT Top 1 name FROM dbo.Confidential))
END
GO

195

These triggers add a new row containing the time of the insert/update operation, sql server login and database user login under which this operation has been performed and the file which has been inserted/updated.

Now it’s time to see how it works:

1) I will copy the file SecretReport.xlsx to the \\FILESERVER\Confidential share – in other words we’ll see how the auditing of the insert operation works:

205

This code will show the content of the Confidential file table:

use FILESERVER
go
select name, file_type, cached_file_size, is_directory, creation_time, last_write_time,
last_access_time from Confidential
order by creation_time desc
go

206

This one will show the content of the AuditConfidentialInsert  table:

use FILESERVER
go
SELECT *  FROM AuditCONFIDENTIALinsert
order by InsertTime desc
go

207-Audit
This table shows what has been created (“inserted”), when and by whom.

 

2)  This time we’ll see how the auditing of the update operation works – I will open SecretReport.xlsx, make some changes and save the file:
253-TestUpdateFile 254-TestUpdateFile

Here’s the content of the AuditConfidentialUpdate table:

use FILESERVER
go
SELECT * FROM AuditCONFIDENTIALupdate
order by UpdateTime desc
go

255

I’d like to draw your attention to the following facts:

1) working with the document (“updating”) produces more the one row in the audit table even if you click “Save” button only once,

2) the updating takes place for the temporary file ~$___, not for the original file name

3) for the reason unknown to me only the first update event is registered under the name of the current user – Testcompany\Administrator – all subsequent ones are registered under the sa account and the most interesting fact:

4) some time after I took the previous screenshot  (minutes or hours later) the same code produces another output:
260
…and this really can become an issue when auditing file modifications because 1) it was only User1 who was changing the SecretReport.xlsx file at 2016-01-12 05:14:34.880 and 2) if Administrator had really changed this document at the exactly same time as User1 why whould SQL Server have not  added the corresponding row to the table at 2016-01-12 05:14:34.880 and have done it only some time later?

I hope this article provided some insight into what the FileTables feature is, how it can be used and what issues you may encounter working with them.

 

%d bloggers like this: