protected_databases

The protected_databases system variable specifies a comma-separated list of database names that are protected from DROP and other destructive DDL/DML operations by non-administrative users. Once a database is listed in protected_databases, users without the accountadmin or sys role cannot drop it, create objects in it, or modify its data. This provides a safeguard against accidental or unauthorized destruction of critical databases.

The protected_databases system variable is a global-only setting that defines which databases are shielded from DROP DATABASE, DDL modifications, and data manipulation by ordinary users. Only administrative users can modify a protected database or change the protected_databases list. This variable is useful in production environments to prevent accidental deletion of important databases.

Syntax

SET GLOBAL protected_databases = ‘db1,db2,db3’;

Query the current value:

SHOW VARIABLES LIKE ‘protected_databases’; SELECT @@global.protected_databases;

Arguments

Property

Value

Variable Type

string

Scope

Global

Dynamic

Yes

Default Value

""

Optional Value

Comma-separated database names

Examples

DROP DATABASE IF EXISTS protected_demo;
CREATE DATABASE protected_demo;
USE protected_demo;

CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (1, 1);

SET GLOBAL protected_databases = 'protected_demo';
SELECT @@global.protected_databases;

SELECT a FROM t1;

SET GLOBAL protected_databases = 'unused_value';
DROP DATABASE protected_demo;

Constraints

  • protected_databases is a global-only variable. It cannot be set at the session level.

  • The list cannot be cleared by setting it to '' or ','; these attempts will raise an internal error. To effectively clear protection, set it to a placeholder name (e.g., 'unused_value') that does not match any existing database.

  • Database names in the list are matched case-sensitively when lower_case_table_names is set to 0. When lower_case_table_names is 1, all names are compared in lowercase.

  • Protected databases block DDL (CREATE, ALTER, DROP, RENAME, CREATE INDEX, DROP INDEX, CREATE VIEW, DROP VIEW, ALTER VIEW), DML (INSERT, UPDATE, DELETE, REPLACE, TRUNCATE), and clone/snapshot operations by non-administrative users.

  • The accountadmin and sys roles retain full access to protected databases.