It's all about permissions - Part 3 ; PLSQL permissions users

Hi guys!

This post is simply on PLSQL permissions.

Specifically, how to script out users.

In Postgres, users can have access to:

• a sequence
• a table
• a schema
• a cluster

This example covers giving users access to a sequence, table and schema only. Permissions to a cluster are obtained differently; these permissions are created in AWS (Amazon Web Services).

Note: In Postgres, make sure that your Postgres connection has read/write permissions by right clicking the cluster and selecting properties to view the user under the 'connections' tab.

Locate the database you want to access under the appropriate cluster > right click the database > choose 'Grant Wizard'.

a) Select all the objects.

b) select the grantee (the user you want to have access to the database) + the level of permissions they need (which is SELECT in this example).

c) copy the text results in step 3 of 3 and paste it into Notepad++.

Note: The permissions the grant wizard provided did not grant access to the schema. You will have to manually grant access to the schema as shown below so the user can have access to the database.

GRANT SELECT ON SEQUENCE database.sequenceName  TO user;

GRANT SELECT ON SEQUENCE database.sequenceName TO user;

GRANT SELECT ON TABLE database.tableName TO user;

GRANT SELECT ON TABLE database.tableName TO user;

GRANT SELECT ON TABLE public.schema_version TO user;

GRANT USAGE ON SCHEMA schemaName TO user;


*Also, there was a system reserved schema which had no effect on permissions called apgcc in Postgres; You can comment out that line of permissions.*
--GRANT USAGE ON SCHEMA apgcc TO user;



That's it!



If you have any questions related to this post, please put them below.


Thank you and Happy reading,

-marshé hutchinson
#learnSQLwithme