Required minimum database permissions

When the database is created with the restrictive option, the user account used to configure your Optim™ solution requires specific minimum database permissions (for example, to allow tables and procedures to be created). Use this information to determine the required permissions for the supported database management systems.

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

The DB2 database can be created using the “restrictive” option of the “create database” command. This option limits Catalog access and also limits other privileges, such as binding packages against the database. This approach might be preferred by clients who are looking for granular privileges to accomplish Optim functions, without granting DBADM, IMPLICIT_SCHEMA, BINDADD, CREATE_NOT_FENCED_ROUTINE, CREATE VIEW, and other privileges.

The optimdirowner user in the following list is the owner of the Optim directory in the DB2 database. The optimdirowner user is created at the OS level and does not belong to the administrator group. (Replace optimdirowner with the appropriate value for your environment.)

The following minimum system privileges are required to create and maintain the Optim directory on a DB2 database:

  • GRANT connect on database to optimdirowner
  • GRANT execute on package NULLID.SYSSH200 to optimdirowner
  • GRANT execute on package NULLID.SYSSTAT to optimdirowner
  • GRANT SELECT on SYSIBM.SYSNODEGROUPDEF TO optimdirowner
  • GRANT SELECT on SYSIBM.SYSTABLESPACES TO optimdirowner
  • GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD to optimdirowner
  • GRANT createtab on database to optimdirowner
  • GRANT createin on schema optimdirowner to optimdirowner (optimdirowner is used as the schema name when the schema is created)
  • GRANT use of tablespace USERSPACE1 to optimdirowner
  • GRANT bindadd on database to optimdirowner
  • GRANT SELECT on SYSCAT.DATATYPES to optimdirowner

For DB2 9.7, the following privilege is also needed:

  • GRANT execute on package NULLID.SQLABH02 to optimdirowner

For DB2 9.7 fix levels 0 and 1, the following privilege is also needed:

  • GRANT execute on package NULLID.SQLC2H20 to optimdirowner

For DB2 9.7 fix levels 2, 3, 3a and 4, the following privilege is also needed:

  • GRANT execute on package NULLID.SQLC2H21 to optimdirowner

For DB2 9.7 fix level 5, the following privilege is also needed:

  • GRANT execute on package NULLID.SQLC2H22 to optimdirowner

For DB2 9.7 fix level 6, the following privilege is also needed:

  • GRANT execute on package NULLID.SQLC2H23 to optimdirowner

For DB2 10.1, the following privileges are also needed:

  • GRANT execute on package NULLID.SQLC2J23 to optimdirowner
  • GRANT execute on package NULLID.SQLABJ02 to optimdirowner

For DB2 10.5, the following privileges are also needed:

  • GRANT execute on package NULLID.SQLC2K26 to optimdirowner
  • GRANT execute on package NULLID.SQLABK02 to optimdirowner

For DB2 11.1, the following privileges are also needed:

  • GRANT execute on package NULLID.SQLC2O26 to optimdirowner
  • GRANT execute on package NULLID.SQLABO02 to optimdirowner

Create the Optim DB aliases

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

  • CONNECT
  • BINDADD
  • IMPLICIT_SCHEMA
  • CREATE_NOT_FENCED_ROUTINE (Only needed when registering user-defined functions (UDFs) as "not fenced")

Additional privileges to create and maintain Optim DB aliases for DB2 for Linux, UNIX, and Windows

You can grant the following individual privileges to create and maintain Optim DB Aliases. These CATALOG OBJECT privileges must be granted by a user with DBA privileges. (In the following list, optimdirowner is the owner of the Optim directory in the DB2 database. Replace optimdirowner with the appropriate value for your environment.)

  • GRANT select on SYSIBM.SYSTABLES to optimdirowner
  • GRANT select on SYSIBM.SYSSECURITYPOLICIES to optimdirowner
  • GRANT select on SYSIBM.SYSCOLUSE to optimdirowner
  • GRANT select on SYSIBM.SYSCHECKS to optimdirowner
  • GRANT select on SYSIBM.SYSTABCONST to optimdirowner
  • GRANT select on SYSIBM.SYSTABOPTIONS to optimdirowner
  • GRANT select on SYSIBM.SYSDATAPARTITIONEXPRESSION to optimdirowner
  • GRANT select on SYSIBM.SYSDATAPARTITIONS to optimdirowner
  • GRANT select on SYSIBM.SYSTRIGGERS to optimdirowner
  • GRANT select on SYSIBM.SYSTABLESPACES to optimdirowner
  • GRANT select on SYSIBM.SYSVIEWS to optimdirowner
  • GRANT select on SYSIBM.SYSSECURITYLABELS to optimdirowner
  • GRANT select on SYSIBM.SYSCOLUMNS to optimdirowner
  • GRANT select on SYSIBM.SYSDEPENDENCIES to optimdirowner
  • GRANT select on SYSIBM.SYSSEQUENCES to optimdirowner
  • GRANT select on SYSIBM.SYSINDEXES to optimdirowner
  • GRANT select on SYSIBM.SYSINDEXXMLPATTERNS to optimdirowner
  • GRANT select on SYSIBM.SYSINDEXCOLUSE to optimdirowner
  • GRANT select on SYSIBM.SYSRELS to optimdirowner
  • GRANT select on SYSIBM.SYSKEYCOLUSE to optimdirowner
  • GRANT select on SYSIBM.SYSSECURITYLABELCOMPONENTS to optimdirowner
  • GRANT select on SYSIBM.SYSSECURITYPOLICYCOMPONENTRULES to optimdirowner
  • GRANT select on SYSIBM.SYSDATATYPES to optimdirowner
  • GRANT select on SYSIBM.SYSCOLCHECKS to optimdirowner
  • GRANT select on SYSIBM.SYSBUFFERPOOLS to optimdirowner
  • GRANT select on SYSIBM.SYSNODEGROUPS to optimdirowner
  • GRANT select on SYSIBM.SYSROUTINES to optimdirowner
  • GRANT select on SYSIBM.SYSVIEWDEP to optimdirowner
  • GRANT select on SYSIBM.SYSPLANDEP to optimdirowner
  • GRANT select on SYSIBM.SYSROUTINEPROPERTIESJAVA to optimdirowner
  • GRANT select on SYSIBM.SYSROUTINEPARMS to optimdirowner
  • GRANT select on SYSIBM.SYSPREDICATESPECS to optimdirowner
  • GRANT select on SYSIBM.SYSSECURITYLABELCOMPONENTELEMENTS to optimdirowner
  • GRANT select on SYSIBM.SYSINDEXPARTITIONS to optimdirowner
  • GRANT select on SYSCAT.DATATYPES to optimdirowner

Specify a grant authorization ID

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.

Privileges required when using Optim Manager and Optim Connection Manager

The following privileges are required to use the Optim Manager and Optim Connection Manager to access DB Aliases. Replace optimuser with the appropriate value for your environment.

  • GRANT CREATEIN ON SCHEMA "ocm" TO USER optimuser;
  • GRANT DROPIN ON SCHEMA "ocm" TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLATTRIBUTES( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGE( INTEGER, SMALLINT, VARCHAR(70 OCTETS ), CHAR(8 OCTETS ), INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, CHAR(11 OCTETS ), CHAR(5 OCTETS ), VARCHAR(20 OCTETS ), VARCHAR(33 OCTETS ), VARCHAR(4096 OCTETS ), INTEGER ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGECCSID( INTEGER, SMALLINT, VARCHAR(70 OCTETS ), CHAR(8 OCTETS ), INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, CHAR(11 OCTETS ), CHAR(5 OCTETS ), VARCHAR(20 OCTETS ), VARCHAR(33 OCTETS ), VARCHAR(4096 OCTETS ), INTEGER, INTEGER ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCOLPRIVILEGES( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCOLUMNS( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLFOREIGNKEYS( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLFUNCTIONCOLS( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLFUNCTIONS( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLGETTYPEINFO( SMALLINT, VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLPRIMARYKEYS( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLPROCEDURECOLS( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLPROCEDURES( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLPSEUDOCOLUMNS( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLSPECIALCOLUMNS( SMALLINT, VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), SMALLINT, SMALLINT, VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLSTATISTICS( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), SMALLINT, SMALLINT, VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLSUPERTABLES( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLSUPERTYPES( VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(128 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLTABLEPRIVILEGES( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLTABLES( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(4000 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT EXECUTE ON PROCEDURE SYSIBM.SQLUDTS( VARCHAR(128 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(256 OCTETS ), VARCHAR(18 OCTETS ), VARCHAR(4000 OCTETS ) ) TO USER optimuser;
  • GRANT select on SYSIBM.SQLTABLES to optimuser;
  • GRANT select on SYSIBM.SQLTABLETYPES to optimuser;
  • GRANT select on SYSIBM.SQLTABLEPRIVILEGES to optimuser;
  • GRANT select on SYSIBM.SQLSPECIALCOLUMNS to optimuser;
  • GRANT select on SYSIBM.SQLCOLPRIVILEGES to optimuser;
  • GRANT select on SYSIBM.SYSDUMMY1 to optimuser;
  • GRANT select on SYSIBM.SQLPRIMARYKEYS to optimuser;
  • GRANT select on SYSIBM.SQLSTATISTICS to optimuser;
  • GRANT select on SYSIBM.SQLPROCEDURES to optimuser;
  • GRANT select on SYSIBM.SQLPROCEDURECOLS to optimuser;
  • GRANT select on SYSIBM.SQLTYPEINFO to optimuser;
  • GRANT select on SYSIBM.SQLUDTS to optimuser;
  • GRANT select on SYSIBM.SQLSCHEMAS to optimuser;
  • GRANT select on SYSIBM.SYSDATATYPES to optimuser;

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 Optim solutions use the Informix client SDK to connect to an Informix server. The SDK must be installed on the workstation so that the Optim solution can communicate with an Informix database. (You can download the client SDK for free from the Informix website.)

To create and use 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.) The user account must have the CONNECT and RESOURCE privileges. DBA privilege includes both privileges.

In some cases, 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 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 account 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

    CREATE VIEW

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: Create, 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:

  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE VIEW
  • CREATE SESSION
  • UNLIMITED TABLESPACE
  • SELECT ANY DICTIONARY
  • GRANT EXECUTE ON DBMS_SQL TO oracle_user
  • SELECT ON SYS.USER$ TO oracle_user
  • SELECT ON SYS.ENC$ TO oracle_user

Optim 11.3.0.5 removes the need to specify these permissions:

  • SELECT ON SYS.USER$ TO oracle_user
  • SELECT ON SYS.ENC$ TO oracle_user

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.

Follow the instructions here: http://www-01.ibm.com/support/docview.wss?uid=swg27038552 to not have to specify this permission:

  • SELECT ANY DICTIONARY

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:

  1. You must connect to the database as a database user with create procedure/create table 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 user account 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