Database Security Auditing for Non-Technical Teams: Track Who's Accessing What

Most companies don't discover a data breach from a security alert. They discover it from a customer complaint, a competitor who knows too much, or an entry in a log file that someone finally decided to look at. By then, the damage is done.

Database auditing tracking who accessed what data, when, and why is the foundation of any serious data security practice. But for years it's been the exclusive territory of DBAs and security engineers. The tools were complex, the queries were long, and getting a simple answer like "who looked at our customer table last week" required opening a ticket with the engineering team.

This guide explains what database auditing covers, what to monitor, and how non-technical teams can now get meaningful answers about data access without writing a single SQL query.

What Database Auditing Actually Covers

Database auditing is the practice of recording and reviewing database activity reads, writes, logins, schema changes, and permission modifications. The goal is to answer three questions:

A secondary goal is detecting anomalies: a user who normally reads 100 rows suddenly querying 50,000, a login from an unfamiliar IP address, a schema change made outside business hours.

Most enterprise databases PostgreSQL, MySQL, SQL Server, Oracle have built-in audit logging features. They write activity records to a log table or log file. The challenge has never been collecting this data. It's been querying and interpreting it without a SQL background.

Setting Up Basic Audit Logging in PostgreSQL

PostgreSQL uses the pgaudit extension for comprehensive audit logging. Here's what enabling it looks like at the configuration level:

 Enable the pgaudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

 Configure what to log (in postgresql.conf or as a session variable)
SET pgaudit.log = 'read, write, ddl';

 Log to the standard PostgreSQL log file
SET pgaudit.log_client = on;

Once enabled, pgaudit writes entries like this to your logs whenever a table is accessed:

AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.customers,
SELECT id, email, plan FROM customers WHERE created_at > '2026-01-01';

For MySQL, the equivalent is the general_log or audit_log plugin:

 Enable general query log (MySQL)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

Most managed database services AWS RDS, Supabase, PlanetScale have audit logging enabled through the dashboard. You don't need to touch configuration files. You just need to know where the logs land and how to query them.

The Audit Queries That Actually Matter

Once you have logging in place, you'll query an audit log table or parse log files. Here are the SQL queries that answer the most common security questions.

Who accessed the customers table in the last 7 days?

SELECT
  session_user_name,
  COUNT(*) AS query_count,
  MIN(statement_timestamp) AS first_access,
  MAX(statement_timestamp) AS last_access
FROM pgaudit_log
WHERE
  object_name = 'customers'
  AND statement_timestamp > NOW() - INTERVAL '7 days'
GROUP BY session_user_name
ORDER BY query_count DESC;

Were there any data exports (large SELECT queries) recently?

SELECT
  session_user_name,
  statement_timestamp,
  rows_returned,
  LEFT(statement_text, 200) AS query_preview
FROM pgaudit_log
WHERE
  command_tag = 'SELECT'
  AND rows_returned > 10000
  AND statement_timestamp > NOW() - INTERVAL '30 days'
ORDER BY rows_returned DESC;

Has anyone modified the schema in the last 30 days?

SELECT
  session_user_name,
  statement_timestamp,
  command_tag,
  object_type,
  object_name,
  statement_text
FROM pgaudit_log
WHERE
  audit_type = 'DDL'
  AND statement_timestamp > NOW() - INTERVAL '30 days'
ORDER BY statement_timestamp DESC;

Any failed login attempts?

SELECT
  usename,
  client_addr,
  backend_start,
  state
FROM pg_stat_activity
WHERE state = 'idle in transaction'
UNION ALL
SELECT
  log_user AS usename,
  connection_from AS client_addr,
  statement_timestamp AS backend_start,
  'failed login' AS state
FROM pgaudit_log
WHERE command_tag = 'CONNECT' AND error_code IS NOT NULL;

These queries work but require knowing the exact table and column names of your audit log schema, which varies depending on your database and how logging was configured.

Asking Security Questions in Plain English

This is where the workflow changes for non-technical teams. Instead of writing those queries or filing a ticket with engineering, you connect your database to AI for Database and ask questions like:

The tool translates each question into SQL, runs it against your live database, and returns the results as a readable table. You don't need to know the column names in your audit log or how to write a GROUP BY clause.

This is particularly useful for compliance teams, operations managers, and anyone who needs to answer an auditor's question quickly without pulling in a developer.

What to Monitor Regularly

A reactive audit "we think something went wrong, let's look at the logs" is better than nothing. A proactive monitoring schedule is much better.

Daily:

Weekly:

Monthly:

The key is turning these into scheduled queries that run automatically and flag anomalies. AI for Database's action workflows can do this: set a condition like "if any DDL change is detected outside business hours" and trigger a Slack message or email to your security team. No stored procedures, no cron jobs to maintain.

Role-Based Access Control: The First Line of Defense

Auditing tells you what happened. Role-based access control (RBAC) determines what's possible. Before you can have a meaningful audit, you need to know what each database user should and shouldn't be allowed to do.

Here's how to check existing permissions in PostgreSQL:

 See all database roles and their privileges
SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_name;

 Check what a specific user can do
SELECT
  privilege_type,
  table_name
FROM information_schema.role_table_grants
WHERE grantee = 'reporting_user';

The principle of least privilege says every user should have exactly the access they need and nothing more. A reporting user should be able to SELECT from specific tables but not INSERT, UPDATE, or DELETE. An application service account should only access the tables the application needs, not the entire database.

Quarterly access reviews asking "does this user still need this access?" are good hygiene and often required by SOC 2, ISO 27001, and similar frameworks.

Compliance and Database Auditing

If your company is working toward SOC 2 Type II, GDPR compliance, HIPAA, or PCI-DSS, database audit logs aren't optional. Auditors will ask for them.

The specific requirements vary by framework, but they generally cover:

GDPR/CCPA You need to be able to demonstrate who accessed personal data and when. If a data subject makes a Subject Access Request, you should be able to show a complete access history. If there's a breach, you need to be able to reconstruct what was accessed.

SOC 2 The Common Criteria require logical access controls and monitoring. Auditors will ask for evidence that you have audit logging in place and that someone actually reviews the logs.

HIPAA For healthcare applications, audit controls are an addressable implementation specification. You need to record and examine activity in systems containing protected health information (PHI).

PCI-DSS Requirement 10 explicitly requires logging and monitoring all access to cardholder data and network resources.

In all cases, audit logs need to be stored securely, retained for a minimum period (typically 1–3 years depending on the framework), and protected from tampering.

Building a Security Posture That Actually Works

Database security doesn't require a full-time security engineer. It requires consistent habits: audit logging turned on, access permissions reviewed quarterly, and someone actually looking at the logs.

The gap for most small and mid-sized companies isn't the tooling it's the time. Querying audit logs in SQL takes time and expertise. When it's hard, it doesn't happen. When you can ask "who accessed our customer data this week" in plain English and get an answer in seconds, it becomes something anyone on your team can do as a regular check.

Try AI for Database free at aifordatabase.com. Connect your database and start asking security questions in plain English no DBA required.