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 :
- Interface with the database
- Constitutes a database or a table
- Table can be altered
- Preferred lines from another client's table
- Performs stored method of another client.
Description
PostgreSQL Privilages can be classified into 2 types:
- Grant
- Revoke
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.
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.
Examples
By viewing the below example, the concept of Grant privilege can be easily understand.
[c]
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)[/c]
Description
Revoke command is utilized to dismiss all the privileges that have been assigned for a user in the database.
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.
Examples
By viewing the below example, the concept of Revoke privilege can be easily understand.
[c]
SQLDB=# REVOKE ALL ON Employee21 From Shah;
REVOKE
SQLDB=# Drop user shah;
DROP ROLE
SQLDB=#
[/c]
Revoke all the grant permissions assigned to shah and at the same time we can drop the user shah from the database.
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.