In PostgreSQL, the GRANT statement is used to grant privileges to a role to alter on database objects like tables, views, functions, etc. The following shows the syntax of the GRANT statement: Let’s analyze the above syntax: Example: Create a role called ‘Anshul’ that can login to the PostgreSQL database server as shown below: Now create a table called candidate as below: Now, use the role ‘Anshul’ to log in to the PostgreSQL database server seperately and try to select data from the players table from the Anshul’s session: Here PostgreSQL will raise an error as shown below: To grant the SELECT privilege on the players table to the role anshul, the GRANT statement can be executed in the postgres‘ session as shown below: Now, execute the SELECT statement from the anshul’s session: Seventh, execute the following INSERT statement; PostgreSQL issued the following error because anshul does not have the INSERT privilege on the players table: Now, grant INSERT, UPDATE, and DELETE privileges on the candidates table to the role anshul: Ninth, execute the INSERT statement again from the anshul’s session: Now, anshul can insert data into the players table. In addition to that the Anshul role can update or delete data from the table. Output: Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.Syntax:
GRANT privilege_list | ALL
ON table_name
TO role_name;
create role anshul
login
password 'geeks12345';
create table Players (
player_id int generated always as identity,
first_name varchar(100) not null,
last_name varchar(100) not null,
email varchar(255) not null unique,
phone varchar(25) not null,
primary key(player_id)
);
SELECT * FROM players;
ERROR: permission denied for table players
GRANT SELECT
ON players
TO anshul;
SELECT * FROM players;
INSERT INTO players(first_name, last_name, email, phone)
VALUES('raju', 'kumar', 'raju.kumar@geeforgeeks.org', '408-111-2222');
ERROR: permission denied for table players
GRANT INSERT, UPDATE, DELETE
ON players
TO anshul;
INSERT INTO players(first_name, last_name, email, phone)
VALUES('raju', 'kumar', 'raju.kumar@gmail.com', '408-111-2222');
Data Structures and Algorithms – Self Paced Course
View Details