I can confirm in SQL Server 2008 R2 Service Pack 3 (v10.50.6000.34), simple parameterized queries do result in the parameterized values being recorded in the audit log.
I setup a simple test rig, as follows.
Create a server audit:
USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
BEGIN
CREATE SERVER AUDIT AuditTest
TO FILE (FILEPATH = 'D:\SQLServer\MV\Audits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
WITH (
QUEUE_DELAY = 0
, ON_FAILURE = CONTINUE
, AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
);
END
ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
GO
Create a database where we can play with auditing:
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest')
BEGIN
ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE AuditTest;
END
CREATE DATABASE AuditTest;
ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
BACKUP DATABASE AuditTest TO DISK = 'NUL:';
ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
GO
Create a table in the database:
USE AuditTest;
CREATE TABLE dbo.TestTable
(
id int NOT NULL
CONSTRAINT FK_TestTable
PRIMARY KEY
CLUSTERED
IDENTITY(1,1)
, someVal varchar(30) NOT NULL
);
Create a database audit spec:
CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
FOR SERVER AUDIT AuditTest
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
WITH (STATE = ON);
Create a stored procedure and trigger an audit action:
CREATE PROCEDURE dbo.AuditTest
(
@t varchar(50)
)
AS
BEGIN
INSERT INTO dbo.TestTable (someVal) --stored procedure
VALUES (@t);
END
GO
EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
GO
Perform a direct parameterized test:
DECLARE @t varchar(50);
SET @t = 'runtime value not audited - direct insert';
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);
SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;
Finally, run a simple-parameterization test:
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
GO
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
GO
The execution plan for the above two simple-parameterized statements shows simple parameterization is working as expected:
Read the audit log, using a cursor and PRINT
to preserve formatting:
USE master;
DECLARE @stmt nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT af.statement
FROM fn_get_audit_file('D:\SQLServer\MV\Audits\AuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
OPEN cur;
FETCH NEXT FROM cur INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @stmt;
FETCH NEXT FROM cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;
Results:
INSERT INTO dbo.TestTable (someVal) --stored procedure
VALUES (@t);
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);
SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
As you can see in the above output, the simple-parameterized statement run-time values were captured in the audit log.
This is expected behaviour SYS
auditing will always go to the filesystem.
The documentation for AUDIT_FILE_DEST
states:
Oracle Database also writes mandatory auditing information to this
location, and if the AUDIT_SYS_OPERATIONS initialization parameter is
*set, writes audit records for user SYS*.
... and the documentation for AUDIT_SYS_OPERATIONS
states:
Enables or disables the auditing of top-level operations directly
issued by user SYS, and users connecting with SYSDBA or SYSOPER
privilege. Oracle Database writes the audit records to the audit trail
of the operating system. If you set the AUDIT_TRAIL initialization
parameter to XML or XML, EXTENDED, it writes the audit records in XML
format. On UNIX systems, if you have also set the AUDIT_SYSLOG_LEVEL
parameter, then it overrides the AUDIT_TRAIL parameter, which writes
the SYS audit records to the system audit log using the SYSLOG
utility.
Documentation link here.
Best Answer
The resolution is
SET GLOBAL audit_log_include_accounts='someuser@';
- without host part of definition, BUT with@