What ispg_read_all_data & pg_write_all_data role in PostgreSQL

                    pg_read_all_data & pg_write_all_data

pg_read_all_data is a built-in PostgreSQL role that allows a user to:

- Read all tables (SELECT)

- Read all schemas

- Read all sequences

Across all databases in the cluster

It is read-only (no INSERT/UPDATE/DELETE).

Step 1: Check if pg_read_all_data role exists

Expected result:

Role exists (PostgreSQL 14+)

If it does not exist → then PostgreSQL version is very old

Step 2: Create a read-only user

CREATE ROLE readonly_user LOGIN PASSWORD 'postgres@123';

Step 3: Grant pg_read_all_data to the user

    This instantly gives access to all existing & future tables.

Step 4: Grant CONNECT privilege on database

Step 5: Verify select(read-only) access to user readonly_user.

Select should work.

Step 6: Verify insert into a table from readonly_user.

It will not work.

Step 7: Verify pg_write_all_data role.

Step 8: create a user and grant pg_write_all_data role. Also grant connect to the database.

    

Step 9: Run insert command -

        It will work.

Step 10: Now run the select from pg_write_all_data on departments table -

    It will not work.

Step 11: If any user want select, insert, update and delete permission then we have to grant                             pg_read_all_data and pg_write_all_data both.

How to find out who has changed the user's password and when

In our day to activity we come across this situation when suddenly a password gets changed and no one takes responsibility who did this. So here is the way to find out the genius who is not taking responsibility. Or simply we can say it is the best way to find out who did the password change in oracle database.

Note: We can find out the user who changed the password and when he did, only when we have below 4 steps already in place. 

Step1- Create a table

SQL> create table reyaj (msg varchar2(1000));
Table created.

Step2- Create a prodedure

SQL> create or replace procedure reyaj_p (who in varchar2, what in varchar2)
  is
  pragma autonomous_transaction;
  begin
 insert into reyaj values (who||' modifies '||what||'''s password at '||systimestamp);
 commit;
  end;
   /
  Procedure created.

Step 3 - Create a function

SQL> CREATE OR REPLACE FUNCTION verify_function
  (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
  BEGIN
  reyaj_p (user, username);
  RETURN(TRUE);
  END;
 /
 Function created.

Step 4 - Alter default profile

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile altered.

Step 5 - Change password 

SQL> alter user test identified by test321;
User altered.

Step 6 - Now query the table to find out who changed the password and when

SQL> select * from reyaj;

MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00

Step 7 - We can test it again if it is working properly and creating a new user and chaning its password.

SQL> create user passtest identified by passtest123;
User created.

SQL> alter user passtest identified by passtest321;
User altered.

SQL> select * from reyaj;
MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.11.985540 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.53.454250 AM -05:00

Granting permissions on v$ views

Sometimes we may get a requirement in which we need to provide access on dynamic performance views like v$session, v$process etc. I had seen this when application team want to capture session information from their webpages.

In such situations, application team will ask to grant select on those views. when you try the same as just like normal grant statement, you will get following error

SQL> grant select on v$session to PAST1;
grant select on v$session to PAST1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The reason for this is v$session is synonym to the view v_$session. so you need to grant select on that main view instead of synonym

SQL> grant select on v_$session to PAST1;

Grant succeeded.

Same you need to follow for all other v$ views. But for data dictionary views, you can directly grant permissions

SQL> grant select on dba_users to PAST1;

Grant succeeded.

Note : Due to security reasons, never we should encourage granting permissions on data dictionary of Oracle until very much required

Finding all the privileges and roles granted to a user

To find all the privileges and roles granted to a user 3 dictionary views comes very handy:

1. dba_sys_privs
2. dba_tab_privs
3. dba_role_privs

I queried all 3 views and then verified with toad to match the result for a user called "USERNAME".
The result was very accurate.

SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='USERNAME';
GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
USERNAME                        CREATE PUBLIC SYNONYM
USERNAME                        CREATE SYNONYM
USERNAME                        UNLIMITED TABLESPACE
USERNAME                        CREATE SEQUENCE
USERNAME                        CREATE ANY SYNONYM
USERNAME                        CREATE MATERIALIZED VIEW
USERNAME                        CREATE TRIGGER
USERNAME                        CREATE TABLE
USERNAME                        CREATE SESSION
USERNAME                        CREATE DATABASE LINK
USERNAME                        CREATE PROCEDURE
USERNAME                        CREATE VIEW

12 rows selected.

SQL> select GRANTEE, PRIVILEGE from dba_tab_privs where GRANTEE='USERNAME';

no rows selected

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTEE='USERNAME';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
USERNAME                        CONNECT
USERNAME                        RESOURCE