sql server on LINUX audit

Question

Hello
I'm new to sqlserver - we have a sql server on linux and we need audit enable,
i followed the steps as per https://solutioncenter.apexsql.com/auditing-select-statements-on-sql-server/
and i have created server rules per database, and added the users that I need audited to the proper server rules and enabled audit,
however, when the audited ID goes in and executes, insert, update , select delete which are the commands we wanted tracked i dont see it
also how can i extract the data been audited ?
TIA

Answer 1

Hi @helena Matos ,

Change fisops to dbo in your principle Name coloumn.

Or please check the audit log, there are detail messages:

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @helena Matos ,

There are many workarounds to achieve that, trigger , trace and other tools:
Please reference this doc. to have a test: various-techniques-to-audit-sql-server-databases

And code from my own side for trigger tech. as next:

--creat test db,table and then insert, update,select,delete   
--1.create db and table  
use master   
go  
  
create database test1027  
go  
  
use test1027  
go  
  
create table TSubject   
(   
subJectID nvarchar(4),   
subJectName nvarchar(30),   
BookName nvarchar(30),   
Publisher nvarchar(20)   
)   
go   
  
  
select * from TSubject  
  
--2.Create a record audit table, add actions,PerformedBy,TookPlace  
create table audit_TSubject  
(   
subJectID nvarchar(4),   
subJectName nvarchar(30),   
BookName nvarchar(30),   
Publisher nvarchar(20),  
Actions nvarchar(100),  
PerformedBy nvarchar(20) default user,  
TookPlace datetime default getdate()  
)on [primary]  
  
go   
  
select * from audit_TSubject  
  
--Create an insert tracking trigger on TSubject to record insert information  
create trigger insert_TSubject on  TSubject for insert  
as  
insert into audit_TSubject (subJectID,subJectName,BookName,Publisher,Actions) select *, 'insert' from inserted  
  
--verify the insert tracking  
--insert values into TSubject  
insert TSubject values (1,'sub1','book1','Pub1')  
  
--query the audit table to check   
select * from audit_TSubject  
  
--create update tracking trigger on TSubject to record update information  
create trigger update_TSubject on TSubject for update  
as  
insert into dbo.audit_TSubject (subJectID,subJectName,BookName,Publisher,Actions) select *,'update' from deleted  
  
select * from TSubject  
  
update TSubject set Publisher = 'Microsoft',BookName = 'Azure Cloud' where subJectID = '1'  
  
select * from audit_TSubject  
  
--create delete tracking trigger on TSubject to record delete information  
create trigger delete_TSubject on dbo.TSubject for delete  
as  
insert into dbo.audit_TSubject(subJectID,subJectName,BookName,Publisher,Actions) select *,'delete' from deleted  
  
select * from TSubject  
  
delete TSubject where subJectID = '1'  
  
select * from audit_TSubject  

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

I used the statements that you posted and adapted them a little to my system. I tried setting up a role fisops, and I created user fisse who is member of that group. I then impersonated that user, and that user's action were audited, whereas my own actions were not. Maybe you made a mistake somewhere in your test?

This is my script:

CREATE SERVER AUDIT [audit_fisops]
TO FILE
( FILEPATH = N'/tmp/audit/'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '71b427e3-aeeb-49f7-b239-b442b2b3a220'
)
ALTER SERVER AUDIT [audit_fisops] WITH (STATE = ON)
go
USE Northgale_utf8
go
CREATE ROLE fisops
CREATE USER fisse WITHOUT LOGIN
ALTER ROLE fisops ADD MEMBER fisse
GRANT INSERT, SELECT, DELETE, UPDATE ON SCHEMA::dbo TO fisse
go
CREATE DATABASE AUDIT SPECIFICATION [test]
FOR SERVER AUDIT [audit_fisops]
ADD (DELETE ON SCHEMA::[dbo] BY [fisops]),
ADD (INSERT ON SCHEMA::[dbo] BY [fisops]),
ADD (RECEIVE ON SCHEMA::[dbo] BY [fisops]),
ADD (SELECT ON SCHEMA::[dbo] BY [fisops]),
ADD (UPDATE ON SCHEMA::[dbo] BY [fisops])
WITH (STATE = ON)

go
SELECT * FROM Orders WHERE OrderID = 10248
EXECUTE AS USER = 'fisse'
SELECT * FROM Orders WHERE OrderID = 11000
go
REVERT
go
UPDATE Customers SET CustomerName = 'Berglunds Supermkaret' WHERE CustomerID = 'BERGS'
EXECUTE AS USER = 'fisse'
UPDATE Customers SET CustomerName = 'Futterkiste des Alfreds' WHERE CustomerID = 'ALFKI'
go
REVERT
go


SELECT
event_time ,
session_server_principal_name AS UserName ,
server_instance_name ,
database_name ,
object_name ,  
statement, *
FROM sys.fn_get_audit_file('/tmp/audit/*.sqlaudit', DEFAULT, DEFAULT)
WHERE
action_id = 'SL'
AND
database_name = 'Northgale_utf8';

I will post the output separately due to some technical issues at my end.

Answer 4

thank you so much and still the same problem audit is not working

Answer 5

thank you so much , that was the problem thank you again

However , question - how can I set up audit for individual users, I dont want key stroke on dbo id

for example
I need to see what user testuser is doing, can this be done in sqlserver?
thanks