Question & Answer
Question
How do I list all NPS database users and their privileges?
Answer
nz_my_grant is the command you can use to list all of the grants made to a user account. It is located in the /nz/support/contrib/4.6/bin directory.
Because it can take some time to finish, you may want to direct the output to a file as follows:
$ /nz/support/contrib/4.6/bin/nz_my_grant > log
$ grep '^GRANT' log | grep -v 'TO PUBLIC '
GRANT insert ON TABLE_1 TO USER_1 ;
GRANT select ON TABLE_2 TO USER_1 ;
If you do not know what the object is, you can use the nz_my_access script which is also located in /nz/support/contrib/bin. This command lists all of the objects to which a given user has access. The names of the columns of the output are as follows:
Database |Object| Type |Privilege(s)|w/Grant Opt| Why you can access this object | User/Group
---------+--------+------+---------+-----------+------------------------------+--------
DB_1 | TABLE_1 |TABLE | I | | (1) Explicit GRANT (user) | USER_1
The Privilege(s) column uses the following abbreviated forms:
(L)ist (S)elect (I)nsert (U)pdate (D)elete (T)runcate (L)ock (A)lter (D)rop a(B)ort (L)oad
(G)enstats (E)xecute Label-A(C)ess Label-(R)estrict Label-E(X)pand Execute-(A)s
Assume that you created a database "DB_1" and a user "TABLE_1" as follows:
$ nzsql DB_1
DB_1(ADMIN)=> CREATE TABLE TABLE_1 (id int);
CREATE TABLE
DB_1(ADMIN)=> CREATE TABLE TABLE_2 (id int);
CREATE TABLE
DB_1(ADMIN)=> GRANT INSERT ON TABLE_1 TO USER_1;
GRANT
DB_1(ADMIN)=> GRANT SELECT ON TABLE_2 TO USER_1;
GRANT
DB_1(ADMIN)=> \q
The following are examples of the resulting nz_my_access output:
$ ./nz_my_access admin | grep TABLE_1
DB_1 | TABLE_1 | TABLE | I | | (1) Explicit GRANT (user) | USER_1
$ ./nz_my_access admin | grep TABLE_2
DB_1 | TABLE_2 | TABLE | S | | (1) Explicit GRANT (user) | USER_1
Historical Number
NZ484586
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21573223