Viewing table locks as non-admin user

Answer

As of NPS Release 4.5, an admin user can use the show locks; command to view table locks.  However you may have an environment in which you want to a user (such as a developer) to have the ability to view table locks but you do not want to give that user administrative access for auditing or other security-related reasons.

In NPS 4.6 and later, you can grant select permission for the _t_pg_locks table so that a non-admin user can get the needed information and keep the show locks command reserved as an 'admin only' function as designed.

The following example shows the following:

1. The user1 account is denied access to table locks information.
2. The admin user then grants select permissions on that table for user1. 
3. The user1 account is able to see the table lock information.
my_database(user1)=> select * from _t_pg_locks; 
ERROR: Permission denied on "_t_pg_locks".
my_database(user1)=> \q

system(admin)=> grant select on _t_pg_locks to user1; 
GRANT
system(admin)=> \q

[nz@nps_server ~]$ nzsql -db my_database -u user1 -pw password 


my_database(user1)=> select * from _t_pg_locks; 

sessionid|dbname|relname|username|processid|lockmode    | granttime|
---------+------+-------+--------+---------+------------+----------+
20970    |global|t_pg_lock|user1|18107|AccessShareLock | 15:37:42


Note: Due to display limitations, several columns of output (dbid, relid, clientip, lockstate, requesttime, and command) have been omitted and the granttime column has been trimmed of the date.