Revoking tablespace privileges in SQL

24 Sep 2023

In SQL, privileges can be granted to users on various objects, including tablespaces. Tablespace privileges determine a user’s ability to perform operations on a specific tablespace, such as creating or modifying tables. Sometimes, it may be necessary to revoke these privileges from a user. In this blog post, we will discuss how to revoke tablespace privileges in SQL.

Syntax

The syntax for revoking tablespace privileges in SQL is as follows:

REVOKE {privilege_type | ALL [PRIVILEGES]}
    ON TABLESPACE tablespace_name
    FROM {user | role | PUBLIC}
    [CASCADE CONSTRAINTS]
    [KEEP {TABLE | INDEX}]
    [COMMIT WRITE NOWAIT]

Let’s break down the components of this syntax:

REVOKE: This keyword is used to indicate that tablespace privileges are being revoked.
privilege_type | ALL [PRIVILEGES]: Specify the specific privilege type you want to revoke, such as CREATE TABLE. Alternatively, you can use ALL PRIVILEGES to revoke all privileges.
ON TABLESPACE tablespace_name: Specifies the tablespace for which you want to revoke privileges.
FROM {user | role | PUBLIC}: Specify the user, role, or PUBLIC to whom the privilege is granted.
[CASCADE CONSTRAINTS]: This optional parameter indicates whether to cascade the revoke operation to dependent objects.
[KEEP {TABLE | INDEX}]: This optional parameter determines whether to retain tables or indexes owned by the user when revoking privileges.
[COMMIT WRITE NOWAIT]: This optional parameter specifies the level of transactional behavior.

Examples

Revoking CREATE TABLE privilege from a user:
```
REVOKE CREATE TABLE
 ON TABLESPACE example_tablespace
 FROM example_user;
```
This statement revokes the CREATE TABLE privilege from the example_user on the example_tablespace.
Revoking all privileges from a role:
```
REVOKE ALL PRIVILEGES
 ON TABLESPACE example_tablespace
 FROM example_role;
```
This statement revokes all privileges from the example_role on the example_tablespace.
Revoking all privileges from the public:
```
REVOKE ALL PRIVILEGES
 ON TABLESPACE example_tablespace
 FROM PUBLIC;
```
This statement revokes all privileges from the PUBLIC on the example_tablespace.

Remember, revoking tablespace privileges is an essential SQL operation to effectively manage user access and security in a database environment.