Answer

Once you have installed the IBM Netezza SQL Extensions using the admin account, you must provide NPS database users with access to the toolkit functions. This document explains how to grant the minimum access privileges required for using the IBM Netezza SQL Extensions.

The following steps assume that you have successfully installed the IBM Netezza SQL Extensions as described in the document referenced below.

1. Log in as the admin user and validate that the IBM Netezza SQL Extensions is installed.

[nz@host1]$ nzsql -db DB1
DB1(ADMIN)=> select regexp_version();

                      REGEXP_VERSION

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

IISI XML / Regular Expression Library Version 1.2 Build ()

(1 row)

2. For this example, the USR1 user account is requesting access. Validate the existence of USR1 and the current permissions assigned to the account.

DB1(ADMIN)=> \du USR1
List of Users
USERNAME | VALIDUNTIL | ROWLIMIT | SESSIONTIMEOUT | QUERYTIMEOUT | DEF_PRIORITY | MAX_PRIORITY | USERESOURCEGRPID | USERESOURCEGRPNAME | CROSS_JOINS_ALLOWED
----------+------------+----------+----------------+--------------+--------------+--------------+------------------+--------------------+---------------------
USR1     |            |        0 |              0 |            0 | NONE         | NONE         |          1826459 | GRP1               | NULL
(1 row)
DB1(USR1)=> select regexp_version()
DB1(USR1)-> ;
ERROR: EXECUTE FUNC: permission denied.
USR1 does not have permission to execute functions.

3. Log in as the admin user and grant execute permission for the FUNCTION object type.

[nz@host1]$ nzsql -db DB1
DB1(ADMIN)=> grant execute on FUNCTION to usr1;

GRANT>

4. Validate the grant by connecting to DB1 as user USR1.

[nz@host1]$ nzsql -db DB1 -u USR1 -pw usr1
DB1(USR1)=> select regexp_version();

                      REGEXP_VERSION

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

IISI XML / Regular Expression Library Version 1.2 Build ()

(1 row)

DB1(USR1)=>

Please refer to the IBM Netezza SQL Extensions User’s Guide  for further information on supported functions.