User account privileges

To create user-defined functions, aggregates, and shared libraries, you must be able to log in as the admin user account, or your user account must have the respective Create Function, Create Aggregate, or Create Library administration privilege. You must also have List privileges to the target database and schema where you plan to create the UDX. Note that if you have List access to a database, you implicitly have access to the default schema of that database. If the database has multiple schemas, you must be explicitly granted List access to a non-default schema.

To change a user-defined function, aggregate, or shared library, your account must have the Alter privilege for the function, aggregate, or library objects or for the specific UDX.

As a best practice, do not use the admin user account to create and manage user-defined objects. Create special user accounts or groups, and give them the necessary privileges to create, manage, or run user-defined objects. The admin user has root-like access to all the Netezza Performance Server databases and objects and special privileges for work priority. The database owner has full privileges to all objects in the database, and for systems that support multiple schemas, the schema owner has full privileges to all objects in the schema. Restrict access to the admin account and use it only for the critical work or tasks on the system. This publication uses a sample user account named myuser to do the sample tasks and examples.

To use a user-defined function or aggregate in your SQL queries, you must be the admin user, or your user account must have Execute privilege for the function or aggregate objects, or for the specific UDF or UDA.

If the UDF or UDA uses shared libraries, you must have Execute access to the shared libraries. You must be the admin user, or your user account must have Execute privilege for library objects or for the specific shared library. If your account has Unfence privilege, you can create or alter a UDX to run in unfenced mode, which can offer improved performance for the UDX.

You can use any SQL tool that supports ODBC, JDBC, OLE DB, and nzsql to create, modify, and run Netezza Performance Server UDXs. This publication provides examples that use the nzsql command-line tool. Any SQL tool that can connect to the Netezza Performance Server database can be used to run queries that include UDXs.