Data Branch Privilege Model¶

Every DATA BRANCH command in MatrixOne now requires specific, operation-scoped privileges on both the source and target objects. This page documents the privilege requirements for each branch operation, the error messages users may encounter, and the protected database mechanism.

Description¶

MatrixOne enforces fine-grained privilege checks for all DATA BRANCH SQL commands. Each operation type requires specific object-level privileges on the source and target tables or databases. The privilege model also includes cross-account cloning restrictions, system database protection, and a protected-database mechanism that prevents accidental drops.

Syntax¶

The privilege model is enforced through standard SQL GRANT and REVOKE statements. Below are the privilege patterns required for each DATA BRANCH operation.

DATA BRANCH CREATE TABLE¶

GRANT SELECT ON TABLE <source_db>.<source_table> TO <role>;
GRANT CREATE TABLE ON DATABASE <target_db> TO <role>;

DATA BRANCH CREATE DATABASE¶

GRANT SELECT ON TABLE <source_db>.<source_table> TO <role>;
GRANT CREATE DATABASE ON ACCOUNT * TO <role>;

DATA BRANCH DIFF¶

GRANT SELECT ON TABLE <db>.<target_table> TO <role>;
GRANT SELECT ON TABLE <db>.<base_table> TO <role>;

DATA BRANCH MERGE¶

GRANT SELECT ON TABLE <db>.<source_table> TO <role>;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <db>.<destination_table> TO <role>;

DATA BRANCH PICK¶

GRANT SELECT ON TABLE <db>.<source_table> TO <role>;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <db>.<destination_table> TO <role>;
GRANT SELECT ON TABLE <db>.<keys_table> TO <role>;

DATA BRANCH DELETE TABLE¶

GRANT DROP TABLE ON DATABASE <db> TO <role>;

DATA BRANCH DELETE DATABASE¶

GRANT DROP DATABASE ON ACCOUNT * TO <role>;

Arguments¶

Privilege	Scope	Description
`SELECT`	Table	Allows reading rows from the specified table. Required on source tables for all branch read operations and on both sides for DIFF, MERGE, and PICK.
`CREATE TABLE`	Database	Allows creating new tables in the specified database. Required on the target database for `DATA BRANCH CREATE TABLE`.
`CREATE DATABASE`	Account	Allows creating new databases in the account. Required for `DATA BRANCH CREATE DATABASE`.
`INSERT`	Table	Allows inserting new rows into the specified table. Required on the destination table for `DATA BRANCH MERGE` and `DATA BRANCH PICK`.
`UPDATE`	Table	Allows modifying existing rows in the specified table. Required on the destination table for `DATA BRANCH MERGE` and `DATA BRANCH PICK`.
`DELETE`	Table	Allows deleting rows from the specified table. Required on the destination table for `DATA BRANCH MERGE` and `DATA BRANCH PICK`, and on the target table for `REPLACE INTO` conflict paths.
`DROP TABLE`	Database	Allows dropping tables in the specified database. Required for `DATA BRANCH DELETE TABLE`.
`DROP OBJECT`	Database	Allows dropping objects in the specified database. Satisfies the requirement for `DATA BRANCH DELETE TABLE`.
`DROP DATABASE`	Account	Allows dropping databases in the account. Required for `DATA BRANCH DELETE DATABASE`.
`DatabaseAll`	Database	Grants all database-level privileges. Satisfies requirements for `CREATE TABLE`, `DROP TABLE`, and `DROP OBJECT` scoped to a database.
`DatabaseOwnership`	Database	Grants ownership-level privileges on a database. Satisfies requirements for `CREATE TABLE`, `DROP TABLE`, and `DROP OBJECT` scoped to a database.
`AccountAll`	Account	Grants all account-level privileges. Satisfies `DROP DATABASE` and `CREATE DATABASE` requirements.

Privilege Requirements by Operation¶

DATA BRANCH CREATE TABLE¶

Creates a branch table from a source table.

Object	Required Privileges
Source table	`SELECT`
Destination database	One of: `CREATE TABLE`, `DatabaseAll`, `DatabaseOwnership`

Cross-account clones require a snapshot. Only sys accounts can clone to another account.

DATA BRANCH CREATE DATABASE¶

Creates a branch database from a source database.

Object	Required Privileges
Each source table	`SELECT`
Destination	`CREATE DATABASE` (validated via standard CREATE DATABASE authorization)

DATA BRANCH DELETE TABLE¶

Deletes a branch table. The target must be an active branch table (created by a prior DATA BRANCH CREATE TABLE).

Object	Required Privileges
Target database	One of: `DROP TABLE`, `DROP OBJECT`, `DatabaseAll`, `DatabaseOwnership`

For non-system databases, table ownership also satisfies the requirement.

DATA BRANCH DELETE DATABASE¶

Deletes a branch database. All tables in the target database must be active branch tables.

Object	Required Privileges
Account	One of: `DROP DATABASE`, `AccountAll`

For non-system databases, database ownership also satisfies the requirement.

DATA BRANCH DIFF¶

Compares two tables and outputs the differences.

Object	Required Privileges
Target table	`SELECT`
Base table	`SELECT`

DATA BRANCH MERGE¶

Merges changes from a source table into a destination table.

Object	Required Privileges
Source table	`SELECT`
Destination table	`SELECT`, `INSERT`, `UPDATE`, `DELETE`

DATA BRANCH PICK¶

Copies selected rows from a source table into a destination table by primary key.

Object	Required Privileges
Source table	`SELECT`
Destination table	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
KEYS subquery tables	`SELECT`

Cross-Account Cloning Restrictions¶

Cloning a table or database from one account to another requires a snapshot ({snapshot = '<name>'} or {timestamp = <expr>}).
Only sys account users can clone to another account.
COPY GRANTS cannot be used with TO ACCOUNT.

System Database Protection¶

Non-sys accounts cannot clone data from system databases (mo_catalog, system, information_schema). No account can clone data into system databases.

Protected Databases¶

MatrixOne supports a protected_databases system variable that marks specified databases as protected from accidental drops.

SET GLOBAL protected_databases = 'db1,db2';

When a database is listed in protected_databases:

DROP DATABASE on the protected database is rejected, even for users with DatabaseAll and DatabaseOwnership privileges.
All other operations (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, CREATE VIEW) on the protected database continue to work normally.
Only users with the ability to set global variables can modify protected_databases.

REPLACE INTO DELETE Privilege¶

When REPLACE INTO encounters a unique-key conflict, it internally performs a DELETE of the conflicting row. Users must hold DELETE privilege in addition to SELECT and INSERT for REPLACE INTO to succeed on conflict paths. Pure-insert paths (no conflict, such as NULL unique values) succeed without DELETE privilege.

Examples¶

The following examples demonstrate how privileges interact with DATA BRANCH operations. All statements combine into a single runnable script that can be executed against an empty MatrixOne instance.

DROP DATABASE IF EXISTS dbbranchpriv;
CREATE DATABASE dbbranchpriv;
USE dbbranchpriv;

-- Create source table and data for branch operations
CREATE TABLE src_t(a INT PRIMARY KEY, b VARCHAR(20));
INSERT INTO src_t VALUES (1, 'one'), (2, 'two');

-- Create target table for DIFF operations
CREATE TABLE target_t(a INT PRIMARY KEY, b VARCHAR(20));
INSERT INTO target_t VALUES (1, 10), (3, 30);

-- Create a snapshot for table-level branch operations
CREATE SNAPSHOT sp_t FOR TABLE dbbranchpriv src_t;

-- Create a role with SELECT on source and CREATE TABLE on database
CREATE ROLE r_branch;
GRANT SELECT ON TABLE dbbranchpriv.src_t TO r_branch;
GRANT CREATE TABLE ON DATABASE dbbranchpriv TO r_branch;
CREATE USER u_branch IDENTIFIED BY '111' DEFAULT ROLE r_branch;

-- DATA BRANCH CREATE TABLE: creates a branch table from the source snapshot
DATA BRANCH CREATE TABLE dbbranchpriv.branch_t FROM dbbranchpriv.src_t{snapshot="sp_t"};

-- Verify the branch table contains source data
SELECT COUNT(*) FROM dbbranchpriv.branch_t;

-- DATA BRANCH DIFF: compares two tables and outputs the differences
DATA BRANCH DIFF dbbranchpriv.target_t AGAINST dbbranchpriv.src_t OUTPUT COUNT;

-- Error case: DATA BRANCH DELETE TABLE fails when the target is not a branch-created table
-- Expected-Success: false
DATA BRANCH DELETE TABLE dbbranchpriv.target_t;

-- Cleanup
DROP SNAPSHOT sp_t;
DROP USER u_branch;
DROP ROLE r_branch;
DROP DATABASE IF EXISTS dbbranchpriv;

Error Messages¶

Error	Cause
`internal error: do not have privilege to execute the statement`	Missing required privilege for the attempted branch operation
`internal error: non-sys account cannot clone data from system database`	Non-sys account attempting to clone from `mo_catalog`, `system`, or `information_schema`
`internal error: cannot clone data into system database`	Attempting to clone into a system database
`internal error: DATA BRANCH DELETE target <name> is not an active branch table`	DELETE target is not a branch-created table
`internal error: DATA BRANCH DELETE target <name> is not an active branch database`	DELETE target database has no active branch tables
`invalid input: COPY GRANTS cannot be used with TO ACCOUNT`	Combining COPY GRANTS with cross-account clone
`internal error: clone table between different accounts need a snapshot`	Cross-account clone without a snapshot

Usage Notes¶

Privilege checks are skipped for internal system users and when SkipCheckPrivilege is enabled.
DATA BRANCH CREATE DATABASE resolves source tables during privilege checking to ensure the same object set is authorized and executed.
For DATA BRANCH DELETE TABLE and DELETE DATABASE, the system validates that the target objects are active branch objects (tracked in mo_catalog.mo_branch_metadata).
Branch operations that require multiple privileges check each independently; missing any one privilege causes the entire statement to fail.