In this sub section, let us learn the usage of below commands in detail.

1. GRANT

GRANT statement is used to provide access privileges to users to access the database.

Syntax:

GRANT privileges ON object TO user;

Note: Privileges can be SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, ALL. You can also specify combination of these privileges in a statement.

GRANT Connect to Database

GRANT CONNECT ON DATABASE database_name TO username;

GRANT Usage on Schema

GRANT USAGE ON SCHEMA database_name TO username;

Grant access to all tables in the database:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Grant permission to create database:

ALTER USER username CREATEDB;

Grant superuser access to a user

ALTER USER myuser WITH SUPERUSER;

2. REVOKE

REVOKE statement is used to withdraw the access priviliges given to a user by GRANT statement.

Syntax:

REVOKE privileges ON object FROM user;

Example:

 REVOKE DELETE, UPDATE ON ORDERS FROM customer1;