Secure and harden Netezza data warehouse appliance with InfoSphere Guardium

This article will build the basic foundation of a hardened, monitored, and secured Netezza environment. To achieve this, InfoSphere® Guardium®, IBM's solution for database activity monitoring and auditing, will be used. Hardening Netezza with Guardium will be presented as a multi-step process. The first step addresses the basic initial setup from a security and user access management perspective. Configuration of users, groups, and privileges will be addressed. The second step leverages Guardium Vulnerability Assessment to scan the environment for Netezza-specific insecure configurations and privileges. The third step details how to address the failed Vulnerability Assessment test by making appropriate adjustments in the Netezza configuration. The last step is ensuring that the optimal configuration of privileges built in the first and third stages are not tampered with. Guardium Entitlement Reports will be used to audit the Netezza user management.

Benjamin Leonhardi (benleon@ca.ibm.com), Software Engineer, IBM

Photo of author Benjamin LeonhardiBenjamin Leonhardi is a Software Engineer in the InfoSphere Warehouse and Netezza Technology Ecosystem team at the IBM Toronto Lab. Before that he was a Software Developer for InfoSphere Warehouse at the IBM Research & Development Lab Boeblingen, Germany. He was a developer in the data mining, text mining, and mining reporting solutions.



Saeid Modares (saeidm@ca.ibm.com), Solution Specialist, IBM

Saeid ModaresSaeid Modares is a solution specialist in the InfoSphere Optim and Guardium Technology Ecosystem team at the IBM Toronto Lab. He is the Guardium technical lead with focus on delivering solutions to new customers. His experience includes technical sales consulting, proof of concepts, implementation, and business partner enablement for InfoSphere Guardium and InfoSphere Optim products.



04 October 2012

Also available in Chinese Portuguese

Securing and hardening data environments

Building a secure and hardened data environment requires addressing all aspects of database security. Limiting data access using user and group privileges is an example of database security that requires attention for any new or existing data environments. Similarly many out-of-box settings may not provide the most optimal and secure configuration for a given data environment. Some of these settings are platform-specific, requiring expert knowledge for the best configuration. In addition to ongoing changes to an environment and frequently identified database application vulnerabilities, an assessment of all components affecting data environments security is recommended.

InfoSphere Guardium Database Vulnerability Assessment offers a solution that detects and recommends fixes for numerous database vulnerabilities, including insecure configuration and object privileges, weak passwords, and missing patches. Regular updates through Database Protection Service ensure that the tests are based on the latest industry best practices, including CIS and STIG, and vendor-released critical updates.

The product is based on the proven Guardium architecture, and runs on the secured and hardened Guardium appliance. Assessment tests and results are stored on the appliance and accessed through a secure web interface. The non-intrusive independent nature of the solution eliminates the performance impact on the data server. It also ensures a true separation of duty between database security and database administration tasks.

In addition to the comprehensive Vulnerability Assessment solution, Guardium offers Entitlement Reports for auditing the sensitive Netezza user and group entitlements. These reports provide a snapshot of user and group privileges, including access privileges, object privileges, and system privileges specific to a Netezza data warehouse environment.

A secure Netezza environment requires an initial security configuration of database and relevant operating system settings, and creation of users and groups along with access privileged assignments. This process should be based on security best practices and Vulnerability Assessment results and its recommendations. There is also a need for recurring testing and reviewing of entitlements and configuration after the initial setup. Changes to entitlement or deviation from the fine-tuned configuration must be reviewed and escalated for fixes.

The completed process of securing and hardening a data environment that has been will be applied to a Netezza data warehouse appliance. Details of Netezza and Guardium configuration will be explained along with sample scenario examples.


Basics of Netezza security

Netezza security is similar to other databases and shares the same basic concepts. The main challenges are to correctly and safely authenticate users and to set access privileges, so users can only see and modify their database objects. These two problems are solved similar to other databases, but with minor differences. One difference between Netezza and other databases is that it is an appliance that includes server hardware, operating system, and database software, so we need to talk about all components.

OS security

The OS on a Netezza data warehouse appliance is a modified Red Hat Linux®. Normally, the user should only make minimal modifications to the OS; package management is, for example, handled by Netezza support. By default, a Netezza appliance has two OS users:

  • ROOT — Should only be used for the application of Netezza firmware and OS patches
  • NZ — The owner of the database software

It is possible to create additional users, such as for development or data movement. In this case, basic Red Hat security guidelines should be followed. It is important to minimize direct access to the appliance.

User authentication

Netezza database users are managed by the database and are unrelated to the OS users. Per default, user authentication is handled by the database software itself. It is possible to use LDAP, but that is beyond the scope of this article. The Netezza database software was originally built on PostgreSQL and shares a lot of characteristics especially in the administrative features like user security. Netezza provides a variety of advanced features to restrict user access, some of which we will discuss.

Users and groups

When delivered, a Netezza appliance has a single-user ADMIN, which is the super-user of the database, who has all possible privileges and some special advantages like a reserved set of system resources. The ADMIN should only be used for initial setup and emergencies. In a normal Netezza appliance, you create a variety of users. You can create them based on their tasks, but normally, they should be associated with their real-world personas for better audit possibilities.

Netezza also supports groups used to create user roles with specific sets of privileges. There is one special group (PUBLIC), which every created user belongs to. It is always present and cannot be dropped. The PUBLIC group is a way to give privileges to all database users. You should be restrictive with the privileges given to the PUBLIC group.

Netezza databases

Each Netezza appliance has a single running instance of the Netezza Performance Software (NPS), which is the database software powering the Netezza data warehouse appliance. But it can have multiple databases. In contrast to other database systems, databases are not related to physical objects. Instead, they are logical groupings of database objects used for management and to set privileges. You would, for example, have a database associated with an application or project. Each Netezza system has a special database called SYSTEM. This database contains the system catalog, which is in some ways a global database that stands above the other databases in the system. Users should have minimal access to the SYSTEM database.

Access privileges

After a user is securely authenticated, the system needs to make sure that he can only access and modify the database objects he is supposed to. This is done similarly to other database systems through access privileges. There are two types of access privileges in Netezza, administrative and object privileges. Administrative privileges are associated with administrative tasks like backup, restore, or hardware management. They are not tied to a specific database object. Object privileges are associated with specific database objects.

Privileges can be granted to users or groups with the GRANT command. Privileges are always additive, which means that each user has all privileges granted directly to him, in addition to all privileges, which have been granted to all groups he belongs to. It is possible to remove a privilege from a group or user with the REVOKE command, but this will remove the privilege from all users belonging to that group.

Access privileges in Netezza can be local to a database or global, meaning that they apply to every database in the system. To give a privilege globally, you need to grant the privilege while connected to the SYSTEM database. To give the privilege only for a specific database, you need to connect to that database before executing GRANT.

Besides GRANT, there is a second concept in Netezza that provides privileges: object ownership. Each database object in Netezza has an owner — by default, the creator of the object. The owner has all privileges for the object. This is especially useful for databases because the owner can function as an administrator for this database without impacting other databases or the SYSTEM database.


Configuring Netezza user access and security

When you retrieve a Netezza appliance, the security settings of the OS layer normally have good defaults, so the only crucial step is to change the passwords of the OS user accounts. Therefore, we will focus on setting up database security. We will create some initial users, groups and privileges, like you would do when receiving a new Netezza appliance.

Users and groups in Netezza are global objects, so they can be seen in every database. Before creating tables and loading data, you need to create a database. Normally, a database will refer to a project or application in your warehousing environment. Our database will be called REPORTDB, and it will contain tables for a set of business intelligence reports.

We will create three users:

  • MARK — This user will be the owner of the database, making him the administrator of this database.
  • MARIA — This user will be able to modify data in the database, getting all privileges to load and modify data without the ability to change database objects.
  • KARL — This user will be a read-only user. For example, he could be used for access from a reporting software like IBM Cognos. He will be able to read data from the database, but cannot modify any database objects or data.

Instead of granting the privileges directly to these users, we will set up two groups, so we can later add and remove users at will. Since we do not foresee having more than one administrative user for this database, we will not create a special group for this role.

Here, we will create two groups:

  • REPORTRW — This is the read-write access group of this database. Members can modify and read data in the database tables.
  • REPORTRO — This is the read-only access group of this database. Members can only read data in the database and cannot modify any data.

Initially, we need to log in with the ADMIN user. You can see the connected database and the user you are logged in with in the prompt of the NZSQL console. The first thing we need to do is to create the REPORTDB:

SYSTEM(ADMIN)=> CREATE DATABASE REPORTDB;

Now we will create the three users:

SYSTEM(ADMIN)=> CREATE USER MARK WITH PASSWORD 'markspass';
SYSTEM(ADMIN)=> CREATE USER MARIA WITH PASSWORD 'mariaspass';
SYSTEM(ADMIN)=> CREATE USER KARL WITH PASSWORD 'karlspass';

After that, we create the group, adding the users during creation. We can always modify this later with the ALTER statement:

SYSTEM(ADMIN)=> CREATE GROUP REPORTRW WITH ADD USER MARIA;
SYSTEM(ADMIN)=> CREATE GROUP REPORTRO WITH ADD USER KARL;

Now we can set the necessary privileges. First, let's make MARK the owner of the database:

SYSTEM(ADMIN)=> ALTER DATABASE REPORTDB OWNER TO MARK;

MARK is now the owner of the REPORTDB and has all privileges on the database and all contained database objects. This is a convenient way to delegate admin responsibilities. MARK can handle all administrative tasks for this database without interfering with other databases. It is even possible to use workload management features to reduce the amount of system recourses a user or group can use. This can be important if you, for example, want to run some development databases on the same system as a production database.

We will now add the necessary privileges to the read-only group. We want them to be able to read from database tables in the REPORTDB database without access to any other database. To do this, we need to connect to the REPORTDB database. Any GRANT statement executed while connected to the SYSTEM database would be global, meaning it would apply to all databases in the appliance:

SYSTEM(ADMIN)=> \c REPORTDB

Netezza has a unique privilege LIST, which allows users to see objects and is needed to connect to databases. All users who work with a database need to have at least the LIST privilege on that database:

REPORTDB(ADMIN)=> GRANT LIST, SELECT ON REPORTDB TO REPORTRW, REPORTRO;

Our groups will need LIST and SELECT privileges on the tables, views and synonyms in the database. Object privileges can be given on a specific database object like a customer table or on object classes like TABLE:

REPORTDB(ADMIN)=> GRANT LIST, SELECT ON TABLE, VIEW, SYNONYM TO REPORTRO, REPORTRW;

Since we execute the GRANT command while connected to REPORTDB, it is a local privilege. This means that members of the groups can list and select from all tables in the REPORTRD database — not other databases. If we wanted to give this privilege globally for all databases, we could execute this command while being connected to the SYSTEM database. Global privileges should be given only sparingly. We will now set the privileges for the REPORTRW group:

REPORTDB(ADMIN)=> GRANT LIST, SELECT, UPDATE, DELETE, INSERT, TRUNCATE 
                        ON TABLE TO REPORTRW;

This command allows members of the REPORTRW group to freely read and modify the contents of the REPORTDB tables without changing the table structure.

We also will give the privileges needed for loading data. Loading data in Netezza requires the ability to create external tables. Sometimes during load processes, it is necessary to create temporary tables for ELT data transformations, so we will also give the privilege to create temp tables:

REPORTDB(ADMIN)=> GRANT CREATE EXTERNAL TABLE, CREATE TEMP TABLE TO REPORTRW;

Finally, we will allow our data modification users to groom tables. When rows in Netezza are deleted or updated, they are not immediately removed from disc but logically deleted. These historic row versions can be cleaned up with the GROOM command. This is perhaps the most important, but not the only, task of the GROOM command. Although this is an administrative task, it makes sense to allow the user modifying the database to also run the GROOM command. It can take up a significant amount of system resources, so it should only be executed by knowledgeable users:

REPORTDB(ADMIN)=> GRANT GROOM ON TABLE TO REPORTRW;

We have finished our basic security setup for the REPORTDB database. We have the owner of the database, who is able to modify database objects; we have a group of users allowed to modify data; and we have a group of read-only users, who can run reports and analytics. This is a classic setup, but it depends on your requirements. Netezza is a warehousing appliance, so modifications of user data are normally executed in a controlled environment by a small number of administrative users. For example in weekly loads of transactional data or through automated trickle-feed scripts. This is especially true because Netezza is extremely easy to use and only requires a small amount of administrative effort. Therefore, in many situations, the read-write group will not be necessary, and data modification can be handled by the owner of the database.

Different situations may require different setups, the development environment would, for example, need a group of power users who can modify database objects, create stored procedures, etc.

Finally, it is a good idea to create a global administrative user with most ADMIN privileges. The ADMIN user is in many regards special and should only be used in emergencies. It has, for example, a reserved set of system resources associated with it. Therefore, it is good practice to create a user with all administrative and object privileges, and use this instead of the ADMIN user for most tasks. This step is by no means necessary, though:

SYSTEM(ADMIN)=> CREATE USER NEWADMIN WITH PASSWORD 'adminpass';
SYSTEM(ADMIN)=> GRANT ALL ON AGGREGATE, DATABASE, FUNCTION, GROUP, PROCEDURE, 
                USER, TABLE, EXTERNAL TABLE, SYSTEM TABLE, SEQUENCE, 
                SYSTEM VIEW, MANAGEMENT VIEW, MANAGEMENT TABLE, SYNONYM, 
                VIEW, MATERIALIZED VIEW TO NEWADMIN WITH GRANT OPTION;
SYSTEM(ADMIN)=> GRANT all ADMIN TO NEWADMIN WITH GRANT OPTION;

Configuring and running Vulnerability Assessment

Vulnerability Assessment runs on the Guardium appliance. The appliance can be deployed as a hardware appliance or a virtual appliance, as a VMware virtual machine. This does not affect configuration or functionality of the Vulnerability Assessment. For details on how to install and deploy the Guardium appliance, refer to the installation guide.

It is assumed that a Guardium appliance with basic configuration is available. Guardium appliance basics, such as how to access the appliance, are also assumed to be known.

Vulnerability Assessment can be accessed through the appliance's web interface. Log in to the web interface and select the Assess/Harden tab, followed by the Vulnerability Assessment tab. As shown in Figure 1, configuration and results of assessment can accessed through this portal.

Figure 1. Vulnerability Assessment interface
Image shows Vulnerability Assessment interface

Vulnerability Assessment assesses database configuration and user entitlement. The information about these aspects of the database is available from system tables, or files and scripts accessible at an OS level. Information stored in system tables can be accessed through a database connection, provided valid credentials. Configuration available through scripts and files would require access at an OS level with elevated privileges.

To provide access at the OS level, the Configuration Audit System (CAS) is deployed. The CAS client, a small software agent installed on the data server, periodically polls relevant configuration files or OS script output and sends data to the CAS server running on the Guardium appliance. This information is used by Vulnerability Assessment to offer a comprehensive assessment of a database system.

The CAS client agent has a simple command-line installation. The installation is performed by invoking the installation script and providing a path to the Java™ and install directories. Note that the Java Runtime Environment must be installed on your Netezza appliance. The installation must be run under the root account. Figure 2 shows an example of the CAS client agent installation.

Figure 2. CAS installation
Image shows CAS installation

After the installation, the guard_tap.ini configuration file needs to be edited. This can be found in the CAS install directory under %cas_install_dir%/etc/guard_tap.ini. Two entries must be edited: sqlguard_ip, which is the IP address of the Guardium appliance; and tap_ip, which is the IP address of the host Netezza appliance. These parameters are initially set to NULL. In Figure 3, sqlguard_ip and tap_ip entries of guard_tap.ini is edited to valid IP addresses.

Figure 3. Editing guard_tap.ini configuration file
Image shows editing guard_tap.ini configuration file

To verify the status of the CAS client agent, log in as admin and check the CAS Status under Local Taps in the Administration Console. Figure 4 shows the status of the CAS client agent that was installed and configured.

Figure 4. CAS status
Image shows CAS status

We shall now proceed to configure the Vulnerability Assessment. While logged in as a Guardium user, access the Vulnerability Assessment application by clicking the Assess/Harden tab, then Vulnerability Assessment, then define what database you want to assess.

At the Security Assessment Finder panel, click New. This brings up the Security Assessment Builder, as shown in Figure 5.

Figure 5. Security Assessment Builder
Image shows Security Assessment Builder

Enter a suitable description. You may ignore the observed test parameters. Observed tests use the audit data, which are only available if database activity monitoring is being performed using the S-TAP software agent and a suitably installed security policy. For this scenario, it is assumed that Guardium Database Activity Monitoring (DAM) is not deployed on the Netezza appliance.

In order for the database assessments to be run, a data source with valid credentials is required. To configure the data source, click Add Datasource. In the Datasource Finder panel, click New. Enter database credentials for a user with access to system tables. Guardium provides a script that creates a Netezza user with the minimum required privileges needed to run the Vulnerability Assessment tests. These scripts are available for download as part of Database User Role Definitions package. Figure 6 shows a sample datasource definition.

Figure 6. Datasource definition
Image shows Datasource definition

In addition to the database connection, we need to specify which database and OS objects are to be monitored by CAS. The data from CAS monitored items will be used in assessment tests. Guardium provides a set of items for each data environment to be monitored by CAS. These sets are referred to as templates.

To apply a CAS template, click CAS Support in the Security Assessment Builder panel. With the CAS Assessment Support opened, select UNIX/Netezza Assessments from the Select a Template Set drop-down menu, then click Add. The template set will be added under the current-instance monitored section, as shown in Figure 7. Click Save to save the configuration.

Figure 7. CAS Assessment Support
Image shows CAS Assessment Support

At this stage, we are ready to select and configure tests to be run against the Netezza appliance.

There are numerous tests that can be selected to be run as part of the assessment. Clicking Configure Tests in the Security Assessment Builder will bring up the Assessment Test Selections panel. There are sets of tests for each database platform. Choosing a test and click Add Selection to add the test to the Tests for Security Assessment section at the top. Figure 8 shows all Netezza-specific tests selected for assessment.

Figure 8. Assessment test selection
Image shows CAS Assessment test selection

Some tests can be tuned to override the default setting or add exceptions for user/group privilege assessment. For example, tuning Global admin privilege granted to users and/or groups offers the option of assigning an exception group, as shown in Figure 9. This allows us to specify a list of users we choose to have the global admin privilege — in this example, the Netezza Trusted Users. These users with global admin privilege will, therefore, not trigger a failure of this assessment test.

Figure 9. Tuning assessment test
Image shows Tuning assessment test

We will choose the default setting for all tests and run the assessment. To run the assessment, select the vulnerability assessment created in the Security Assessment Finder (shown in Figure 10) and choose Run Once Now.

Figure 10. Running assessment
Image shows Running assessment

The status of the assessment is displayed under the Guardium Job Queue, shown in a panel to the right of the Security Assessment Finder. Figure 11 shows an assessment in progress. The status will change to "Complete" when the assessment is finished.

Figure 11. Assessment in Guardium Job Queue
Image shows Assessment in Guardium Job Queue

After the assessment completes, select the assessment in the Security Assessment Finder and click View Results. Figure 12 shows an example of a Vulnerability Assessment report.

Figure 12. Assessment results
Image shows assessment results

A passing score is given based on the assessment result. Each test receives a pass or fail, or is labeled as error if the test could not be completed. Failed tests also include a recommendation on how to fix the issue causing the test to fail. Next, we will address number of these failed tests in order to improve the Netezza appliance's security and reduce its vulnerability.


Resolving Netezza appliance vulnerabilities

Here, we discuss and resolve some of the vulnerabilities identified by the Guardium Vulnerability Assessment. Depending on your environment, you may not need to resolve all encountered vulnerabilities and can define exceptions instead.

Password length

The first security problem Guardium is highlighting is a missing minimum password length. A Netezza appliance comes with good default security settings, but in an environment with a large number of users and wider access, it may be necessary to implement a set of password controls.

Figure 13. Password length
Image shows Password length

Netezza provides the ability to:

  • Enforce minimum password length
  • Enforce password complexity
  • Lock a user account after a number of failed login attempts
  • Enforce password change after a specified time period
  • Restrict user access to specific time periods

User authentication can also be handed over to an LDAP server — to have consistent password controls for all user accounts, for example. But this would go beyond the scope of this article.

As discussed, Netezza is built on the PostgreSQL database, so it is no wonder that you will find many of the needed changes in the PostgreSQL configuration files. To change the minimum password length, you need to connect to the Netezza host as the user nz. Navigate to the folder containing the configuration files, log files, system catalog etc.: cd /nz/data.

We now need to modify the postgresql.conf file. For example, with VI, you will find the parameter near the end of the file:

#password_length = 4      # minimum value is 4

Uncomment the parameter and set the value to a minimum of 8 to satisfy the Guardium requirements:

password_length = 8      # minimum value is 4

After saving the file, you need to restart the Netezza database software to enable the setting:

[nz@netezza data]$ nzstop;nzstart

Invalid attempts

The second setting Guardium detects as a vulnerability is the invalid attempts setting. Enforcing this ensures that users are locked out after a set number of wrong login attempts. This makes sure that users with unsecure passwords cannot be guessed in a brute-force attack.

Figure 14. Invalid attempts
Image shows invalid attempts

The setting can be found in the same configuration file postgresql.conf, similar to the minimum password length. Per default the setting is disabled:

#invalid_attempts = 0     # zero - no limit

Uncomment the line by removing the # character and change the value to 3 to satisfy the Guardium requirements:

invalid_attempts = 3     # zero - no limit

As before, you need to restart the Netezza appliance to enable this setting:

[nz@netezza data]$ nzstop;nzstart

If a user should enter a wrong password three times, the account is locked and needs to be unlocked by an administrator with the following command:

SYSTEM(ADMIN)=> alter user %username% reset account;

Password authentication does not use clear text

Figure 15. Password authentication does not use clear text
Image shows that password authentication does not use clear text

This finding is actually more complex. Netezza allows you to specify different connection types for different IP ranges. You can specify if passwords should be transmitted in clear-text or hashed, and you can specify if an IP range has to connect using a secure encrypted SSL connection. The connection types are stored in the /nz/data/pg_hba.conf file and could be directly edited there. But this is discouraged. Instead, you should use the SQL admin commands Netezza provides to manage the connection types.

To display the currently defined connections, you need to connect to the Netezza appliance as an administrator. Run the following command to display all available connection types:

SYSTEM(ADMIN)=> show connection;

On a new system, you should see something like the following:

SYSTEM(ADMIN)=> show connection;
CONNID | CONNTYPE | CONNDB | CONNIPADDR |   CONNIPMASK    | CONNAUTH
-------+----------+--------+------------+-----------------+----------
     1 | local    | all    |            |                 | trust
     2 | host     | all    | 127.0.0.1  | 255.255.255.255 | password
     3 | host     | all    | 0.0.0.0    | 0.0.0.0         | md5
(3 rows)

It is possible to define multiple connection types for the same IP range. In this case, the first fitting entry would be used. In our example, we have three types: an internal local type, a clear-text password authentication for users from localhost, and an md5 hash authentication for users from outside the machine.

You can disregard the local connection since this is used internally by Netezza. Guardium is complaining about the clear-text (password) authentication from localhost in connection type 2. This is no apparent security risk since no password is transmitted over the network. We could, therefore, define an exception for this vulnerability.

The alternative is to change the connection type for connection 2 to md5 as well. We will do this to demonstrate how to work with connection types. It is important to keep the order of connections in mind. The first connection from the top that fits the IP address of the incoming call will be applied. It is important to have more restricting connection types first. The second problem is that there is no ALTER CONNECTION TYPE command. So it is not possible to update the second connection type. We can only delete it and add it again. This would change the order of the connections.

To keep the order of connections and update connection type 2, we need to drop and recreate connection type 3 to keep the order intact. Drop connection types 2 and 3:

SYSTEM(ADMIN)=> drop connection 3;
SYSTEM(ADMIN)=> drop connection 2;

Now recreate connection type 2 with the changed CONNAUTH parameter to md5:

SET CONNECTION HOST DATABASE 'all' IPADDR '127.0.0.1' IPMASK '255.255.255.255' AUTH md5;

And recreate connection type 3 as it was before:

SET CONNECTION HOST DATABASE 'all' IPADDR '0.0.0.0' IPMASK '0.0.0.0' AUTH md5;

You have now changed the authorization to MD5 for all connections — even local ones.

Figure 16. Object privileges granted to public
Image shows Object privileges granted to public

The PUBLIC group in Netezza is a special user group that contains all users of the system. Any privileges added to this group are available to all users. Normally, you should have as few privileges as possible associated with the PUBLIC group. To see which privileges are associated with the PUBLIC group, execute the following command from nzsql:

SYSTEM(ADMIN)=> \dpg PUBLIC
Group object permissions for group 'PUBLIC'
Database Name |           Object Name            | L S I U D T L A D B L G O E
C R X A | D G U T E X Q Y V M I B R C S H F A L P N S
---------------+----------------------------------+-----------------------------
--------+---------------------------------------------
 GLOBAL        | _V_JDBC_TABLETYPES2              | X X
        |
 GLOBAL        | _V_GROUP_PRIV                    | X X
        |
 GLOBAL        | _V_USER_PRIV                     | X X
                ...

We can see that by default, a large number of views are readable by the PUBLIC group. Some of these seem to contain sensitive information like table names, user privileges, and even queries run in the system. This would be a clear security violation since queries could contain sensitive information — in WHERE conditions, for example. However, when you select data from the views, Netezza ensures that no user has access to information he shouldn't see. The views return different information for each user and will not show information a user shouldn't have access to.

The views are needed for the connection with JDBC, ODBC, etc. and for the use of the NZAdmin graphical user client and should not be removed from the PUBLIC group. We will add an exception for these objects to the Guardium VA. This is advisable since we still want to get warned about any other privilege added to the PUBLIC group and might provide access to sensitive information to every user.

To view complete list of objects that failed this test, click Object privileges granted to public test title.

Figure 17. Object privileges granted to public
Image shows Object privileges granted to public

To create an exception for these objects, choose the Assessment Test Tuning for the Object privileges granted to public (Netezza) test from the Assessment Test Selection list. Select Netezza Allowed Grants to Public from the Exception group drop-down menu.

Figure 18. Test tuning
Image shows Test tuning

Clicking the small icon to the right of the drop-down menu opens the group editor. Add the objects from the list shown in Figure 22 to this group, as shown in Figure 24. Save the test turning with the added exception group and rerun the Vulnerability Assessment test. Objects added to the group will be excluded from the test, but other objects that fail this test will be flagged.

Figure 19. Exception group
Image shows Exception group

We have had a look at some of vulnerabilities Guardium identifies in an empty system. Some of these can be easily fixed; others are not real vulnerabilities and can be put on an exception list. After these initial modifications, we can run the vulnerability assessment report on a regular basis and will get notified about any vulnerability added to the system.


Auditing Netezza use entitlements

Vulnerability Assessment detects insecure user entitlements and makes recommendations on improving user and group privileges. There is however a need to continuously audit the user entitlements. Guardium Entitlement Reports simplify reviewing and auditing of various user privileges. Entitlement reports can be accessed through the View tab.

Figure 20. DB entitlement reports
Image shows DB entitlement reports

Following is a list of Netezza-specific DB entitlement reports:

  • Netezza Obj Privs Granted
  • Netezza Admin Privs by DB Username
  • Netezza Obj Privs by DB Username
  • Netezza Obj Privs by Group
  • Netezza Admin Privs Granted
  • Netezza Group/Role Granted to User
  • Netezza Global Admin Privs To Users and Groups
  • Netezza Global Obj Privs To Users and Groups
  • Netezza Admin Privs By DB Username Group
  • Netezza Admin Privs by Group

Entitlement information is based on data from relevant system tables. Therefore, to populate the entitlement reports, a data source with appropriate credentials to access these systems tables is required. In order to configure the uploading of data, open the Build Reports tab under the Monitor/Audit tab and click Custom table builder.

Figure 21. Reporting building options
Image shows Reporting building options

Select a desired entitlement report from the list and click Upload Data.

Figure 22. Custom tables
Image shows Custom tables

Add a data source by clicking Add Datasource and selecting an existing data source or creating a new data source by entering credentials with access to relevant systems tables. Use the Scheduling tool to schedule data import frequency, or click Run Once Now for ad-hoc data upload. The figure below shows a sample configured Import Data panel.

Figure 23. Uploading data
Image shows Uploading data

The report will be populated with data from the Netezza appliance. The figure below shows an example of the Netezza Group/Role Granted to User report populated with sample entitlement data.

Figure 24. Populated entitlement report
Image shows Populated entitlement report

We are now able to audit the user configuration and ensure that any changes made are acceptable.


Conclusion and outlook

In this article, we have explained the basic concepts of securing a Netezza appliance with Guardium. We have described the basic Netezza security model and have provided an example for an initial security setup. We then configured and executed a Guardium Vulnerability Assessment of the Netezza appliance, and discussed and solved a number of the identified vulnerabilities. Finally, we went through the Guardium entitlement reports.

Together, vulnerability assessments and entitlement reports allow a company to monitor and identify potentially dangerous changes to the Netezza security setup that might put sensitive customer information at risk. The article has covered the basic aspects of securing a Netezza appliance with Guardium. More advanced topics would be implementing LDAP authentication, row-level security or SSL encryption in Netezza, and the implementation of auditing and more advanced Guardium features, such as S-GATE Termination for restricting access to sensitive information.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Security
ArticleID=838724
ArticleTitle=Secure and harden Netezza data warehouse appliance with InfoSphere Guardium
publish-date=10042012