Required Database Permissions

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.

Click on the appropriate link to jump to the database permissions for that selection:

DB2 for Linux, UNIX, and Windows interface

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.

Create the Optim Directory and DB Aliases

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.

  • CONNECT
  • CREATETAB
  • IMPLICIT_SCHEMA
  • BINDADD
  • CREATE_NOT_FENCED_ROUTINE

To create a DB Alias, the following authorizations are needed:

  • CONNECT
  • BINDADD
  • IMPLICIT_SCHEMA
  • CREATE_NOT_FENCED_ROUTINE

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.

DB2 for z/OS

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:

  • GRANT BINDADD TO userid
  • GRANT CREATE ON COLLECTION * TO userid

To create and load the sample tables or data privacy tables for DB2 for z/OS, you must have the following authorizations:

  • GRANT USE OF TABLESPACE <tblspace> TO <userid>
  • GRANT USE OF BUFFERPOOL <bpname> TO <userid>

Informix

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

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:

  1. Connect to the database as
    • the System Administrator (SA), or
    • a user account with ’sysadmin’ role, or
    • a user account with dbo alias.

    The account used to connect to the database may be different from the owner ID for the Optim directory tables.

  2. Ensure that the owner ID for the Optim directory tables is a valid user account for the database and has a LOGIN to the database server. The owner ID can be different from the account used to connect. You can use the special SQL Server ID, dbo, as the owner ID of the directory tables and related stored procedures.
  3. If the user account that corresponds to the owner ID for the Optim directory tables does not have SA role, the user account must have the following permissions:

    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.

To create a DB alias in SQL Server, you must have two user accounts:
  • An account to connect to the database and store the procedures. CREATE PROCEDURE is the minimum permission required for this account.
  • An account to own the procedures. READ ONLY is the minimum permission required for this account which, for simplicity, you can assign to the sysadmin role. Beginning with SQL Server 2005, if you do not assign the sysadmin role, this account must have the VIEW ANY DEFINITION permission. Alternatively, you can use VIEW DEFINITION permission at the database level rather than the server level.

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.

Netezza

Needed Netezza authorizations are:

System View: List, Select

Procedure: Drop, Execute, Alter, List

System Table: List, Select

Oracle

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.

  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE SESSION
  • UNLIMITED TABLESPACE
  • SELECT ANY DICTIONARY

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.

PostgreSQL

The Optim solution must retrieve certain information from the PostgreSQL catalog, so users must be given SELECT access to the following catalog tables:

  • pg_attrdef
  • pg_attribute
  • pg_class
  • pg_constraint
  • pg_collation
  • pg_database
  • pg_depend
  • pg_description
  • pg_enum
  • pg_index
  • pg_namespace
  • pg_proc
  • pg_range
  • pg_statistic
  • pg_tablespace
  • pg_trigger
  • pg_type

Sybase ASE

To create a DB Alias in Sybase ASE, the following must be true:

  1. You must connect to the database as
    • System Administrator (SA),
    • a user account with SA role,
    • a user account with a dbo alias, or
    • a user account with CREATE PROCEDURE permission.

    The account used to connect to the database can be different from the Owner ID for the procedures used to access the system tables.

  2. The owner ID for the procedures used to access the system tables must be a valid user ID for the database and must have a LOGIN to the database server. If wanted, you can specify the special Sybase ID of dbo as the owner ID of the stored procedures. The Owner ID may be different from the ID used to connect.
  3. If the owner ID for the procedures used to access the system tables does not have the SA role, the user account must have the CREATE PROCEDURE permission.

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.

Teradata

SELECT authorization for the Optim user ID on the Teradata system is required for the following tables and views:

Catalog Tables
  • DBC.UDFINFO
  • DBC.UDTCAST
  • DBC.UDTINFO
  • DBC.IDCOL
Catalog Views
  • DBC.ALL_RI_CHILDREN
  • DBC.ALL_RI_CHILDRENV
  • DBC.CHARTRANSLATIONS
  • DBC.CHARTRANSLATIONSV
  • DBC.COLUMNS
  • DBC.COLUMNSV
  • DBC.DATABASES
  • DBC.DATABASESV
  • DBC.DATABASES2
  • DBC.DATABASES2V
  • DBC.FUNCTIONS
  • DBC.FUNCTIONSV
  • DBC.INDEXCONSTRAINTS
  • DBC.INDEXCONSTRAINTSV
  • DBC.INDICES
  • DBC.INDICESV
  • DBC.SHOWCOLCHECKS
  • DBC.SHOWCOLCHECKSV
  • DBC.SHOWTBLCHECKS
  • DBC.SHOWTBLCHECKSV
  • DBC.TABLES
  • DBC.TABLESV
  • DBC.TABLES2
  • DBC.TABLES2V
  • DBC.TRIGGERS
  • DBC.TRIGGERSV


Feedback