Oracle auditing: Is it possible to log when a permission is being used

A.B.Cade and Phil gave the answer in the comments:

CONNECT THROUGH cannot be granted using a role.

As Phil said, creating a script to do this is trivial. Be aware the database links do not work the same when used via proxy.

This situation does have risk, but given the requirements and limitations in Oracle, it is sometimes necessary. You might consider allowing the script to accept two usernames. The DBA can then run the script on demand for the particular client and support person combination that needs it. By revoking the privilege when the support session is complete there will be less the potential for abuse.

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.