Permissions
Tags: opengauss
Published: 2023-03-09 04:20:52

A user who creates an object is the owner of this object. By default, separation of duties is disabled after database installation. A database system administrator has the same permissions as object owners. After an object is created, only the object owner or system administrator can query, modify, and delete the object, and grant permissions on the object to other users through GRANT by default.

To enable another user to use the object, grant required permissions to the user or the role that contains the user.

To remove permissions, use REVOKE. Object owners have implicit permissions (such as ALTER, DROP, COMMENT, INDEX, VACUUM, GRANT, and REVOKE) on objects. That is, once becoming the owner of an object, the owner is immediately granted the implicit permissions on the object. Object owners can remove their own common permissions, for example, making tables read-only to themselves or others, except the system administrator.

openGauss supports the following permissions, which are associated with different object types:

**·** **SELECT**: allows **SELECT** on a specified table, view, or sequence. The **SELECT** permission on the corresponding column is also required for **UPDATE** or **DELETE**.

**·** **INSERT**: allows **INSERT** on a specified table.

**·** **UPDATE**: allows **UPDATE** on any column of a specified table. Generally, **UPDATE** also requires the **SELECT** permission to query which rows need to be updated. **SELECT … FOR UPDATE** and **SELECT … FOR SHARE** also require this permission on at least one column, in addition to the **SELECT** permission.

**· DELETE**: allows **DELETE** on a row of a specified table. Generally, **DELETE** also requires the **SELECT** permission to query which rows need to be deleted.

**· TRUNCATE**: allows **TRUNCATE** on a specified table.

**· REFERENCES**: allows creation of a foreign key constraint. This permission is required on both referencing and referenced tables.

**· CREATE**:

**-** For databases, allows new schemas to be created within the database.
**-** For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have the **CREATE** permission on the schema of the object.
**-** For tablespaces, allows tables to be created within the tablespace, and allows databases and schemas to be created that have the tablespace as their default tablespace.

**· CONNECT**: allows you to access specified databases.

**· EXECUTE**: allows you to use specified functions and the operators that are realized by the functions.

**· USAGE**:
**-** For procedural languages, allows use of the language for the creation of functions in that language.
**-** For schemas, allows access to objects contained in the schema. Without this permission, it is still possible to see the object names.
**-** For sequences, allows use of the **nextval** function.
**-** For data sources, specifies access permissions or is used as **ALL PRIVILEGES**.

**· ALTER**: allows users to modify the attributes of a specified object, excluding the owner and schema of the object.

**· DROP**: allows users to delete a specified object.

**· COMMENT**: allows users to define or modify comments of a specified object.

**· INDEX**: allows users to create indexes on specified tables, manage indexes on the tables, and perform REINDEX and CLUSTER operations on the tables.

**· VACUUM**: allows users to perform ANALYZE and VACUUM operations on specified tables.

**· ALL PRIVILEGES**: grants all available permissions to specified users or roles at a time. Only a system administrator has the **GRANT ALL PRIVILEGES** permission.

**Syntax**
**·** GRANT

**GRANT** grants permissions to roles and users. **GRANT** is used in the following scenarios:

**-** Granting system permissions (such as **sysadmin, CREATEDB**, and **CREATEROLE**) to a role or user

The syntax for granting sysadmin permissions to a specified role is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfi9ad091y1nrip1kd2t.png)

**-** Granting the permissions of a role or user to others

The syntax for granting the permissions of a role to other others is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x9n0gc4lblp9u1383iuv.png)

**-** Granting database object permissions to a role or user

 **-** Grant the table or view access permission to a user or role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ilj0fxjejggrx6ktv58o.png)

 **-** Grant the column access permission to a user or role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nev81qiltp4a1idrwh62.png)

 **-** Grant the database access permission to a user or role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1arbzr7r111stalvqgxp.png)

 **-** Grant the schema access permission to a user or role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qtquf9xtfbe62g881c8h.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/idj3ycvahtoqu2la6pty.png)

 **-** Grant the tablespace access permission to a user or role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vsz496muy9ud7bvxu60x.png)

**· REVOKE**

**-** Revoking system permissions (such as **sysadmin, CREATEDB**, and **CREATEROLE**) from a role or user

Revoke the sysadmin permission from a role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3fge8vubkt36p3nhyh00.png)

**-** Revoke the permissions of a role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q3t3034ayval2l632tqm.png)

**-** Revoking permissions on database objects

 **-** Revoke the permission on a specified table or view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t2l0prj9j3ecm497q8ay.png)

 **-** Revoke the permission on a specified column in a table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3l26yujxs7fbb30dgcpd.png)

 **-** Revoke the permission on a specified database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hs0qcrrbi8tpdveyjf94.png)

 **-** Revoke the permission on a specified schema.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i0k4jgg4nbccdn3av5yq.png)

 **-** Revoke the permission on a specified tablespace.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k97qutpvubg8x8sml487.png)

**Parameter Description**
**· role_name**

Specifies the username.

**· table_name**

Specifies the table name.

**· column_name**

Specifies the column name.

**· schema_name**

Specifies the schema name.

**· database_name**

Specifies the database name.

**· tablespace_name**

Specifies the tablespace name.

**· WITH GRANT OPTION**

If **WITH GRANT OPTION** is specified, the recipient of the permission can in turn grant it to others. Without a grant option, the recipient cannot do that. This option cannot be granted to **PUBLIC**.

**Examples**
**·** Grant system permissions to a user or role.

Create the **joe** user and grant the **sysadmin** permission to it.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5n1fuubad7kps221gklx.png)

Then the joe user has the sysadmin permission.

**·** Grant object permissions to a user or role.

1. Create the **test** schema and the **customer** table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qi0qfshguu3wmxim6hua.png)

2. Revoke user joe's sysadmin permission, and grant it with the usage permission on the test schema and all the permissions on the customer table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4m5h4bmuq3nmij8lnycr.png)

After the permissions are successfully granted, user **joe** has all the permissions on the **customer** table, including addition, deletion, modification, and query permissions.

3. Grant the query permission on the c_customer_sk, c_customer_id, and c_first_name columns and the update permission on the c_last_name column in the customer table to user **joe**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4bvd1suazia6gwhc5uo6.png)

After the permissions are successfully granted, user joe immediately has the query permission on the c_customer_sk, c_customer_id, c_first_name columns in the customer table. To enable user joe to grant these permissions to other users, execute the following statement:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lwfnr9le6nkeo44i3kor.png)

Grant the postgres database connection permission and schema creation permission to user joe and enable this user to grant these permissions to other users.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hmoldctyg2e01st6ulqn.png)

Create the test_manager role, grant the access and object creation permissions on the test schema to the test_manager role, but do not allow this role to grant these permissions to others.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iyld6wesbovnnxrd67ni.png)

Create the tpcds_tbspc tablespace and grant all permissions on the tablespace to user joe. However, user joe cannot grant these permissions to other users.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7tsqdhut56mk1duyebhp.png)

**·** Grant a user's or role's permissions to other users or roles

1. Create the manager role, grant joe's permissions to the manager role, and allow the manager role to grant these permissions to others.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5bo8m206h8l9ltcqik4h.png)

2. Create the senior_manager user and grant manager's permissions to it.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/40fodwqs3gh8oglics3q.png)