Logical View Security Modes

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Connect to Cursor

  Install MCP server on Cursor
• Connect to VS Code

  Install MCP server on VS Code

Logical views support two security modes that control how permissions are evaluated when accessing tables referenced by the view: DEFINER and INVOKER.

Overview

The security mode determines whose permissions are used when the view accesses underlying tables:

• DEFINER mode: The view executes with the permissions of the view owner
• INVOKER mode: The view executes with the permissions of the query executor

The default security mode is DEFINER, which follows the standard convention used by most database systems.

DEFINER Mode (Default)

With DEFINER mode, queries against the view are executed using the permissions of the user who created (owns) the view, not the user executing the query.

When to Use DEFINER Mode

Use DEFINER mode when you want to:

• Provide controlled access to sensitive data: Allow users to query specific columns or filtered rows without granting them direct access to the underlying tables
• Simplify permission management: Grant permissions once to the view owner instead of to every user who needs to query the view
• Create abstraction layers: Hide complex security logic behind a simple view interface

Example

CREATE VIEW filtered_data.customers
SECURITY DEFINER
AS
SELECT customer_id, customer_name, country
FROM source_data.customers
WHERE country IN ('US', 'CA', 'MX');

In this example:

• The view is created in the filtered_data database, while the referenced table is in the source_data database
• The view owner has permission to access the source_data.customers table
• Users who query filtered_data.customers do not need direct permission to the source_data database or source_data.customers table
• All users see only customers from US, CA, or MX, regardless of their own permissions
• The view executes with the owner's permissions, which include access to the full source_data.customers table

DEFINER Mode with Nested Views

When views reference other views, each view's security mode is evaluated independently:

-- view_a references table_c
CREATE VIEW view_a
SECURITY DEFINER
AS SELECT * FROM table_c;

-- view_b references view_a
CREATE VIEW view_b
SECURITY DEFINER
AS SELECT * FROM view_a;

When a query executes SELECT * FROM view_b:

1. view_b is accessed with the permissions of query executor
2. view_a is accessed with the permissions of view_a's owner (DEFINER mode)
3. table_c is accessed with the permissions of view_a's owner (DEFINER mode)

INVOKER Mode

With INVOKER mode, queries against the view are executed using the permissions of the user who is running the query, not the view owner.

When to Use INVOKER Mode

Use INVOKER mode when you want to:

• Enforce user-level permissions: Ensure each user sees only data they're authorized to access based on their own permissions
• Simplify query logic without changing security: Provide reusable query templates while maintaining existing permission models
• Audit who accessed what: Track access based on the actual query executor, not the view owner

Example

CREATE VIEW analytics.all_customer_orders
SECURITY INVOKER
AS
SELECT o.order_id, o.customer_id, o.amount, c.customer_name
FROM source_data.orders o
JOIN source_data.customers c ON o.customer_id = c.customer_id;

In this example:

• The view is created in the analytics database, while the referenced tables are in the source_data database
• Users must have permission to access both the source_data.orders and source_data.customers tables
• Each user sees only the data they're authorized to see based on their own permissions
• If a user lacks permission to the source_data.customers table, the query will fail

INVOKER Mode with Nested Views

When views reference other views with INVOKER mode:

-- view_a references table_c
CREATE VIEW view_a
SECURITY INVOKER
AS SELECT * FROM table_c;

-- view_b references view_a
CREATE VIEW view_b
SECURITY INVOKER
AS SELECT * FROM view_a;

When a query executes SELECT * FROM view_b:

1. view_b is accessed with the permissions of the query executor (INVOKER mode)
2. view_a is accessed with the permissions of the query executor (INVOKER mode)
3. table_c is accessed with the permissions of the query executor (INVOKER mode)

Nested View Limitations

Comparison Table

Specifying Security Mode

When Creating a View

When creating a view, specify the security mode in the CREATE VIEW statement:

-- DEFINER mode (default)
CREATE VIEW view_name
SECURITY DEFINER
AS
SELECT * FROM table_name;

-- INVOKER mode
CREATE VIEW view_name
SECURITY INVOKER
AS
SELECT * FROM table_name;

If you don't specify a security mode, DEFINER is used by default.

Changing Security Mode

You can change the security mode through the Data Workbench UI or using SQL.

Using the UI:

1. Navigate to the view detail page in Data Workbench
2. Select the menu (⋯) and choose Edit Details
3. In the Edit Logical View Details dialog, select:
   • Definer: Uses the permissions of the view creator
   • Invoker: Uses the permissions of the current user
4. Select Confirm to apply the change

Using SQL:

To change the security mode via SQL, use CREATE OR REPLACE VIEW:

-- Change to INVOKER mode
CREATE OR REPLACE VIEW view_name
SECURITY INVOKER
AS
SELECT * FROM table_name;

Important Considerations

View Ownership Matters for DEFINER Mode

• The view owner's permissions are critical when using DEFINER mode
• If the view owner is deleted, views with DEFINER mode will stop working
• Before deleting a user who owns DEFINER views, delegate ownership to another user with appropriate permissions

Column-Level Security Limitations

• Columns of logical views cannot be annotated with tags for data classification
• If a user has a column permission with colulmn permission (cannot view non-tagged columns), they cannot use logical views in queries
• This limitation applies regardless of security mode

Next Steps

• Logical Views - Complete guide to creating, viewing, and managing logical views