# Logical View Security Modes

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.

The security mode doesn't matter when a view references only tables that are stored in the same database as the view, because access control is performed at the database level. Security modes become relevant when views reference tables across different databases.

## 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


```sql
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


Since access control is at the database level, to leverage DEFINER security mode effectively, the view should be defined in a different database from the database where the referenced tables are stored.

### DEFINER Mode with Nested Views

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


```sql
-- 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


```sql
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:


```sql
-- 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

A logical view with DEFINER security mode should not reference a logical view with INVOKER security mode. This combination may lead to unexpected permission behavior and is not recommended.

## Comparison Table

| Aspect | DEFINER Mode | INVOKER Mode |
|  --- | --- | --- |
| **Permissions Used** | View owner's permissions | Query executor's permissions |
| **Use Case** | Controlled access to sensitive data | User-level permission enforcement |
| **Permission Setup** | Grant permissions to view owner only | Grant permissions to all users |
| **Security Benefit** | Restrict data access to specific columns/rows | Enforce existing permission model |
| **Default** | Yes | No |


## Specifying Security Mode

### When Creating a View

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


```sql
-- 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


![Edit Logical View Details dialog](/assets/logical-view-edit-details-dialog.e55c3c71ef4577f32e40badcd4c2eb9904ce516dcade6a1ff89fe06a9b57e635.4c190181.png)

**Using SQL:**

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


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

When using CREATE OR REPLACE VIEW, you must re-specify the complete query definition.

## 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](/products/customer-data-platform/data-workbench/databases/logical-views) - Complete guide to creating, viewing, and managing logical views