Revoking schema privileges in SQL

24 Sep 2023

Security

In SQL, granting privileges to users is an important task to control access permissions to various database objects. However, there may be situations where you need to revoke privileges for a specific user or role on a schema. In this blog post, we will explore how to revoke schema privileges in SQL.

Revoke Privileges Syntax

The syntax for revoking privileges on a schema in SQL varies slightly depending on the database management system (DBMS) you are using. Here, we will cover the general syntax that is widely supported:

REVOKE privilege_type [, privilege_type, ...]
    ON SCHEMA schema_name
    FROM { user_name | role_name | PUBLIC }
    [ CASCADE | RESTRICT ];

Let’s break down the components of this syntax:

REVOKE: The keyword used to revoke privileges.
privilege_type: The type of privilege to be revoked (e.g., SELECT, INSERT, UPDATE, DELETE, ALL).
ON SCHEMA schema_name: Specifies the schema from which the privilege should be revoked.
FROM { user_name | role_name | PUBLIC }: Specifies the user, role, or the keyword PUBLIC (representing all users) from which the privilege should be revoked.
CASCADE | RESTRICT (optional): Specifies the action to take if the privileges being revoked are dependent on other objects within the schema. CASCADE removes the objects, while RESTRICT raises an error if dependencies exist.

Revoking Schema Privileges Example

Let’s consider a scenario where we want to revoke the SELECT, INSERT, and UPDATE privileges for a user named john on a schema called sales.

REVOKE SELECT, INSERT, UPDATE
    ON SCHEMA sales
    FROM john;

In this example, we simply specify the privilege types (SELECT, INSERT, and UPDATE), followed by the ON SCHEMA clause to indicate the schema name (sales). Finally, we specify the user name (john) from which we want to revoke the privileges.

Conclusion

Controlling and managing privileges is an essential aspect of database security. With the REVOKE statement in SQL, you can easily revoke privileges on a schema, restricting access to specific users or roles. By understanding the syntax and using the appropriate privilege types and names, you can effectively manage and enforce access control in your SQL-based applications.

#SQL #Security