Basic User Administration commands in NETEZZA

• CONNECTING AS A USER

nzsql -h `hostname` -u testuser -pw testuser

• GRANT/ REVOKE SELECT ON DATABASE ‘COMMON’

system(admin)=> grant list on common to testuser;
GRANT
common(admin)=> grant select on common to testuser;
common(admin)=> revoke all on common from testuser;

• DISPLAY PERMISSIONS

common(admin)=> \dpu testuser

• GRANT LIST/SELECT ON ALL TABLES IN A DATABASE ‘COMMON’

system(admin)=> \c common
common(admin)=> grant list on table to testuser;
common(admin)=> GRANT SELECT ON TABLE TO TESTUSER;

MORE -
grant all on table to <user>;
grant all on view to <user>;
grant all on procedure to <user>;
grant all on function to <user>;
grant all on sequence to <user>;
grant all on synonym to <user>;

• CREATING A NEW USER

create user testuser with password 'testuser';

• ADD USER TO GROUP

ALTER GROUP bi_analysts ADD USER testuser;

• CREATE/ALTER RESOURCE GROUPS

CREATE GROUP bi_analysts WITH RESOURCE MINIMUM 40;
ALTER GROUP public WITH RESOURCE MAXIMUM 50;

• ADD USER TO RESOURCE GROUP

ALTER USER testuser IN RESOURCEGROUP bi_analysts;

Note that adding user to a group doesnt add him to same resourcegroup.

• RESOURCE GROUP PCT

select GROUPNAME as "Resource Group",GRORSGPERCENT as "Min Resource",RSGMAXPERCENT as "Max Resource"
from _v_group where grorsgpercent<>0