Database Permissions#

AuditChain's Eloquent boot guards prevent updates and deletes at the application layer, but a compromised application can bypass Eloquent entirely and run raw SQL. A dedicated database user with restricted privileges closes this gap by enforcing immutability at the database level.

Why This Matters#

The AuditLog model throws a RuntimeException if you try to update or delete a record through Eloquent. This is effective against accidental modifications and application-level bugs, but it does not protect against:

• Direct SQL queries (DB::statement('DELETE FROM audit_logs ...'))
• A compromised application running arbitrary database commands
• An attacker with access to database credentials used by the main application
• Database management tools (phpMyAdmin, TablePlus, etc.) connected with the app user

By giving the audit connection a database user that can only INSERT and SELECT, you ensure that audit logs cannot be modified or deleted even if the application is fully compromised.

Setting Up a Dedicated Connection#

1. Create a Restricted Database User#

MySQL#

-- Create the user
CREATE USER 'audit_writer'@'%' IDENTIFIED BY 'a-strong-random-password';

-- Grant only INSERT and SELECT on the audit table
GRANT INSERT, SELECT ON your_database.audit_logs TO 'audit_writer'@'%';

-- Apply changes
FLUSH PRIVILEGES;

PostgreSQL#

-- Create the user
CREATE USER audit_writer WITH PASSWORD 'a-strong-random-password';

-- Grant only INSERT and SELECT on the audit table
GRANT INSERT, SELECT ON audit_logs TO audit_writer;

-- Grant USAGE on the schema (required for PostgreSQL)
GRANT USAGE ON SCHEMA public TO audit_writer;

If your audit table uses a sequence for any column (uncommon with ULIDs, but possible), you will also need GRANT USAGE, SELECT ON SEQUENCE audit_logs_id_seq TO audit_writer; in PostgreSQL.

2. Add the Connection to config/database.php#

Define a new connection that uses the restricted user:

// config/database.php
'connections' => [
    // Your main application connection
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE', 'your_database'),
        'username' => env('DB_USERNAME', 'app_user'),
        'password' => env('DB_PASSWORD', ''),
        // ...
    ],

    // Dedicated audit connection (INSERT + SELECT only)
    'audit' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE', 'your_database'),
        'username' => env('AUDIT_DB_USERNAME', 'audit_writer'),
        'password' => env('AUDIT_DB_PASSWORD', ''),
        // ...remaining options same as your main connection
    ],
],

3. Point AuditChain to the Restricted Connection#

Set the connection in config/audit-chain.php:

'connection' => 'audit',

Or via your .env file if you prefer:

AUDIT_DB_USERNAME=audit_writer
AUDIT_DB_PASSWORD=a-strong-random-password

Running Migrations#

The restricted audit_writer user cannot create or alter tables. Run migrations using your main application connection (the default). AuditChain's migration does not specify a connection, so it uses the default database connection automatically.

Pruning Audit Logs#

The audit:prune command deletes old audit logs. Since the restricted user lacks DELETE privileges, pruning will fail if the audit connection is the only one available. Two approaches:

Option A: Run the prune command with a different connection that has DELETE privileges. You can override the connection temporarily in your scheduled task:

Schedule::command('audit:prune --days=365')
    ->daily()
    ->before(function () {
        config(['audit-chain.connection' => 'mysql']);
    })
    ->after(function () {
        config(['audit-chain.connection' => 'audit']);
    });

Option B: Run the prune query manually using your main database connection outside of AuditChain.

Verifying Permissions#

Confirm that the restricted user cannot modify or delete audit records:

-- These should succeed
INSERT INTO audit_logs (id, auditable_type, auditable_id, event, created_at)
VALUES ('test', 'App\\Models\\User', '1', 'test', NOW());

SELECT * FROM audit_logs WHERE id = 'test';

-- These should fail with a permission error
UPDATE audit_logs SET event = 'tampered' WHERE id = 'test';
DELETE FROM audit_logs WHERE id = 'test';

Clean up the test row using your main application user after verifying.

Defense in Depth#

Database-level permissions are one layer in AuditChain's immutability strategy:

No single layer is sufficient on its own. Together, they provide strong assurance that your audit trail has not been tampered with.