The user account used to configure Optim™ requires specific database permissions (for example, to allow tables and procedures to be created). Use this information to determine the required permissions for the database management systems that Optim supports.
The DB2 for Linux, UNIX, and Windows interface used to validate a user account uses restricted APIs. Starting with Windows 2000, DB2 for Linux, UNIX, and Windows provides a Windows service called DB2 Security Server (db2sec.exe). This program must be started on any machine (client or server) on which a user account must be validated. For client machines, this service is necessary only if any connected instance requires client authentication. During installation of a DB2 for Linux, UNIX, and Windows product, this service is registered with Windows. It is removed during uninstall.
By default, the DB2 Security Server starts automatically when Windows starts. You can start it manually using the Service dialog from the Windows Control Panel or you can enter the following command at the Command Line Interface:
NET START DB2NTSECSERVER
You can stop the service manually using the Service dialog or you can enter the following command at the command line interface:
NET STOP DB2NTSECSERVER
If you want to start the service manually at system startup, use the Service dialog in the Windows Control Panel to change the service startup options.
When you create a DB alias or apply maintenance to an existing DB alias, the DB2 for Linux, UNIX, and Windows client software on the workstation must be at the same or higher level as the target database.
Any version of the DB2 for Linux, UNIX, and Windows client can connect to the next older version of the database or the next two more recent versions. IBM® does not support a client/server configuration that includes an out-of-service version, however. For example, DB2 UDB version 7 clients connecting to a DB2 UDB version 8 server is not supported after version 7 is withdrawn from service.
To create an Optim directory, specify the following authorizations. However, if you require more stringent security than that offered by these authorizations and you want to assign more granular permissions, see Minimum database privileges to create and maintain the Optim Directory and DB Aliases for DB2 for linux, UNIX, and Windows.
To create a DB Alias, the following authorizations are needed:
When the plans are bound for the Optim directory and the System Catalog, you can specify a grant authorization ID. When this ID is PUBLIC (the default value), all users are able to run Optim. Optionally, you can specify a user account or group name to limit access to specific users.
You need certain authorizations to create a DB alias, and create and load sample or data privacy tables for DB2 for z/OS.
To create a DB alias for DB2 for z/OS, the following authorizations are needed:
To create and load the sample tables or data privacy tables for DB2 for z/OS, you must have the following authorizations:
The Informix utility program, SELNET 32, includes an environment variable named IFX_AUTO_FREE. This variable must not be set. If the IFX_AUTO_FREE variable has a value, the configuration program fails during the creation of the Optim directory with the error, -481 SQL State 37000 Invalid Statement Name. To avoid or correct the error, ensure that the IFX_AUTO_FREE variable is not set.
Optim uses the ODBC module ISQLT09A.DLL to connect to an Informix server. This module is installed as part of the Informix Client SDK 2.2. This SDK must be installed on the workstation for Optim to communicate with an Informix database. (You can download the Informix Client SDK for free from the Informix website.)
To create and access an Optim directory and a DB alias, the user account must be defined on the server (that is, the operating system). The user account must be configured in uppercase for an ANSI database and in lower case for a non-ANSI database. (The server is not case-sensitive.) To create the Optim directory or a DB alias, the user account must have the RESOURCE privilege. DBA privilege includes RESOURCE privilege.
In some cases, however, Informix requires that the user account used to create the stored procedures (or tables) match the stored procedure qualifier (or table owner ID). This is true even if the user account has DBA privilege. This rule also applies when creating the sample tables or data privacy tables, since a GRANT is issued as part of the creation process.
When you catalog procedures for the Optim directory and the system tables, you can specify a grant authorization ID. When this ID is PUBLIC (the default value), all users are able to run Optim. Optionally, you can specify a user account or group name to limit access to specific users.
To access an existing Optim directory or a DB Alias, a user must have CONNECT privilege. Both DBA and RESOURCE privileges include CONNECT privilege.
Microsoft SQL Server support requires that the user have a login at the database server level and a user account for the database instance that is accessed. This is true for both creating and accessing an Optim directory and a DB Alias.
If shared (global) stored procedures are used for DB Aliases, the user account used to create the stored procedures must have system administrator privileges (sysadmin).
To create the Optim directory in SQL Server:
The account used to connect to the database may be different from the owner ID for the Optim directory tables.
CREATE TABLE
CREATE PROCEDURE
When you catalog the procedures for the Optim directory and the system tables, you can specify a grant authorization ID. When this ID is PUBLIC (the default value), all users are able to run Optim. Optionally, you can specify a user account or group name to limit access to specific users.
VIEW ANY DEFINITION is a server level permission that must be granted while the current database is "master." Here is an example.
use master
grant VIEW ANY DEFINITION to owneridforprocedures
To
create sample or privacy tables while in the DB Alias dialog, the
connecting account must have CREATE TABLE authorization. If, while
creating the DB alias, you plan to connect using the procedure-owning
account, be sure this account has the appropriate permissions. Needed Netezza authorizations are:
System View: List, Select
Procedure: Drop, Execute, Alter, List
System Table: List, Select
When you use the configuration program to create the Optim directory tables and procedures, create a DB alias, and load the sample tables for an Oracle database, specify the following permissions.
If you require more stringent security than that offered by these authorizations and you want to assign more granular permissions, see Minimum database privileges to create and maintain the Optim Directory and DB Aliases for Oracle.
The SELECT ANY DICTIONARY permission can be granted to PUBLIC to satisfy the requirement. If the Oracle Initialization parameter 07_DICTIONARY_ACCESSIBILITY is set to TRUE, the SELECT ANY TABLE permission can be used instead of the SELECT ANY DICTIONARY permission.
The permissions cannot be revoked for the user account once the Optim directory or DB alias is created. Oracle packages are run under the permissions of the user account that created them. If any required permissions are revoked, the packages become invalid when executed.
When you create the packages for the Optim directory and the Data Dictionary, you can specify a grant authorization ID. When this ID is PUBLIC (the default value), all users can run Optim. Optionally, you can specify a user ID or group name to limit access to specific users.
The Optim solution must retrieve certain information from the PostgreSQL catalog, so users must be given SELECT access to the following catalog tables:
To create a DB Alias in Sybase ASE, the following must be true:
The account used to connect to the database can be different from the Owner ID for the procedures used to access the system tables.
When you catalog the procedures for the Optim directory and the system tables, you can specify a grant authorization ID. When this ID is PUBLIC (the default value), all users can run Optim. Optionally, you can specify a user account or group name to limit access to specific users.
SELECT authorization for the Optim user ID on the Teradata system is required for the following tables and views: