Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Some of the sample netezza commands to create database, user groups , users, add user to a group, grant permissions to netezza objects is below:

Creating a netezza database, group, and adding user group permissions to that database

\c SYSTEM
CREATE DATABASE <databasename>;
CREATE GROUP <groupname> WITH USER <username1>, <username2>;
GRANT LIST ON <databasename> TO <groupname> WITH GRANT OPTION;GRANT  Create External Table, Create Sequence, Create Synonym, Create Table, Create Temp Table, Create View TO <groupname> ;
GRANT backup, create sequence, create synonym, create table, create external table, create temp table, create view, restore  TO <groupname> ;

To create a user group in netezza database:

Syntax: CREATE GROUP <groupname>

Example: CREATE GROUP  MYTESTGROUP1;

To alter the settings of the group:

ALTER GROUP "<groupname>" WITH ROWSETLIMIT 0 SESSIONTIMEOUT 0 QUERYTIMEOUT 50 DEFPRIORITY High MAXPRIORITY High;

To create a user and assign to a netezza database user group:

CREATE USER  <username>  WITH PASSWORD '??' IN GROUP "<groupname>";

If user already exists or after you have added a user then you can use the command below to add that user to a group:

ALTER GROUP "<groupname>" ADD USER "<username>";

To provide permission on netezza objects such as tables, views, database etc to a user group or a user use the syntax below:

GRANT List ON "<databasename>" TO "groupname";

GRANT List, Select, ON "<tablename>" TO "groupname";

GRANT delete,genstats,groom,insert,list,select,truncate,update ON <tablename> TO <groupname>;
 

To copy permissions from one table to the other in Netezza:

ALTER TABLE <target_table_name> set privileges to  <target table name>;

To change ownership of a netezza table:

alter table <tablename> owner to <newownername>

The above command change the schema name associated with the table