SQL to check user details and its activities

Note: IF you want to add another query here, send your query with output to gpdba@gpdba.com. We will post it here with your name and email id.

1. List user information
sachi=# select
sachi-# a.rolname as "User name",
sachi-# a.oid as "User Oid",
sachi-# a.rolsuper as "Super User",
sachi-# b.rsqname as "Resource queue",
sachi-# a.rolcanlogin as "Can Login",
sachi-# a.rolconnlimit as "Connection limit",
sachi-# b.rsqcountlimit,
sachi-# b.rsqcostlimit,
sachi-# b.rsqovercommit,
sachi-# b.rsqignorecostlimit
sachi-# from
sachi-# pg_roles a,
sachi-# pg_resqueue b
sachi-# where
sachi-# a.rolresqueue=b.oid;
User name | User Oid | Super User | Resource queue | Can Login | Connection limit | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
--------------+----------+------------+----------------+-----------+------------------+---------------+--------------+---------------+--------------------
admin | 88044 | f | pg_default | f | -1 | 20 | -1 | f | 0
gpadmin | 10 | t | pg_default | t | -1 | 20 | -1 | f | 0
gpmon | 96279 | t | pg_default | t | -1 | 20 | -1 | f | 0
sachi | 16994 | f | pg_default | t | -1 | 20 | -1 | f | 0
gpuser | 33477 | f | pg_default | t | -1 | 20 | -1 | f | 0
e2e_bdasvc | 180262 | f | e2erq | t | 2 | 2 | -1 | f | 0
(6 rows)


2.  ACTIVITIES ON USERS 
sachi=# select 
sachi-# a.rolname as User, 
sachi-# staactionname as Create_alter, 
sachi-# stausename as Created_by, 
sachi-# stasubtype as Action, 
sachi-# statime as When 
sachi-# from 
sachi-# pg_stat_last_shoperation p, 
sachi-# pg_authid a 
sachi-# where 
sachi-# p.objid=a.oid 
sachi-# and 
sachi-# classid=1260 
sachi-# order by 1 ;
user | create_alter | created_by | action | when 
--------------+--------------+------------+------------------+-------------------------------
admin | CREATE | gpadmin | ROLE | 2014-10-17 22:23:34.663169-04
admin | PRIVILEGE | gpadmin | GRANT | 2014-10-17 22:23:50.37452-04
e2e_bdasvc | CREATE | gpadmin | ROLE | 2015-02-25 15:56:40.121114-05
e2e_bdasvc | ALTER | gpadmin | CONNECTION LIMIT | 2015-02-25 16:06:50.401481-05
gpadmin | ALTER | gpadmin | PASSWORD | 2013-11-27 12:54:24.258383-05
gpmon | CREATE | gpadmin | ROLE | 2014-10-25 10:09:36.054814-04
gpuser | CREATE | gpadmin | ROLE | 2014-03-30 19:52:27.89646-04
sachi | ALTER | gpadmin | LOGIN | 2013-11-27 14:20:29.535817-05
sachi | CREATE | gpadmin | ROLE | 2013-11-27 13:00:00.963128-05
(9 rows)
sachi=#

3. User last LOGIN details

sachi=# select
sachi-# logtime as Lastqueryrantime,
sachi-# logsessiontime as Sessloggedintodbtime,
sachi-# loguser as User,
sachi-# logdatabase as Db,
sachi-# loghost as Fromhost,
sachi-# logseverity as Serverity,
sachi-# logmessage as Queryran
sachi-# from
sachi-# gp_toolkit.gp_log_database
sachi-# where
sachi-# loguser='sachi'
sachi-# order by 1 desc
sachi-# limit 1;


lastqueryrantime | sessloggedintodbtime | user | db | fromhost | serverity | queryran

-------------------------------+------------------------+-------+-------+----------+-----------+-------------------------------------------------------------
---------------------
2014-11-04 16:50:52.805827-05 | 2014-11-04 16:50:52-05 | sachi | sachi | [local] | FATAL | no pg_hba.conf entry for host "[local]", user "sachi", datab
ase "sachi", SSL off
(1 row)


sachi=#

4. USER PRIVILEGES

sachi=# select usename as "User name",
sachi-#        nspname || '.' || relname as "Object Name",
sachi-#        case relkind when 'r' then 'TABLE'
sachi-#                     when 'v' then 'VIEW'
sachi-#        end as "Object Type",
sachi-#        priv as "privilege"
sachi-# from pg_class
sachi-# join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
sachi-#      pg_user,
sachi-#      (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
sachi-# where relkind in ('r', 'v')
sachi-#       and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
sachi-#       and not (nspname ~ '^pg_' or nspname = 'information_schema')
sachi-# order by 2, 1, 3, privorder;
  User name   |                                Object Name                                | Object Type | privilege 
--------------+---------------------------------------------------------------------------+-------------+-----------
 gpadmin      | dbadmin.smart_analyze_config                                              | TABLE       | SELECT
 gpadmin      | dbadmin.smart_analyze_config                                              | TABLE       | INSERT
 gpadmin      | dbadmin.smart_analyze_config                                              | TABLE       | UPDATE
 gpadmin      | dbadmin.smart_analyze_config                                              | TABLE       | DELETE
 gpmon        | dbadmin.smart_analyze_config                                              | TABLE       | SELECT
 gpmon        | dbadmin.smart_analyze_config                                              | TABLE       | INSERT
 gpmon        | dbadmin.smart_analyze_config                                              | TABLE       | UPDATE
 gpmon        | dbadmin.smart_analyze_config                                              | TABLE       | DELETE
 gpadmin      | dbadmin.v_dist_key                                                        | VIEW        | SELECT
 gpadmin      | dbadmin.v_dist_key                                                        | VIEW        | INSERT
 gpadmin      | dbadmin.v_dist_key                                                        | VIEW        | UPDATE
 gpadmin      | dbadmin.v_dist_key                                                        | VIEW        | DELETE
 gpmon        | dbadmin.v_dist_key                                                        | VIEW        | SELECT
 gpmon        | dbadmin.v_dist_key                                                        | VIEW        | INSERT
 gpmon        | dbadmin.v_dist_key                                                        | VIEW        | UPDATE
 gpmon        | dbadmin.v_dist_key                                                        | VIEW        | DELETE
 gpadmin      | dbadmin.v_distribution_key                                                | VIEW        | SELECT
 gpadmin      | dbadmin.v_distribution_key                                                | VIEW        | INSERT
 gpadmin      | dbadmin.v_distribution_key                                                | VIEW        | UPDATE
 gpadmin      | dbadmin.v_distribution_key                                                | VIEW        | DELETE
....









Comments