TechBlogs- Teradata and Netezza : Netezza Advanced Security

Netezza Advanced Security

1.System Security

SYSTEM(ADMIN)=> CREATE USER dso;

CREATE USER

SYSTEM(ADMIN)=> GRANT MANAGE SECURITY TO dso;

GRANT

SYSTEM(ADMIN)=> GRANT USER TO dso;

GRANT

2.User Login Control

Concurrent Sessions

For example, to set the number of concurrent sessions to 3 for user John Doe, enter the following:

SYSTEM(ADMIN)=> CREATE USER jdoe CONCURRENT SESSIONS 3;

Access Time Control

The following example creates a new user with a concurrent session limit of 2 and an

access time that ranges from 7:00 AM to 7:00 PM:

SYSTEM(ADMIN)=> CREATE USER jdoe CONCURRENT SESSIONS 2 ACCESS TIME

(DAY ALL START '7:00' END '19:00');

CREATE USER

The following example creates a new group with a concurrent session limit of 8 and an

access time that ranges from Tuesday to Thursday, from 9:00 AM to 6:00 PM:

SYSTEM(ADMIN)=> CREATE GROUP finance CONCURRENT SESSIONS 8 ACCESS TIME

(DAY 3,4,5 START '9:00' END '18:00');

CREATE GROUP

3.Masquerading

The following command example begins a session for user John, using the password ABCD,

and accessing the database BIZ:

nzsql -u JOHN -pw ABCD -db BIZ

BIZ(JOHN)=>

John uses the EXECUTE AS command to masquerade as the user Hank:

BIZ(JOHN)=> EXECUTE AS HANK;

EXECUTE AS

To see which is the session user and which is the current user, run the following:

BIZ(JOHN)=> SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | HANK

(1 row)

To reverse the EXECUTE AS command, use the REVERT command, which changes the current

user back:

BIZ(JOHN)=> REVERT;

REVERT

BIZ(JOHN)=> SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | JOHN

(1 row)

Nesting and Stored Procedures

You can nest EXECUTE AS and REVERT commands to switch the current user. In the following

example, John masquerades as Hank, then masquerades as Tom, and reverts back:

BIZ (JOHN) => EXECUTE AS HANK;

EXECUTE AS

BIZ (JOHN) => SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | HANK

(1 row)

BIZ (JOHN) => EXECUTE AS TOM;

EXECUTE AS

BIZ (JOHN) => SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | TOM

(1 row)

BIZ(JOHN)=> REVERT

REVERT

BIZ (JOHN) => SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | HANK

(1 row)

BIZ(JOHN)=> REVERT

REVERT

BIZ (JOHN) => SELECT session_user, current_user;

SESSION_USER | CURRENT_USER

--------------+--------------

JOHN | JOHN

(1 row)

You can create stored procedures to use nested masquerading commands. For the next example, first login as Admin and set up a group of users and permissions.

BIZ(ADMIN)=> CREATE USER john PASSWORD 'john';

CREATE USER

BIZ(ADMIN)=> CREATE USER hank PASSWORD 'hank';

CREATE USER

BIZ(ADMIN)=> CREATE USER tom PASSWORD 'tom123';

CREATE USER

BIZ(ADMIN)=> GRANT EXECUTE AS ON tom to hank;

GRANT

BIZ(ADMIN)=> GRANT LIST ON john,hank,tom to john, hank, tom;

GRANT

BIZ(ADMIN)=> GRANT CONNECT ON dev to john, hank, tom;

GRANT

BIZ(ADMIN)=> GRANT CREATE PROCEDURE to hank;

GRANT

4.Key Management
Changing Keys

On the host, you can change the key used for encrypting and decrypting passwords by using

the syntax as in the following example:

SET SYSTEM DEFAULT HOSTKEY TO <keystore name>.<key name>;

<keystore name> is the name of the key store

<key name> is the name of the key, which must be of type AES_256

Digital Signing

To set the audit digital signature key, use the syntax as in the following example:

ALTER HISTORY CONFIG

KEY <keystore name>.<key name>;

To turn off signing, use the following command:

ALTER HISTORY CONFIG

KEY NONE

To verify the signature, in addition to displaying the keys, Netezza provides the following

sample script that checks the key against the signature:

nzverifyauditsig.sh

The output returns a verification or a failure message, as in the following:

signature verified

signature could not be verified

Creating and Managing Keys

To create a keystore, use the syntax as in the following example:

CREATE KEYSTORE <name> TYPE <type> PASSWORD <password>;

<name> is the name you give the keystore

<type> is LOCAL, the only type currently supported

<password> is the password you give to encrypt the keys held in that keystore

To create or import a key on the Netezza host, use the syntax as in the following example:

CREATE CRYPTO KEY <keystore name>.<key name> TYPE <type> {VALUE

<value> PASSWORD <password>}

<keystore name> is the name of the keystore

<key name> is the name of the key

<type> currently supported types are asymmetric keys of type DSA_KEYPAIR, and symmetric

keys of type AES_256

<value> is the value of the key/key pair (encrypted form). Only used for importing keys

into the key store

<password> is used to decrypt the key value, if one is specified

5.Multi-Level Security and Row-Secure Tables

The following is an example of how to create and display levels:

DEV(ADMIN)=> CREATE SECURITY LEVEL conf VALUE 500;

CREATE SECURITY LEVEL

DEV(ADMIN)=> CREATE SECURITY LEVEL greater VALUE 600;

CREATE SECURITY LEVEL

DEV(ADMIN)=> CREATE SECURITY LEVEL secret VALUE 800;

CREATE SECURITY LEVEL

DEV(ADMIN)=> SHOW SECURITY LEVEL ALL;

NAME | LEVEL

----------+--------

PUBLIC | 0

CONF | 500

GREATER | 600

SECRET | 800

OMNI | 32767

(5 rows)

RSTs and External Tables

DEV(ADMIN)=> CREATE TABLE rst (id int, name varchar(80), metric int)

ROW SECURITY;

CREATE TABLE

DEV(ADMIN)=> CREATE EXTERNAL TABLE 'TMP/XT’ AS SELECT *, _SEC_LABEL

FROM rst;

INSERT 0 0

Nzsql Security Commands

ALTER CATEGORY

Use the ALTER CATEGORY command to change the name of the category.

ALTER CATEGORY ARGON RENAME TO BORON;

ALTER COHORT

Use the ALTER COHORT command to change the name or the hierarchy position of the

cohort.

ALTER COHORT SECRET RENAME TO CONIFDENTIAL IN COHORT HR;

ALTER DATABASE

Use the ALTER DATABASE command with the added COLLECT HISTORY clause.

ALTER DATABASE SECRET COLLECT HISTORY ON;

ALTER GROUP

Use the ALTER GROUP command to alter a group with additional clauses.

ALTER GROUP FLIGHT WITH COLLECTHISTORY OFF;

ALTER HISTORY CONFIGURATION

Use the ALTER HISTORY CONFIGURATION command to modify the configuration of query

or audit history logging. This is executed on the source Netezza system. Note that you cannot

alter the current history configuration.

ALTER HISTORY CONFIGURATION LASTWEEK VERSION 2;

ALTER SECURITY LEVEL

Use the ALTER SECURITY LEVEL command to change the name or value of a security

level.

ALTER SECURITY LEVEL SECRET { RENAME TO CONFIDENTIAL VALUE 22 };

ALTER USER

Use the ALTER USER command to change user settings.

ALTER USER BOB CONCURRENT SESIONS 3;

CREATE CATEGORY

Use the CREATE CATEGORY command to create a new security category.

CREATE CATEGORY ARGON;

CREATE COHORT

Use the CREATE COHORT command to create a new security cohort.

CREATE COHORT FINANCE IN COHORT HR;

CREATE DATABASE

Use the CREATE DATABASE command with the added COLLECT HISTORY clause.

CREATE DATABASE SECRET COLLECT HISTORY ON;

CREATE GROUP

Use the CREATE GROUP command to create a new group with additional clauses.

CREATE GROUP FLIGHT;

CREATE SECURITY LEVEL

Use the CREATE SECURITY LEVEL command to create a new security level, giving it a

name and a value.

CREATE SECURITY LEVEL SECRET VALUE 99;

CREATE TABLE

Use the CREATE TABLE command to create a new table with the option of row security

support.

CREATE TABLE NEWTABLE DISTRIBUTE ON RANDOM ROW SECURITY;

CREATE USER

Use the CREATE USER command to create a new user with additional clauses.

CREATE USER BOB WITH AUDIT CATEGORY TOP;

DROP CATEGORY

Use the DROP CATEGORY command to remove the category from the label security

configuration.

DROP CATEGORY ARGON;

DROP COHORT

Use the DROP COHORT command to remove a cohort.

DROP COHORT SECRET;

DROP HISTORY CONFIGURATION

Use the DROP HISTORY CONFIGURATION command to drop the configuration for query or

audit history logging.

DROP HISTORY CONFIGURATION LASTWEEK VERSION 2;

DROP SECURITY LEVEL

Use the DROP SECURITY LEVEL command to remove a security level.

DROP SECURITY LEVEL SECRET;

EXECUTE AS

Use the EXECUTE AS command to set the CURRENT USER of the session.

EXECUTE AS BOB;

REVERT

Resets the CURRENT USER of the session back to the previous EXECUTE AS user; if none,

then revert to the SESSION USER.

REVERT;

SET HISTORY CONFIGURATION

Use the SET HISTORY CONFIGURATION command to create the initial configuration for

query or audit history logging on a system. This is executed on the source Netezza system.

SET HISTORY CONFIGURATION LASTWEEK;

SHOW CATEGORY

Use the SHOW CATEGORY command to display one or more categories.

SHOW CATEGORY ALL;

SHOW COHORT

Use the SHOW COHORT command to display one or more cohorts or to display the cohort

hierarchy.

SHOW COHORT HR;

SHOW HISTORY CONFIGURATION

Use the SHOW HISTORY CONFIGURATION command to display the query or audit history

configuration settings. This is executed on the source Netezza system. By default with no

arguments it gives the current query or audit history configuration.

SHOW HISTORY CONFIGURATION ALL;

SHOW SECURITY LEVEL

Use the SHOW SECURITY LEVEL command to display one or all security levels.

SHOW SECURITY LEVEL ALL;

CLI Commands

This section describes the CLI commands for the security feature.

nzhistcleanupdb

Use this command to periodically delete old history information from a history database.

The following deletes history data which is older than January 1, 2009,10:45 PM, from the

histdb history database:

nzhistcleanupdb -d histdb -u myuser -p password -t "1/1/2009,22:45"

nzverifyauditsig

The nzverifyauditsig command verifies the signature of a signed block in a specified audit

database table.

The following provides sample usage:

nzverifyauditsig -d auditdb -t query_epilog -v 1 -u histusr -p admpw

-i 2 -m 1

Creating a Database Security Officer and Database Administrator

security label:

SYSTEM(ADMIN)=> CREATE USER dba 'PASSWORD 'dddd' SECURITY LABEL '::';

CREATE USER

SYSTEM(ADMIN)=> GRANT CREATE DATABASE to dba;

GRANT

The DBA will not be able to see any data above 'PUBLIC::' even in tables that they create.

As administrator, create a Database Security Officer (DSO) with the minimum security

label. Note that giving Manage Security permission allows the DSO to change their setting.

SYSTEM(ADMIN)=> CREATE USER dso PASSWORD 'dddd' SECURITY LABEL '::';

CREATE USER

SYSTEM(ADMIN)=> GRANT MANAGE SECURITY to dso;

GRANT

SYSTEM(ADMIN)=> GRANT CREATE USER to dso;

GRANT

SYSTEM(ADMIN)=> GRANT LIST ON dba to dso;

GRANT

Creating a Security Model as Database Security Officer

For this example, login as the newly-created DSO and create a security model, using levels,

categories, and cohorts.

SYSTEM(ADMIN)=> \c system dso dddd

You are now connected to database system as user dso.

SYSTEM(DSO)=> CREATE SECURITY LEVEL confidential VALUE 100;

CREATE SECURITY LEVEL

SYSTEM(DSO)=> CREATE SECURITY LEVEL secret VALUE 200;

CREATE SECURITY LEVEL

SYSTEM(DSO)=> CREATE SECURITY LEVEL "Top Secret" VALUE 300;

CREATE SECURITY LEVEL

SYSTEM(DSO)=> CREATE CATEGORY red;

CREATE CATEGORY

SYSTEM(DSO)=> CREATE CATEGORY green;

CREATE CATEGORY

SYSTEM(DSO)=> CREATE CATEGORY blue;

CREATE CATEGORY

SYSTEM(DSO)=> CREATE COHORT corp;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT hr in COHORT corp;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT fin in COHORT corp;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT eng in COHORT corp;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT qa in COHORT eng;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT sw in COHORT eng;

CREATE COHORT

SYSTEM(DSO)=> CREATE COHORT hw in COHORT eng;

CREATE COHORT

Creating Sample Users

For this example, login as the DSO and create sample users. Then grant the DBA List privileges

on the users created.

SYSTEM(DSO)=> CREATE USER sw1 PASSWORD 'swsw' SECURITY LABEL

'confidential:green:sw';

CREATE USER

SYSTEM(DSO)=> CREATE USER sw2 PASSWORD 'swsw' SECURITY LABEL

'confidential:red:sw';

CREATE USER

SYSTEM(DSO)=> CREATE USER engmgr PASSWORD 'emem' SECURITY LABEL

'secret:red, green:eng';

CREATE USER

SYSTEM(DSO)=> GRANT LIST ON sw1,sw2,engmgr to dba;

GRANT

Creating Sample Users

For this example, login as the DSO and create sample users. Then grant the DBA List privileges

on the users created.

SYSTEM(DSO)=> CREATE USER sw1 PASSWORD 'swsw' SECURITY LABEL

'confidential:green:sw';

CREATE USER

20493 Rev.2 B-3

Creating a Database and Granting Access

SYSTEM(DSO)=> CREATE USER sw2 PASSWORD 'swsw' SECURITY LABEL

'confidential:red:sw';

CREATE USER

SYSTEM(DSO)=> CREATE USER engmgr PASSWORD 'emem' SECURITY LABEL

'secret:red, green:eng';

CREATE USER

SYSTEM(DSO)=> GRANT LIST ON sw1,sw2,engmgr to dba;

GRANT

Creating a Database and Granting Access

As the DBA, create a database (mlssample), connect to it, and grant access to the users.

Note that the DSO does not need access to the created database.

SYSTEM(DSO)=> \c system dba dddd

You are now connected to database system as user dba.

SYSTEM(DBA)=> CREATE DATABASE mlssample;

CREATE DATABASE

SYSTEM(DSO)=> GRANT CONNECT ON mlssample to sw1,sw2,engmgr;

GRANT

SYSTEM(DBA)=> \c mlssample dba dddd

You are now connected to database mlssample as user dba.

Creating a Row-Secure Table With Permissions

For this example, create a row-secure table (projstatus) and grant privileges for the users.

MLSSAMPLE(DBA)=> CREATE TABLE projstatus(id int, name varchar(80),

metric int) ROW SECURITY;

CREATE TABLE

MLSSAMPLE(DBA)=> GRANT SELECT, INSERT, UPDATE, DELETE ON projstatus to

sw1;

GRANT

MLSSAMPLE(DBA)=> GRANT SELECT, INSERT, UPDATE, DELETE, LABEL ACCESS on

projstatus to sw2;

GRANT

MLSSAMPLE(DBA)=> GRANT SELECT, INSERT, UPDATE, DELETE, LABEL ACCESS,

LABEL EXPAND on projstatus to engmgr;

GRANT

The following are the privileges of each user:

􀁘 User sw1 can operate on projstatus, but cannot see the labels.

􀁘 User sw2 can operate on projstatus, can see the labels, but cannot change them.

􀁘 User engmgr can operate on projstatus, can see the labels, and can declassify data.

Netezza Data Loading

External Table- Example

The following examples show how to use the CREATE EXTERNAL TABLE command.

􀁔 To create an external table, enter:

CREATE EXTERNAL TABLE ext_orders(ord_num INT, ord_dt

TIMESTAMP)USING(dataobject('/tmp/order.tbl') DELIMITER '|');

􀁔 To create an external table that uses column definitions from an existing table, enter:

CREATE EXTERNAL TABLE demo_ext SAMEAS emp USING (dataobject

('/tmp/demo.out') DELIMITER '|');

􀁔 To create an external table and specify the escape character (‘\’), enter:

CREATE EXTERNAL TABLE extemp SAMEAS emp USING( dataobject

('/tmp/extemp.dat') DELIMITER '|' escapechar '\');

􀁔 To unload data from your database into a file by using an insert statement, enter:

INSERT INTO demo_ext SELECT * FROM weather;

􀁔 To drop an external table, enter:

DROP TABLE extemp

The system removes only the external table’s schema information from the system catalog.

The file defined in the dataobject option remains unaffected in the filesystem.

􀁔 To back up by creating an external table, enter:

CREATE EXTERNAL TABLE '/path/extfile' USING (FORMAT 'internal'

COMPRESS true) AS SELECT * FROM source_table;

􀁔 To restore from an external table, enter:

INSERT INTO t_desttbl SELECT * FROM EXTERNAL'/path/extfile'

USING(FORMAT 'internal' COMPRESS true);

Transient External Table

The following examples show how to specify the shape of a transient external table:

􀁔 To take on the schema of the target table:

insert into <table> select * from external '<file>' [USING(...)]

􀁔 To take on the schema of the query:

create external table '<file>' [USING (...)] as <QUERY>

􀁔 To take on the schema of <table>:

select * from external '<file>' sameas <table> [USING(...)]

􀁔 To take on the schema as defined:

select * from external '<file>' (schema) [USING(...)]

􀁔 To take on the schema as defined:

create external table '<file>' (schema) [USING(...)]

􀁔 To make the source file FIXED format with the schema as defined:

select * from external '<file>' (schema) USING (FORMAT 'FIXED'

LAYOUT (...))

􀁔 To make the source file FIXED format and the table takes on the schema of the target

table:

insert into <table> select * from external '<file>' USING (FORMAT

'FIXED' LAYOUT (...))

􀁔 The following example will not work, because you cannot unload data into a FIXED format

external table:

create external table '<file>' [(schema)] USING (FORMAT 'FIXED'

LAYOUT ... )

Fixed-Length Format

The following examples show how to use Fixed-Length format with external tables:

􀁔 To load data in fixed format, enter: