Template to Create Database with Groups and privileges in IBM Netezza

Template to Create Database with Groups and privileges in IBM Netezza

SQL scripts to create Database along with their groups and Privileges granted to them, here is the template to create a sample database NZ_DB.

-- Database creation for NZ_DB database.

-- -----------------------------------------------------------------------------

create database NZ_DB;

\c NZ_DB

-- -----------------------------------------------------------------------------

-- Create standard security groups.

-- -----------------------------------------------------------------------------

create group NZ_DB_ADM sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_STATS sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RW sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RO sessiontimeout 180 querytimeout 120  defpriority normal maxpriority normal rowsetlimit 0;

-- -----------------------------------------------------------------------------

-- Grant permissions to groups.

-- -----------------------------------------------------------------------------

--

-- Grant permissions to NZ_DB_ADM.

--

grant TABLE, EXTERNAL TABLE, TEMP TABLE, VIEW, FUNCTION, PROCEDURE, SEQUENCE to NZ_DB_ADM;

grant LIST, SELECT on NZ_DB to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT on VIEW to NZ_DB_ADM;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_ADM;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on FUNCTION to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on PROCEDURE to NZ_DB_ADM;

grant ABORT, LIST on USER to NZ_DB_ADM;

--

-- Grant permissions to NZ_DB_STATS.

--

grant GENSTATS on TABLE to NZ_DB_STATS;

grant GENSTATS on EXTERNAL TABLE to NZ_DB_STATS;

--

-- Grant permissions to NZ_DB_RW.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_RW;

grant LIST, SELECT on VIEW to NZ_DB_RW;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_RW;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RW;

grant LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_RW;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RW;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RW;

grant LIST on USER to NZ_DB_RW;

--

-- Grant permissions to NZ_DB_RO.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RO;

grant LIST, SELECT on TABLE to NZ_DB_RO;

grant LIST, SELECT on EXTERNAL TABLE to NZ_DB_RO;

grant LIST, SELECT on VIEW to NZ_DB_RO;

grant LIST, SELECT on SYSTEM TABLE to NZ_DB_RO;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RO;

grant LIST, SELECT on SEQUENCE to NZ_DB_RO;

grant LIST, SELECT on SYNONYM to NZ_DB_RO;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RO;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RO;

grant LIST on USER to NZ_DB_RO;

-- -----------------------------------------------------------------------------

-- Example to add the users to the security groups if you want.

-- -----------------------------------------------------------------------------

-- alter group NZ_DB_ADM add user pleduc;

-- alter group NZ_DB_RO add user pleduc;

-- alter group NZ_DB_RW add user pleduc;

-- alter group NZ_DB_STATS add user pleduc;