Question & Answer
Question
How can I identify which users or groups have privilege on a particular object?
Answer
The Netezza privilege model allows for access to any object using one of three precedence levels:
- Does the user/group have object-level permissions?
- Does the user/group have object-class permissions in the local database?
- Does the user/group have object-class permissions in the system catalog (global)?
Because of the three different precedence levels, it can be difficult to ascertain which users or groups have access to a particular object. The script, nz_get_acl, is available in the Support-contributed toolkit and provides this type of information. The script runs in three different modes.
Without any parameters, it will display all users and groups that have access to the object.
- /nz/support/contrib/<VERSION>/bin/nz_get_acl MYDATABASE MYTABLE
You can display object permissions owned by a user as follows:
- /nz/support/contrib/<VERSION>/bin/nz_get_acl -user user1 MYDATABASE MYTABLE
Finally, you can display object permissions for a group as follows:
- /nz/support/contrib/<VERSION>/bin/nz_get_acl -group group1 1 MYDATABASE MYTABLE
The output that is produced is Data Definition Language (DDL), which could be used to apply the same privileges on another NPS system. For example:
[nz@cs-spubox2 ~]$ /nz/support/contrib/4.6/bin/nz_get_acl otcd hzw_test
-- Object: HZW_TEST (in database OTCD)
-- Class: TABLE
-- Owner: STAGE_OWNER
-------------------------------------------------------------------------
-- PRECEDENCE LEVEL 1 -- Object permissions --
-------------------------------------------------------------------------
\c "OTCD"
-- Object access granted to any user
\echo
\echo ***** Grant privileges on objects to users
GRANT select ON HZW_TEST TO GROUP1;
\echo
\echo ***** Grant privileges on objects to users (WITH grant option)
-- Object access granted to any group
\echo
\echo ***** Grant privileges on objects to groups
\echo
\echo ***** Grant privileges on objects to groups (WITH grant option)
-------------------------------------------------------------------------
-- PRECEDENCE LEVEL 2 -- Class permissions --
-------------------------------------------------------------------------
-- Class access granted to any user
\echo
\echo ***** Grant privileges on objects to users
\echo
\echo ***** Grant privileges on objects to users (WITH grant option)
GRANT alter, delete, drop, genstats, insert, list, select, truncate, update ON TABLE TO USER1 WITH GRANT OPTION ;
-- Class access granted to any group
\echo
\echo ***** Grant privileges on objects to groups
GRANT select ON TABLE TO GROUP1 ;
GRANT alter, delete, drop, genstats, insert, list, select, truncate, update ON TABLE TO OTC_WRITE ;
\echo
\echo ***** Grant privileges on objects to groups (WITH grant option)
-------------------------------------------------------------------------
-- PRECEDENCE LEVEL 3 -- Class permissions (in the SYSTEM database) --
-------------------------------------------------------------------------
\c system
-- Class access granted to any user
\echo
\echo ***** Grant privileges on objects to users
GRANT select ON TABLE TO USER1;
GRANT select ON TABLE TO USER2;
\echo
\echo ***** Grant privileges on objects to users (WITH grant option)
GRANT select ON TABLE TO USER3 WITH GRANT OPTION ;
GRANT select ON TABLE TO USER4 WITH GRANT OPTION ;
-- Class access granted to any group
\echo
\echo ***** Grant privileges on objects to groups
GRANT select ON TABLE TO ADS_READ ;
\echo
\echo ***** Grant privileges on objects to groups (WITH grant option)
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ116612
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21570109