Principal、Privilege 、Policy and role support Spark SQL management.

[melin]

Is your feature request related to a problem? Please describe.

By combining Spark SQL and Polaris, and designing a set of Spark SQL syntax to manage Principal, Privilege, Policy and Role, it will be much more convenient to use.

The following is the SQL syntax designed for me by Gemini. As a reference:

🧭 Syntax Architecture Overview

In Apache Polaris, access control is structured around a few core entities:

• Principal: The identity performing the action (e.g., a user or service).
• Role: The grouping mechanism. Polaris distinguishes between Principal Roles (assigned to principals) and Catalog Roles (assigned to securable resources).
• Privilege: The specific action allowed on a resource (e.g., TABLE_READ, VIEW_CREATE).
• Policy: The binding logic or rule set that governs how roles, principals, and conditions interact.

Below is a summary of the proposed SQL commands for managing these entities.

Category	SQL Command Template	Description
Principal Management	CREATE / DROP / SHOW PRINCIPAL	Manages users and service identities.
Role Management	CREATE / DROP / SHOW ROLE	Manages Catalog and Principal roles.
Privilege Management	GRANT / REVOKE <privilege> ON <resource>	Controls access to catalogs, namespaces, and tables.
Policy & Assignment	GRANT ROLE TO or CREATE POLICY	Binds principals to roles, optionally with conditions.

---

🔍 Detailed SQL Syntax Design

1. Principal Management

Principals represent the "who" in your security model. They can be users or external service clients.

-- Create a new principal with optional credentials or properties
CREATE PRINCIPAL [IF NOT EXISTS] <principal_name>
TYPE { USER | SERVICE }
[ WITH PROPERTIES ( 'key1'='value1', 'key2'='value2' ) ];

-- Drop an existing principal
DROP PRINCIPAL [IF EXISTS] <principal_name>;

-- List or describe principals
SHOW PRINCIPALS [ LIKE 'pattern' ];
DESCRIBE PRINCIPAL <principal_name>;

2. Role Management

Polaris utilizes roles to decouple identities from direct resource privileges. We support both Principal Roles (assigned to users) and Catalog Roles (assigned to resources), as well as nesting them.

-- Create roles
CREATE [PRINCIPAL | CATALOG] ROLE [IF NOT EXISTS] <role_name>;

-- Drop roles
DROP ROLE [IF EXISTS] <role_name>;

-- Grant a Catalog Role to a Principal Role (Role Inheritance / Mapping)
GRANT ROLE <catalog_role_name> TO ROLE <principal_role_name>;
REVOKE ROLE <catalog_role_name> FROM ROLE <principal_role_name>;

-- List roles
SHOW ROLES [ LIKE 'pattern' ];

3. Privilege Management

Privileges define the "what" and "where". They grant catalog-level or table-level access to Catalog Roles.

-- Grant privileges on a securable resource to a Catalog Role
GRANT <privilege_type> [, <privilege_type> ...]
ON { CATALOG | NAMESPACE | TABLE | VIEW } <resource_identifier>
TO ROLE <catalog_role_name>;

-- Revoke privileges
REVOKE <privilege_type> [, <privilege_type> ...]
ON { CATALOG | NAMESPACE | TABLE | VIEW } <resource_identifier>
FROM ROLE <catalog_role_name>;

-- Show granted privileges
SHOW GRANTS ON ROLE <catalog_role_name>;
SHOW GRANTS ON { CATALOG | NAMESPACE | TABLE } <resource_identifier>;

Note: <privilege_type> includes Polaris-native privileges such as CATALOG_MANAGE_METADATA, NAMESPACE_READ, TABLE_WRITE, etc.

4. Policy & Assignment Management

Policies represent the binding layer. While basic RBAC uses simple grants, advanced deployments benefit from explicit policy objects to handle conditional access (e.g., time-bound access or row/column filters).

-- Standard Assignment: Bind a Principal Role to a Principal
GRANT ROLE <principal_role_name> TO PRINCIPAL <principal_name>;
REVOKE ROLE <principal_role_name> FROM PRINCIPAL <principal_name>;

-- Advanced Policy: Create a conditional policy rule
CREATE POLICY [IF NOT EXISTS] <policy_name>
FOR ROLE <principal_role_name>
TO PRINCIPAL <principal_name>
[ WITH ROW FILTER <filter_expression> ON <table_name> ]
[ WITH COLUMN MASKING ( <col_name> USING <masking_expression> ) ]
[ EFFECTIVE FROM '<start_timestamp>' TO '<end_timestamp>' ];

-- Drop a policy
DROP POLICY [IF EXISTS] <policy_name>;

-- Show policies
SHOW POLICIES [ ON TABLE <table_name> | FOR PRINCIPAL <principal_name> ];

---

💡 Practical Example Workflow

Here is how these custom SQL statements flow together in a real-world scenario where you want to grant an ETL service account read/write access to a specific namespace.

-- Step 1: Create the Principal (the service account)
CREATE PRINCIPAL etl_service_principal TYPE SERVICE 
WITH PROPERTIES ('client_id'='etl_01', 'department'='data_platform');

-- Step 2: Create the Principal Role and assign it to the Principal
CREATE PRINCIPAL ROLE etl_executor_role;
GRANT ROLE etl_executor_role TO PRINCIPAL etl_service_principal;

-- Step 3: Create the Catalog Role and grant resource privileges to it
CREATE CATALOG ROLE sales_rw_role;
GRANT NAMESPACE_READ, TABLE_WRITE ON NAMESPACE main_catalog.sales_db TO ROLE sales_rw_role;

-- Step 4: Link the Catalog Role to the Principal Role
GRANT ROLE sales_rw_role TO ROLE etl_executor_role;

-- Step 5: (Optional) Apply a time-bound Policy for strict governance
CREATE POLICY temporary_etl_access_policy
FOR ROLE etl_executor_role
TO PRINCIPAL etl_service_principal
EFFECTIVE FROM '2026-08-12 00:00:00' TO '2026-12-31 23:59:59';

[melin]

cc @flyrain

[flyrain]

Thanks @melin for raising this. I think it'd be useful. Could you convert the description to English for a wider audiences?