Database Firewall and Dynamic Data Masking Guide

Most database breaches end with a valid connection running SQL the application never needed, and the database happily answers. This guide covers the controls built into the database engine itself that sit in line with that SQL: connection allow-lists, statement allow-lists that can block, privileged-user restrictions and query-time masking. It is for DBAs, data security engineers and architects deciding what to switch on natively before, or alongside, an external database activity monitoring (DAM) tier. Platforms are listed alphabetically: Google BigQuery, Microsoft (SQL Server and Azure SQL), Oracle Database and PostgreSQL. Order is never a ranking. Masking is summarised here and covered in depth in the linked masking guides.

Key takeaways

Layer the controls by question. Connection firewalls answer "who may connect from where", statement allow-lists answer "which SQL may run", privileged-user controls answer "what may a DBA touch", and masking answers "what does the result show".
Statement allow-listing is the strongest in-line control. Oracle SQL Firewall captures a database account's normal SQL and session context, then logs or blocks anything outside that allow-list, including SQL injection through a valid application account.
Masking is not access control. Microsoft and Oracle both document that a user with ad hoc query rights can infer masked values through WHERE clauses, so masking protects against accidental exposure, not a determined insider.
Start in observe mode. Every blocking control here has a log-only or test option: SQL Firewall with block set to FALSE, Oracle Data Redaction type None, impersonation tests for SQL Server masks.
The service account is the unit of policy. Firewall allow-lists, masking exemptions and UNMASK grants all attach to database principals, so application and integration accounts need the tightest rules.

Database firewall, DAM and masking: what each control does

"Database firewall" is used for three different things. A network firewall rule limits which IP ranges reach the listener. A native SQL firewall inspects each statement inside the engine against an allow-list. A DAM appliance or agent watches traffic or audit streams and may block from outside the engine. Dynamic masking changes what is returned without changing what is stored. The Database Activity Monitoring buyer's guide covers the external tier; this page stays with what the engine itself provides.

Control	Where it acts	Stops	Leaves open
Connection firewall (IP rules, pg_hba.conf)	Before authentication	Connections from unexpected networks; scanning of exposed endpoints	Anything sent over an allowed connection with valid credentials
Native SQL allow-list (Oracle SQL Firewall)	Inside the engine, per statement, per account	Injected or unexpected SQL from a known account; use of a stolen application credential from a new host or program	Abuse that uses only allow-listed statements
Privileged-user controls (Oracle Database Vault realms, command rules)	Inside the engine, on system privileges	DBA or ANY-privilege access to application data; destructive DDL	Access by the authorised realm owner
Dynamic masking or redaction	Result set at query time	Casual or accidental exposure in reports, support tools and analytics	Inference through WHERE clauses; writes; users with exemption privileges
Detection alerts (for example Microsoft Defender for SQL)	Analysis of activity	Nothing directly; raises alerts for response	Prevention, unless wired to automated response

Google BigQuery

Connection layer: access is governed through Google Cloud IAM and network perimeter controls at the project level rather than a database listener; see the BigQuery Security Guide for IAM and perimeter settings.
Dynamic data masking: masking builds on column-level access control. A taxonomy holds policy tags; a data policy links a masking rule and principals to a policy tag, or can be set directly on a column. One policy tag can carry up to nine data policies, one of which is reserved for column-level access settings.
Masking rules: custom masking routine, random hash, hash (SHA-256), email mask, last four characters, first four characters, date year mask, default masking value and nullify. When several policies apply to one user on one column, BigQuery applies the highest-ranked rule in that order.
Roles: Masked Reader grants access to masked data; Google recommends granting it at the data policy level rather than the project, which would cover every data policy in the project. Principals with Fine-Grained Reader on a tag used for column-level access control see original values.
Join behaviour: rule choice affects joins. Random hash supports joins only within the same query, so pick SHA-256 where analysts must join masked keys across queries, and accept that a deterministic hash of a low-cardinality value can be reversed by guessing.

Microsoft SQL Server and Azure SQL

Azure SQL IP firewall: server-level rules (stored in master, up to 256 per server) and database-level rules (stored in each database, up to 256, T-SQL only, needing at least CONTROL DATABASE). Database-level rules are checked first, then server-level rules, then the connection fails. Manage them with sp_set_firewall_rule and sp_set_database_firewall_rule, review with sys.firewall_rules and sys.database_firewall_rules. Microsoft recommends database-level rules where possible.
The 0.0.0.0 rule: "Allow Azure services" creates AllowAllWindowsAzureIps, which Microsoft warns admits connections from all Azure services, including other customers' subscriptions. New portal servers default it to No. Prefer private endpoints or virtual network service endpoints.
Propagation: server-level changes can take up to five minutes; DBCC FLUSHAUTHCACHE forces a refresh. A firewall rule only allows a connection attempt, credentials are still required.
Dynamic data masking: available in SQL Server 2016 and later, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics and SQL database in Microsoft Fabric. Functions are default(), email(), random(start, end), partial(prefix, padding, suffix) and, from SQL Server 2022, datetime(). List masks with sys.masked_columns.
Who sees clear data: UNMASK can be granted at column, table, schema or database level (granular scope from SQL Server 2022). ALTER ANY MASK changes masks. CONTROL on the database, CONTROL SERVER, sysadmin and db_owner always see original data.
Detection alerts: Microsoft Defender for SQL raises "A possible vulnerability to SQL Injection" (an application generated a faulty statement) and "Potential SQL injection" (active exploitation), plus brute force, unusual location and harmful application alerts. These are detections, so route them to a response workflow.
Deeper settings: see the Microsoft SQL Server Security Guide and the Azure SQL and Cosmos DB Security Guide.

Oracle Database

SQL Firewall: built into the database kernel and managed with SYS.DBMS_SQL_FIREWALL (role SQL_FIREWALL_ADMIN) or fleet-wide through Oracle Data Safe. Oracle's current documentation lists it for Oracle AI Database 26ai and later. It inspects local and network SQL, encrypted or clear, including SQL from PL/SQL units.
Workflow: ENABLE, CREATE_CAPTURE for an account (optionally top_level_only), run the normal workload, STOP_CAPTURE, GENERATE_ALLOW_LIST, then ENABLE_ALLOW_LIST.
Enforcement: enforce takes ENFORCE_CONTEXT (1), ENFORCE_SQL (2) or ENFORCE_ALL (3, default). block defaults to FALSE, which logs violations and lets them run; TRUE blocks. Violations are logged either way and appear in DBA_SQL_FIREWALL_VIOLATIONS.
Session context: ADD_ALLOWED_CONTEXT pins an account to IP addresses or CIDR ranges, OS user names and OS program names. This is how a stolen application password used from an analyst laptop gets blocked even if the SQL looks normal.
Database Vault: realms (including mandatory realms that block even object owners and direct grants) keep DBA and ANY-privilege accounts out of application schemas; command rules, rule sets and factors restrict statements such as DROP TABLE to trusted paths. Separate duties across DV_OWNER, DV_ADMIN and DV_ACCTMGR.
Data Redaction: redaction types Full, Partial, Regular expression, Random, Nullify and None (None applies the policy with no effect so you can test). Policies decide by session context. Oracle states it is not a control against malicious or privileged ad hoc users.
Availability: Oracle documents edition and option requirements per feature; confirm licensing for SQL Firewall, Database Vault and Data Redaction against your Oracle agreement. See the Oracle Database Security Guide and Oracle Autonomous Database Security.

PostgreSQL

Connection firewall: pg_hba.conf records (local, host, hostssl, hostnossl, hostgssenc, hostnogssenc) match database, user and address, first match wins with no fall-through, and no match means denial. Put reject lines for specific hosts above broader allows and use scram-sha-256; md5 is deprecated.
Restrict the listener: remote connections also need listen_addresses, which listens on localhost by default. Use hostssl rather than host for application networks so unencrypted connections never match.
Test before reload: query pg_hba_file_rules and check the error column, then reload with SELECT pg_reload_conf();. Pair the file with GRANT/REVOKE CONNECT, which PostgreSQL notes is often simpler than complex rules.
Statement control: PostgreSQL core governs statements through privileges, row security policies and views; teams that need statement allow-listing in line add a database proxy or DAM tier in front of the server.
Dynamic masking: the PostgreSQL Anonymizer extension provides it. Enable with ALTER DATABASE ... SET anon.transparent_dynamic_masking TO true, declare rules with SECURITY LABEL FOR anon ON COLUMN, and mark roles with SECURITY LABEL FOR anon ON ROLE ... IS 'MASKED'. Masked roles should not write data, and cannot use EXPLAIN or read SECURITY INVOKER views.
Deeper settings: see the PostgreSQL Security Guide.

Masking in brief: where it fits

Dynamic masking and redaction transform results at query time, so stored data and existing queries are unchanged. Microsoft and Oracle both document the same limits: users who can filter on a masked column can narrow down the real value (a salary between 99,999 and 100,001), users with write permission can still update masked columns, and operations such as SELECT INTO or exports run by a masked user carry masked values into the copy. Treat masking as a presentation control layered on top of least privilege and auditing. For the choice between masking and filtering rows, see Dynamic Data Masking vs Row-Level Security and the Row and Column Level Security Guide; for copies in non-production, see Static vs Dynamic Data Masking.

Choosing native controls or an external tier

Situation	Native first	Add external DAM or proxy when
One engine family, stable application SQL	Statement allow-list per application account, context pinned to app servers	Auditors need one evidence store across many engines
Mixed estate (several engines, cloud and on-premises)	Connection firewalls everywhere, masking where the engine provides it	You need consistent statement-level policy and blocking across engines
DBA insider risk is the main concern	Realms or equivalent privilege fencing, separation of duties	Monitoring must be independent of the DBAs who administer the engine
Analytics and support teams see personal data	Dynamic masking with narrowly scoped exemptions	Masking must follow data out of the database into files and exports
Small team, few databases	Network rules, least privilege, native audit, masking on the handful of sensitive columns	Rarely needed at first; revisit when database count or regulation grows

Native controls cost no extra hops and see encrypted and local traffic, which a network appliance does not. The trade-off is that the people who administer the database can usually administer the control, which is why separation of duties and off-host log forwarding matter.

Where identity fits

Every in-line control here keys on a database principal. Application and integration accounts run the most SQL and hold the broadest grants, and their credentials sit in configuration files and pipelines. A SQL allow-list with IP and program context turns a leaked service account password into a blocked, logged attempt rather than a silent data pull. Keep one database account per application so allow-lists stay small, never grant UNMASK or redaction exemptions to service accounts that feed exports, and review those grants with the Database Privileged Access Review Template. For DBA access paths see DBA Privileged Access.

How to implement

Inventory sensitive columns and the accounts that read them. Classify columns, then list each principal with SELECT on them, separating people, applications and admins.
Close the network edge. Replace broad IP rules (including the Azure 0.0.0.0 rule) with private connectivity or narrow ranges; rewrite pg_hba.conf with specific hostssl lines and test with pg_hba_file_rules.
Capture normal SQL. For each application account, capture a full business cycle (month-end jobs included) before generating an allow-list.
Enforce in log mode. Enable the allow-list with blocking off for two to four weeks; review every violation, append legitimate SQL, and remove stale entries.
Switch to blocking per account. Start with low-change accounts, keep a documented rollback (re-run enforcement with block FALSE or disable the allow-list), and include allow-list updates in the application release process.
Apply masking. Add masks or redaction policies in test, verify by impersonating an unexempt user, then grant exemptions only to named roles.
Fence privileged users. Where the engine provides realms or command rules, protect application schemas from DBA and ANY privileges, starting in a non-production copy.
Forward logs off the host. Send violation logs, firewall changes and mask changes to the SIEM so the people administering the database cannot quietly erase them.

Detection and measurement

Violations: SQL and context violations per account (for example DBA_SQL_FIREWALL_VIOLATIONS); a burst from an application account is a priority alert.
Control changes: changes to firewall rules, allow-lists, masks (ALTER ANY MASK use), redaction policies and exemption grants.
Engine alerts: SQL injection and brute force alerts from the platform's threat detection, correlated with application logs.

Metric	Target direction
Share of application accounts with an enforced (blocking) allow-list	Rising towards all production application accounts
Allow-list violations per week after tuning	Low and stable; spikes investigated within one working day
Principals holding UNMASK, exemption or Fine-Grained Reader rights on sensitive columns	Falling, each with a named owner
Databases reachable from broad or public IP ranges	Zero
Sensitive columns covered by masking or column-level control	Rising towards the classified set

Common failure patterns

Capturing too short a window: the allow-list misses quarterly jobs, blocking breaks them, and the team disables enforcement for good.
Treating masking as a security boundary: analysts with ad hoc access infer values with range filters, or copy masked tables with write rights intact.
Exempting the service account: UNMASK or redaction exemption granted to the integration account that feeds a reporting export, so clear data leaves anyway.
Broad network rules left in place: a cloud "allow all services" rule or a 0.0.0.0/0 line in pg_hba.conf means authentication is the only barrier.
Admins controlling their own controls: the same DBA group can disable the firewall and purge its logs, and nothing is forwarded off the host.
Allow-lists not updated with releases: new application SQL is blocked in production because deployment did not include the allow-list change.

Framework mapping

Framework	Reference	How these controls apply
NIST SP 800-53 Rev 5	AC-3, AC-5, AC-6, SC-7, AU-12	Allow-lists and realms enforce access; Database Vault roles split duties; connection rules protect the boundary; violation logs generate audit records
NIST CSF 2.0	PR.AA-05, PR.DS-01	Least privilege on data access; protection of data at rest from unauthorised reading
CIS Controls v8.1	3.3, 8.2	Configure data access control lists; collect audit logs
ISO/IEC 27001:2022 Annex A	A.8.3, A.8.11, A.8.15	Information access restriction; data masking; logging
MITRE ATT&CK	T1190, T1078, T1213	SQL injection via public-facing applications, misuse of valid accounts, and collection from data repositories are what these controls block or expose

Practitioner checklist

List every principal with read access to classified columns, split by person, application and admin.
Remove broad network rules; prefer private connectivity and narrow, documented ranges.
Give each application its own database account so its allow-list is small and testable.
Capture a full business cycle before generating any statement allow-list.
Run allow-lists in log-only mode, tune, then block account by account with a written rollback.
Pin application accounts to their server addresses and program names where the engine supports context rules.
Mask sensitive columns for support and analytics roles and test by impersonating an unexempt user.
Review UNMASK, exemption and Fine-Grained Reader grants quarterly; none for export service accounts.
Forward violations and control changes to the SIEM and alert on changes made outside change windows.

Database Firewall and Dynamic Data Masking Guide: Native Controls That Block Queries and Hide Values