GRANT and REVOKE in Greenplum

In Greenplum, which is based on PostgreSQL, you use the same GRANT and REVOKE statements to manage privileges and permissions on database objects.

 GRANT:

The GRANT statement is used to give specific privileges to users or roles.

Syntax:

GRANT { { SELECT | INSERT | UPDATE | DELETE | ... }

       [, ...] | ALL [ PRIVILEGES ] }

    ON { TABLE table_name | ALL TABLES IN SCHEMA schema_name }

    TO { [ GROUP ] role_name | PUBLIC }

    [ WITH GRANT OPTION ]

Example:

GRANT SELECT, INSERT ON employees TO user1;

This grants the SELECT and INSERT privileges on the employees table to the user user1.

GRANT SELECT ON employees TO HR WITH GRANT OPTION;

This grants the SELECT privilege on the employees table to the HR role with the option to further grant this privilege to other users.

 REVOKE:

The REVOKE statement is used to take back privileges that have been granted from users or roles.

Syntax:

REVOKE { { SELECT | INSERT | UPDATE | DELETE | ... }

       [, ...] | ALL [ PRIVILEGES ] }

    ON { TABLE table_name | ALL TABLES IN SCHEMA schema_name }

    FROM { [ GROUP ] role_name | PUBLIC }

    [ CASCADE | RESTRICT ]

Example:

REVOKE SELECT, INSERT ON employees FROM user1;

This revokes the SELECT and INSERT privileges on the employees table from the user user1.

REVOKE SELECT ON employees FROM HR;

This revokes the SELECT privilege on the employees table from the HR role.

As with PostgreSQL, ensure to use these statements judiciously to maintain the security and integrity of your Greenplum database.