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

# GRANT Table Permission

[Table permissions](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-permissions-table), which can
be applied to tables and views, can be granted directly to
[users](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-users) or
[roles](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-roles).

[Row-level security](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-row) can be invoked for
`SELECT` privilege by specifying a `WHERE` clause expression.

[Column-level security](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-col) can be invoked for
`SELECT` privilege by specifying a list of accessible columns and/or
[column security functions](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-col-func).

```sql title="GRANT Table Permission Syntax" theme={null}
GRANT < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON <ALL TABLES [IN [SCHEMA] <schema reference>] | [TABLE | SCHEMA] <object reference> [<column list>]>
TO <user name | role name>
[WHERE <expression>]
[WITH GRANT OPTION]
```

```sql title="GRANT Create Table Permission Syntax" theme={null}
GRANT CREATE [PRIVILEGES] ON TABLES
[IN [SCHEMA] <schema reference>]
TO <user name | role name> [WITH GRANT OPTION]
```

## Parameters

> [accordion] PRIVILEGES
>
> Optional keyword for SQL-92 compatibility.

> [accordion] TABLE / SCHEMA
>
> Optional SQL-92 keywords that disambiguate the object reference: `TABLE` selects a
> table/view in the user's default schema (when the schema is omitted), while `SCHEMA`
> selects a schema.

> [accordion] \<object reference>
>
> The name of the table/view and/or schema to which access is being granted.
> the following forms are valid:
>
> <table>
>   <thead>
>     <tr>
>       <th>Form</th>
>       <th>Selected Objects</th>
>     </tr>
>   </thead>
>
>   <tbody>
>     <tr>
>       <td>*\<schema>.\<object>*</td>
>       <td>Fully-qualified table or view name in the specified schema</td>
>     </tr>
>
>     <tr>
>       <td>`.`</td>
>       <td>All existing tables & views that the granting user can see, across all schemas</td>
>     </tr>
>
>     <tr>
>       <td>`*`</td>
>       <td>All existing schemas that the granting user can see, which will grant access to all tables and views in those schemas now and in the future</td>
>     </tr>
>
>     <tr>
>       <td>*\<schema>*</td>
>       <td>All tables and views in the specified schema now and in the future</td>
>     </tr>
>
>     <tr>
>       <td>*\<object>*</td>
>       <td>Table or view in the default schema.  If a schema exists with the same name, it will be used.  To avoid this, specify the fully-qualified object name.</td>
>     </tr>
>   </tbody>
> </table>

> [accordion] \<schema reference>
>
> The schema the permission applies to (with the ALL or CREATE forms).  If the IN \[SCHEMA]
> clause is omitted, the permission applies across all schemas, both now and in the future.
> Wildcards are not permitted with the IN keyword.

> [accordion] \<column list>
>
> Optional comma-separated list of specific table columns and/or
> [column security functions](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-col-func) applied to columns
> on which to grant access; see [Column-Level Security](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-col) for details.

> [accordion] WHERE \<expression>
>
> Optional filter expression on the table to which access is being granted, determining to
> which rows of the target table the grantee will be given access; see
> [Row-Level Security](https://docs.kinetica.com/content/security/sec_concepts.md#sec-concepts-perm-table-row) for details.

> [accordion] WITH GRANT OPTION
>
> Grant this permission with a
> [grant option](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-permissions-grant-option) to grant it to others.
>
> > [!NOTE]
> >
> > `WITH GRANT OPTION` is not allowed when also granting with row or column
> > restrictions.

> [!NOTE]
>
> The `ALL` permission corresponds to the native
> [table\_admin](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-permissions-table) permission, which
> gives full read/write access as well as the additional permission either to
> `ALTER` and `DROP` the specified table.

## Examples

For example, to grant full access on the **network\_config** table to **jsmith**:

```sql GRANT All Permissions on Table to User Example theme={null}
GRANT ALL PRIVILEGES ON TABLE example.network_config TO jsmith
```

To grant `SELECT` access on the **network\_config\_history** table to the
**analyst** role:

```sql GRANT Read on Table to Role Example theme={null}
GRANT SELECT ON example.network_config_history TO analyst
```

To grant full access on each of the tables & views within the
**example** schema to the **developer** role:

```sql GRANT All Permissions on Current Tables/Views in Schema to Role Example theme={null}
GRANT ALL ON example.* TO developer
```

To grant `SELECT` access on all schemas to the **auditor** role:

```sql GRANT Read on Schemas to Role Example theme={null}
GRANT SELECT ON * TO auditor
```

To grant `SELECT` access on all current and future tables & views within the
**app\_schema** schema to the **app\_user** role:

```sql GRANT Read on Schema to Role Example theme={null}
GRANT SELECT ON app_schema TO app_user
```

To grant full access on each of the schemas, tables, & views in the database
individually to the **dbadmin** role:

```sql GRANT All Permissions on All Schemas/Tables/Views to Role Example theme={null}
GRANT ALL ON *.* TO dbadmin
```

## Row-Level Security Examples

To grant `SELECT` access on an **rx\_order** table for all orders placed since
the year **2002**:

```sql GRANT Row-Level Access by Filter Example theme={null}
GRANT SELECT ON example.rx_order
	TO zanalyst
	WHERE order_ts >= '2002-01-01'
```

To grant `SELECT` access on an **rx\_order** table for only orders belonging
to the current user:

```sql GRANT Row-Level Access Tied to Current User Example theme={null}
GRANT SELECT ON example.rx_order
	TO rx_user
	WHERE name = USER()
```

## Column-Level Security Examples

To grant `SELECT` access on the following columns of an **rx\_order** table:

* obfuscated version of the orderer's social security number
* name of the prescription ordered
* order date/time

```sql GRANT Obfuscated Column-Level Access Example theme={null}
GRANT SELECT ON example.rx_order(HASH(ssn), rx_name, order_ts) TO zanalyst
```

To grant `SELECT` access on the following columns of an **rx\_order** table:

* masked version of the orderer's social security number (last 4 digits)
* name of the orderer
* name of the prescription ordered
* order date/time

```sql GRANT Masked Column-Level Access Example theme={null}
GRANT SELECT ON example.rx_order(MASK(ssn, 1, 6), name, rx_name, order_ts) TO rx_user
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.