Configuring Netezza user access and security - IBM

newshumansvilleData Management

Dec 16, 2012 (4 years and 7 months ago)

3,890 views

© Copyright IBM Corporation 2012
Trademarks
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
1
of
28
Secure and harden Netezza data warehouse
appliance with InfoSphere Guardium
Benjamin Leonhardi
(
benleon@ca.ibm.com
)
Software Engineer
IBM
Saeid Modares
(
saeidm@ca.ibm.com
)
Solution Specialist
IBM
Skill Level: Intermediate
Date: 04 Oct 2012
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.
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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
2
of
28
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
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
3
of
28
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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
4
of
28
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
5
of
28

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
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
6
of
28
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
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
7
of
28
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
.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
8
of
28
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
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
9
of
28
Figure 2. 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
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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
10
of
28
Figure 4. 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
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
11
of
28
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
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
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
12
of
28
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
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
13
of
28
Figure 8. 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
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
.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
14
of
28
Figure 10. 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
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
15
of
28
Figure 12. 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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
16
of
28
Figure 13. 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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
17
of
28
Figure 14. 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
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:
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
18
of
28
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
19
of
28
Figure 16. 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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
20
of
28
Figure 17. 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
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
21
of
28
Figure 19. 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.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
22
of
28
Figure 20. 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
.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
23
of
28
Figure 21. Reporting building options
Select a desired entitlement report from the list and click
Upload Data
.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
24
of
28
Figure 22. 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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
25
of
28
Figure 23. 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
We are now able to audit the user configuration and ensure that any changes made
are acceptable.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
26
of
28
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.
ibm.com/developerWorks/
developerWorks®
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
27
of
28
Resources
Learn

The
InfoSphere Guardium website
includes links to whitepapers, demos, and
more.

A new
developerWorks community for InfoSphere Guardium
is evolving to
include links to relevant technical content, industry-specific information, and
FAQs. Join the community and help it grow.

The
InfoSphere Guardium Information Center
includes many how-tos to help
you make the most of InfoSphere Guardium data activity monitoring solution.

The
InfoSphere BigInsights Information Center
includes topics on using word
count and default ports.

Watch a section of videos on the
InfoSphere Guardium YouTube channel
,
including demos of support for SAP, DB2 for z/OS, and others.

Learn more about Information Management at the
developerWorks Information
Management zone
. Find technical documentation, how-to articles, education,
downloads, product information, and more.

Stay current with
developerWorks technical events and webcasts
.

Follow
developerWorks on Twitter
.
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

Participate in the discussion forum for this content.

Get involved in the
Guardium users group on LinkedIn
to ask questions and get
advice from other users.

Check out the
developerWorks blogs
and get involved in the
developerWorks
community
.
developerWorks®
ibm.com/developerWorks/
Secure and harden Netezza data warehouse appliance
with InfoSphere Guardium
Page
28
of
28
About the authors
Benjamin Leonhardi
Benjamin 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
Saeid 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.
© Copyright IBM Corporation 2012
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)