Scribd
Upload
16 views

Grant Command

Uploaded by

Gaurav Vadakte
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
16 views

Grant Command

Uploaded by

Gaurav Vadakte
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

******Grant command*****

 PostgreSQL, the GRANT command is used to grant privileges or


permissions to database objects such as tables, views, functions,
and schemas

Commnad : grant < privileges> on <objects> To <user or


roles> ;

NOTE:- permissions are typically granted at the schema or object


level within a database.

A. OBJECT LEVEL PERMISSION:- 1.select 2.Delete 3. insert 4.Update

Condition 1:- If want to grant read only permission for specific


user for specific schema then we use :-

Grant select on all tables in schema <schema_name> to


user_name;

Condition 2:- if want grant all permission:-

Grant all privileges on all tables in schema <schema_name>


to username;

Condition 3.:- if we want to give multiple permission for multiple


user:-

Grant select,update,insert on all tables in schema


<schema_name> to username1,username2;

Conditon4:- if we want to grant read only permission on specific


table ,specific database:-

Postgres# \c dvd--------------(dvd=database name)


Dvd# grant select on actor to gaurav;

(table_name=actor ; username:- gaurav)

B. Cluster level:-

Condition1:- Grant permission to user to cretate database:-

Command:- alter user <username> createdb;


Condition2:- Make user superuser

Alter user <username> with superuser

\dp command to obtain information about existing privileges for tables


and columns.

r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege

--------------------------------------------------------------------------------------

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA
public TO read_write ;
GRANT ALL ON ALL TABLES IN SCHEMA public TO ADMIN ;

Create Read Only User :-

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword' ;


GRANT CONNECT ON DATABASE <database_name > TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Grant access privileges on objects created in


the future :-

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON


TABLES TO read_only;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT


SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON


TABLES TO ADMIN;

Or, you can set access privileges on objects created in the future by
specified user.

ALTER DEFAULT PRIVILEGES FOR ROLE ADMIN GRANT SELECT ON


TABLES TO read_only;

You might also like