- 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