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

# Privileges

## Overview

Oxla supports `GRANT` and `REVOKE` to manage privileges on database objects. Privileges can be assigned to a role for tables, schemas and database.

## Available Privileges

In the table below, you can read about all available privileges:

| **Privilege** | **Description**                                                                                                                                    | **Database Objects** |
| ------------- | :------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------- |
| **CONNECT**   | Allows a role to connect to the database                                                                                                           | database             |
| **USAGE**     | Allows access to objects within the schema, provided the objects' own privilege requirements are also met                                          | schema               |
| **CREATE**    | Allows creating tables and types in a schema. `CREATE INDEX` requires ownership of the table and both `CREATE` and `USAGE` privilege on the schema | schema               |
| **SELECT**    | Allows selecting data from a table                                                                                                                 | table                |
| **INSERT**    | Allows inserting data into a table                                                                                                                 | table                |
| **UPDATE**    | Allows updating data in a table                                                                                                                    | table                |
| **DELETE**    | Allows deleting and truncating data from a table Allows                                                                                            | table                |

**USAGE Privilege Essentials**

`USAGE` privilege is required to see what objects (tables / types / indices) exist inside the schema. Unless the user has such a privilege, they will get `does not exist` error on accessing any objects in the schema, even if the object exists. It is also required to look up tables using `search_path`. In case of objects listed by tables in `information_schema.tables` and `pg_catalog.pg_class` schemas but also other metatables user can see these objects when they either have `USAGE` on schema or any grant on the object itself.

The only exception to this rule is `information_schema.role_table_grants` table - if a user has any grant on a table, but does not have `USAGE` grant to this table, on parent schema, this grant will still be visible in the `information_schema.role_table_grants` table.

<Info>There is an `ALL PRIVILEGES` alias representing all available privileges for a given database object.
By default, every new role has `CONNECT` privilege on the database and `USAGE` privilege on the default `public` schema.
See the [Default Privileges](#default-privileges) section for details.</Info>

**Schemas and Tables**

* Only superuser can read `pg_authid` and `pg_shadow` tables from `pg_catalog`and can access resources in `oxla_internal`
* Anyone can read other resources in: `pg_catalog`, `information_schema`, `system`
* No one can modify: `pg_catalog`, `information_schema`, `system`, `oxla_internal`

## Checking Privileges

To check privileges on tables, database or schemas, a superuser needs to execute the following commands:

* **Table privileges**

  ```sql theme={null}
  SELECT * FROM oxla_internal.oxla_role_table_grants;
  ```

  Example output:

  ```text theme={null}
  id | grantor | database | schema |      table      | privilege 
  ----+---------+----------+--------+-----------------+-----------
    1 |       1 | oxla     | public | data_types_demo | INSERT
    1 |       1 | oxla     | public | data_types_demo | DELETE
  ```

* **Database privileges**

  ```sql theme={null}
  SELECT * FROM oxla_internal.oxla_role_db_grants;
  ```

  Example output:

  ```text theme={null}
  id | grantor | database | privilege 
  ----+---------+----------+-----------
    1 |       1 | oxla     | CONNECT
  ```

* **Schema privileges**

  ```sql theme={null}
  SELECT * FROM oxla_internal.oxla_role_ns_grants;
  ```

  Example output:

  ```text theme={null}
  id  | grantor | database | schema | privilege 
  ----+---------+----------+--------+-----------
    1 |       1 | oxla     | public | USAGE
  ```

Here's the breakdown of the above outputs:

* `id`: role ID
* `database`: database name
* `schema`: schema name
* `table`: table name
* `privilege`: privilege granted on a given object

## Granting Privilege

To grant privileges, execute the following command:

```sql theme={null}
GRANT privilege [, privilege] ON [ TABLE | SCHEMA | DATABASE ] object_name TO role_name;
```

where:

* `privilege`: one or more privileges from the available list
* `object_name`: name of the object on which the privileges are being granted
* `role_name`: name of the role to which the privileges are being granted

<Note>If `TABLE`, `SCHEMA` or `DATABASE` keyword is omitted, `TABLE` keyword is assumed.</Note>

## Revoking Privileges

Here's the code that needs to be run, in order to revoke a privilege:

```sql theme={null}
REVOKE privilege [, privilege] ON [ TABLE | SCHEMA | DATABASE ] object_name FROM role_name;
```

where:

* `privilege`: one or more privileges from the list of available privileges
* `object_name`: name of the object on which the privileges are being revoked
* `role_name`: name of the role from which the privileges are being revoked, or keyword CURRENT\_ROLE/CURRENT\_USER

<Note>If `TABLE`, `SCHEMA` or `DATABASE` keyword is omitted, `TABLE` keyword is assumed.</Note>

## Default Privileges

By default, every created role has the following privileges:

* `CONNECT`: privilege on the default database
* `USAGE`: privilege on the default `public` schema

Default privileges can be revoked at any time with the following query:

```sql theme={null}
REVOKE CONNECT ON DATABASE oxla FROM role_name;
REVOKE USAGE ON SCHEMA public FROM role_name;
```

* A user without the `CONNECT` privilege cannot connect to the database
* A user without the `USAGE` privilege cannot access the default `public` schema