At whatever point an article is made in an information base, a proprietor is relegated to it. The proprietor is generally the person who executed the creation explanation. For most sorts of articles, the underlying state is that solitary the proprietor (or a superuser) can change or erase the item. To permit different jobs or clients to utilize it, advantages or consent should be allowed.
Various types of advantages in PostgreSQL are −
- SELECT,
- Addition,
- UPDATE,
- Erase,
- Shorten,
- REFERENCES,
- TRIGGER,
- Make,
- Interface,
- Impermanent,
- EXECUTE, and
- Use
Contingent upon the sort of the article (table, work, etc.,), advantages are applied to the item. To appoint advantages to the clients, the GRANT order is utilized.
Syntax for GRANT
Fundamental sentence structure for GRANT order is as per the following −
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
- advantage − qualities could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
- object − The name of an item to which to allow access. The potential items are: table, see, arrangement
- PUBLIC − A short structure addressing all clients.
- Gathering − A gathering to whom to allow advantages.
- username − The name of a client to whom to allow advantages. PUBLIC is a short structure addressing all clients.
The advantages can be denied utilizing the REVOKE order.
Syntax for REVOKE
Essential linguistic structure for REVOKE order is as per the following −
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }
- advantage − qualities could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
- object − The name of an item to which to allow access. The potential articles are: table, see, grouping
- PUBLIC − A short structure addressing all clients.
- Gathering − A gathering to whom to give advantages.
- username − The name of a client to whom to concede advantages. PUBLIC is a short structure addressing all clients.
Example
To comprehend the advantages, let us initially make a USER as follows −
testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE
The message CREATE ROLE shows that the USER "manisha" is made.
Consider the table COMPANY having records as follows −
testdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
Then, let us award all advantages on a table COMPANY to the client "manisha" as follows −
testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT
The message GRANT shows that all advantages are appointed to the USER.
Then, let us renounce the advantages from the USER "manisha" as follows −
testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE
The message REVOKE demonstrates that all advantages are repudiated from the USER.
You can even erase the client as follows −
testdb=# DROP USER manisha;
DROP ROLE
The message DROP ROLE demonstrates USER 'Manisha' is erased from the information base.