PostgreSQL - SPLessons

PostgreSQL Privilages

Chapter 22

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Privilages

PostgreSQL Privilages

shape Description

At the point when numerous clients can get to database objects, endorsement can be restrained to these items with benefits. Each article has a proprietor. PostgreSQL Privilages handles if a client can alter an article claimed by another client.

Granted and revoked are the PostgreSQL Privilages that the administrator provides to the users. A PostgreSQL Privilages are the authorities to assassinate a specific sort of SQL explanation that get to another client’s objects. A few sets of benefits incorporate the privilege to :

Types of PostgreSQL Privilages

shape Description

PostgreSQL Privilages can be classified into 2 types:

Grant

shape Description

GRANT proclamation characteristic the privileges that are accessed in the statement. These proclamations are utilized to accredit the privileges on the objects in the database.

shape Syntax

The syntax for Grant privilege is as follows:


GRANT Privilege[,…]
ON object[,…]
TO {PUBLIC|GROUP group |username}

Privileges => Are the access permissions.
Object => Accessing the grant permissions.
Public => Describing all the clients.
GROUP group => Grant privileges assigned on the set.
Username => The client for which applying all the grant privileges.

shape Examples

By viewing the below example, the concept of Grant privilege can be easily understand.

SQLDB=# create user Shah with password 'shah';
CREATE ROLE
SQLDB=# select * from Employee21;
 Emp_id | Emp_Name |   Dept    | Age | Salary
--------+----------+-----------+-----+--------
   1001 | John     | Manager   |  45 |  25000
   1004 | Mate     | Admin     |  50 |  41000
   1003 | Mike     | Database  |  43 |  40000
   1005 | Shah     | Sales     |  52 |  42000
   1006 | Maddie   | Marketing |  51 |  22000
(5 rows)


SQLDB=# GRANT ALL ON Employee21 TO Shah;
GRANT
SQLDB=# select * from employee21;
 Emp_id | Emp_Name |   Dept    | Age | Salary
--------+----------+-----------+-----+--------
   1001 | John     | Manager   |  45 |  25000
   1004 | Mate     | Admin     |  50 |  41000
   1003 | Mike     | Database  |  43 |  40000
   1005 | Shah     | Sales     |  52 |  42000
   1006 | Maddie   | Marketing |  51 |  22000
(5 rows)

Revoke

shape Description

Revoke command is utilized to dismiss all the privileges that have been assigned for a user in the database.

shape Syntax

The syntax for Revoke privilege is as follows:

REVOKE Privilege[,…]
ON object[,…]
TO {PUBLIC|GROUP group |username}

Privileges => Are the access permissions.
Object => Revoking all the grant permissions.
Public => Describing all the clients.
GROUP group => Revoking the grant privileges assigned on the set.
Username => The client for which applying all the grant privileges.

shape Examples

By viewing the below example, the concept of Revoke privilege can be easily understand.

SQLDB=# REVOKE ALL ON Employee21 From Shah;
REVOKE
SQLDB=# Drop user shah;
DROP ROLE
SQLDB=#

Revoke all the grant permissions assigned to shah and at the same time we can drop the user shah from the database.

Summary

shape Key Points

  • PostgreSQL Privileges – Privileges are the Access permissions assigned to the users in the database.
  • Grant – Grants privileges to the user.
  • Revoke – Revoke all the grant privileges to the user.