> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Learn how to use column-level security (CLS) in Firebolt to restrict which columns of a table a role can read using GRANT and REVOKE with column lists.

# Column-level security

Column-level security (CLS) lets you restrict which columns of a table a given role can read. You grant or revoke `SELECT` on specific columns using PostgreSQL-style syntax:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
GRANT SELECT (col1, col2) ON TABLE t TO analyst_role;
```

A user who has not been granted access to a column cannot read it in queries. `SELECT *` expands only to their permitted columns, explicit references to unpermitted columns are rejected, and catalog views like [`information_schema.columns`](/reference-sql/information-schema/columns) hide those columns from view.

## How column-level security works

CLS extends Firebolt's existing [Role-Based Access Control (RBAC)](/security/rbac) model. A table-level `SELECT` grant (no column list) continues to mean "access to all columns." Column-level grants add a finer level of control within a table.

Key rules:

* **Table-level SELECT grants all columns.** If a role has `GRANT SELECT ON TABLE t`, it can read every column regardless of any column-scoped grants. To restrict the role to specific columns, you must first `REVOKE SELECT ON TABLE` and then grant column-level access.
* **Column grants are additive.** Granting `(col_a)` and then `(col_b)` is equivalent to granting `(col_a, col_b)` — the sets are merged.
* **Union across roles.** A user sees the union of columns granted across all their roles. If `role_a` grants `SELECT (col1)` and `role_b` grants `SELECT (col2)`, a user with both roles can read both columns.
* **Column identity is stable.** Privileges are stored using internal column IDs, not column names. Renaming a column does not affect access. Dropping and re-adding a column with the same name creates a new ID, so old grants do not apply to the new column.
* **Unauthorized columns are invisible.** Referencing an unauthorized column produces the same error as referencing a column that does not exist.

## Prerequisites

Before configuring column-level security, make sure:

* You have a user with `GRANT` privileges (typically an `account_admin` or a role with the relevant permissions).
* You understand [RBAC basics](/security/rbac) and [table permissions](/security/rbac/database-permissions/table-permissions).
* The target database and schema exist, and you have `USAGE` on both.

## Grant SELECT on specific columns

Use [`GRANT`](/reference-sql/commands/access-control/grant) with a column list to restrict a role to specific columns:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
GRANT SELECT (col1, col2) ON TABLE <table_name> TO <role_name>;
```

<Warning>
  If the role already has a table-level `SELECT` grant (that is, `GRANT SELECT ON TABLE` without a column list), it can read **all** columns regardless of any column-scoped grants. You must first revoke the table-level grant before column-level restrictions take effect:

  ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
  REVOKE SELECT ON TABLE <table_name> FROM <role_name>;
  GRANT SELECT (col1, col2) ON TABLE <table_name> TO <role_name>;
  ```
</Warning>

Column-list syntax is only valid for `SELECT` on `TABLE` objects. You cannot use column lists with other privileges (such as `INSERT` or `DELETE`) or on views.

### Example

Suppose you have an `employees` table with sensitive salary and SSN data:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE TABLE employees (
  employee_id INT,
  name TEXT,
  department TEXT,
  salary NUMERIC(10,2),
  ssn TEXT
);
```

Grant an `analyst_role` access to only the non-sensitive columns:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- Grant database and schema access
GRANT USAGE ON DATABASE my_db TO analyst_role;
GRANT USAGE ON SCHEMA public TO analyst_role;

-- Grant SELECT on specific columns only
GRANT SELECT (employee_id, name, department) ON TABLE employees TO analyst_role;
```

Verify access:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- As a user with analyst_role:
SELECT * FROM employees;
-- Returns only employee_id, name, department

SELECT salary FROM employees;
-- Error: column "salary" not found
```

## Revoke SELECT on specific columns

Use [`REVOKE`](/reference-sql/commands/access-control/revoke) with a column list to remove access to specific columns:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
REVOKE SELECT (col1, col2) ON TABLE <table_name> FROM <role_name>;
```

Column-list `REVOKE` subtracts from the column-scoped grant only. It does not affect table-level `SELECT` grants.

### Example

Remove access to the `department` column:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
REVOKE SELECT (department) ON TABLE employees FROM analyst_role;
```

The role retains access to `employee_id` and `name`.

## Interaction with table-level grants

Column-level and table-level `SELECT` grants follow an OR rule, consistent with PostgreSQL:

| Role state                                  | Effect                                          |
| ------------------------------------------- | ----------------------------------------------- |
| Table-level `SELECT` (no column list)       | Role can read all columns.                      |
| Column-scoped `SELECT` for columns `{A, B}` | Role can read columns A and B only.             |
| Both table-level and column-scoped grants   | Same as table-level: role can read all columns. |
| No `SELECT` at all                          | Access denied at the table level.               |

### Moving from full table access to column-scoped access

You cannot "punch holes" in table-level access with a column-list `REVOKE`. To restrict a role from full table access to a subset of columns:

1. Revoke the table-level `SELECT`:
   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   REVOKE SELECT ON TABLE employees FROM analyst_role;
   ```
2. Grant access to only the desired columns:
   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   GRANT SELECT (employee_id, name, department) ON TABLE employees TO analyst_role;
   ```

### Table-level REVOKE removes column-scoped grants

`REVOKE SELECT ON TABLE` (without a column list) removes **all** `SELECT` on that table for the role, including any column-scoped grants. This matches PostgreSQL behavior.

## Schema evolution

Column-level privileges use stable column IDs. When the table schema changes:

* **`ADD COLUMN`**: The new column is **not** included in any existing column-scoped grant. Roles with column-scoped `SELECT` cannot read the new column until an administrator explicitly grants it.
* **`DROP` and re-add column**: Dropping and re-adding a column with the same name creates a new internal column ID. Previous grants do not apply to the new column.
* **`RENAME COLUMN`**: Renaming a column does not affect access. The grant follows the column's stable internal ID, not its name.

## STRUCT columns

CLS operates at the top-level column granularity. A `STRUCT` column is granted or denied as a whole. Individual sub-fields cannot be independently granted or revoked.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- Table: t(id INT, info STRUCT(name TEXT, score INT), secret INT)
-- Role granted SELECT (id, info):

SELECT * FROM t;          -- Returns id and info; secret is hidden
SELECT info.name FROM t;  -- Succeeds; sub-field access works
SELECT secret FROM t;     -- Error: column not found
```

## Catalog visibility

| View                                   | Behavior under CLS                                                                                                                                      |
| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `information_schema.columns`           | Shows only columns the current user is permitted to read.                                                                                               |
| `information_schema.object_privileges` | Shows the `SELECT` privilege for the table without exposing the column list. Both column-scoped and table-level grants appear as `SELECT` on the table. |

## Views and column-level security

When a query references a view, column-level access on the underlying table is evaluated using the **view owner's** privileges, not the querying user's. This is the same [owner rights](/security/guides/rbac-views-security#how-owner-rights-work-in-views) model that Firebolt applies to all views.

For example, if a view owner has `SELECT (a, b)` on a table and a view is defined as `SELECT a, b FROM t`, any user with `SELECT` on the view can see both columns through the view, even if their own direct table grants are narrower.

<Tip>
  For scenarios that require **data masking** (exposing columns in a transformed or redacted form) or **row-level security** (filtering which rows a user can see), use [secure views](/security/guides/rbac-views-security). Column-level security and secure views complement each other as part of Firebolt's layered security model.
</Tip>

## Example: multi-role column access

The following example sets up two roles with different column access on the same table, then shows the effective access for a user with both roles:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE TABLE orders (
  order_id INT,
  customer_name TEXT,
  amount NUMERIC(10,2),
  internal_notes TEXT
);

-- Role for sales: can see order details but not internal notes
CREATE ROLE sales_role;
GRANT USAGE ON DATABASE my_db TO sales_role;
GRANT USAGE ON SCHEMA public TO sales_role;
GRANT SELECT (order_id, customer_name, amount) ON TABLE orders TO sales_role;

-- Role for support: can see order and notes but not amount
CREATE ROLE support_role;
GRANT USAGE ON DATABASE my_db TO support_role;
GRANT USAGE ON SCHEMA public TO support_role;
GRANT SELECT (order_id, customer_name, internal_notes) ON TABLE orders TO support_role;

-- User with both roles sees the union: all four columns
GRANT ROLE sales_role TO USER alice;
GRANT ROLE support_role TO USER alice;
```

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- As alice:
SELECT * FROM orders;
-- Returns order_id, customer_name, amount, internal_notes
```

## Related topics

* [GRANT](/reference-sql/commands/access-control/grant) -- Syntax reference for granting privileges
* [REVOKE](/reference-sql/commands/access-control/revoke) -- Syntax reference for revoking privileges
* [Table permissions](/security/rbac/database-permissions/table-permissions) -- Table-level privilege reference
* [Using secure views](/security/guides/rbac-views-security) -- View-based security for data masking and row-level security
* [Role-Based Access Control](/security/rbac) -- Overview of Firebolt's RBAC model
* [information\_schema.columns](/reference-sql/information-schema/columns) -- Column metadata filtered by access