StarQuest Technical Documents

SQDR Plus IBM i User Authorities

Last Update: 22 December 2016
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL010

Abstract

The Quick Start Guide to Using SQDR Plus v4 on Windows (see Tier 1 DB2 for i host computer requirements) and its Worksheet (see Tier 1 Source Database (IBM i) table) describe the three IBM i userID's that are used by SQDR Plus.

The default user authority requirements are designed for quick and trouble-free installation. However, in situations where data center security policies restrict the use of privileged user accounts, this document describes the roles and tasks performed by each of the three user ID's and the grants necessary to enable non-privileged userID's to perform those tasks.

StarQuest provides a set of CL programs that can be used by an IBM i system administrator to perform the IBM i host setup tasks prior to creating the agent, including creating the userID's and grants described below. The CL programs can be used in place of the standard method of host setup performed by the Add Agent Wizard function of SQDR Manager. See SQDR Plus IBM i CL Setup Scripts for details.

Solution

User Accounts used by SQDR Plus

Installing User - default QSECOFR

During creation of an SQDR Plus Staging Agent for the DB2 for i host, you will be prompted for the user name and password of a user with SECOFR authority (e.g. QSECOFR) or, at a minimum, *SECADM and *ALLOBJ authorities. A schema/collection will be created on the host, and two additional users are either created or verified: a non-privileged user that is the owner of the schema/collection (e.g. SQDR), and a privileged user that is the owner of a service program used for reading journal receivers (e.g. SQDRADM).

This credential is also used to install product updates, but is otherwise not used in the normal operations.

Contact StarQuest support for details on required grants if you need to use a non-privileged user for this purpose.

Agent User (Schema/Collection owner) - default SQDR

This user is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. This user requires no special authorities. In addition to the user, SQDR Control Center will create and populate a schema (collection) by the same name. The schema (collection) contains control tables and stored procedures used by SQDR and SQDR Plus.

This user is created with the options INLMNU(*SIGNOFF) LMTCPB(*YES) which reduces security expose by eliminating the possibility of using this ID for interactive terminal sessions.

Privileged Owner - default SQDRADM

This user is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. This user is the owner of the Journal Reader service program and typically requires *SECADM and *ALLOBJ authorities.

This user is used for subscription “add” processing and routine journal receiver processing. It is also used for log management implemented by an exit program.

After the agent is created, the password for the privileged owner can be set to *NONE.

To use a non-privileged user for this role by granting authority (with a privileged user), perform these steps before creating an incremental subscription with the Data Replication Manager. These grants may also be performed using Authority Lists - see the CL example below.

  1. Create the SQDRADM user:

CRTUSRPRF USRPRF(SQDRADM) PASSWORD(mypassword) INLMNU(*SIGNOFF) LMTCPB(*YES) TEXT('SQDR Plus Privileged user') PWDEXPITV(*NOMAX)

  1. Optionally, create the SQDR user now, rather than during installation of the agent. This step is required when using a non-privileged installing userID for installation of the agent:

CRTUSRPRF USRPRF(SQDR) PASSWORD(mypassword) INLMNU(*SIGNOFF) LMTCPB(*YES) TEXT('SQDR Plus user') PWDEXPITV(*NOMAX)

  1. Grant *USE for the user's table(s) and the library that contains the table(s). You can use wildcard naming (generic*) or *ALL to grant for multiple objects (e.g. use *ALL for all tables in a library):

GRTOBJAUT OBJ(MYLIB) OBJTYPE(*LIB) USER(SQDRADM) AUT(*USE)
GRTOBJAUT OBJ(MYLIB/MYTAB) OBJTYPE(*FILE) USER(SQDRADM) AUT(*USE)
GRTOBJAUT OBJ(MYLIB/MYTAB*) OBJTYPE(*FILE) USER(SQDRADM) AUT(*USE)
GRTOBJAUT OBJ(MYLIB/*ALL) OBJTYPE(*FILE) USER(SQDRADM) AUT(*USE)

  1. Grant *USE for the journal, journal receiver, and the libraries that contain those objects. Typically, the journal and journal receivers reside in the same library as the table:

GRTOBJAUT OBJ(MYLIB/QSQJRN0001) OBJTYPE(*JRNRCV) USER(SQDRADM) AUT(*USE)
GRTOBJAUT OBJ(MYLIB/QSQJRN) OBJTYPE(*JRN) USER(SQDRADM) AUT(*USE)

  1. Grant these additional authorities for the journal:

GRTOBJAUT OBJ(MYLIB/QSQJRN) OBJTYPE(*JRN) USER(SQDRADM) AUT(*ADD *OBJEXIST)

  1. Now use SQDR Control Center to install the agent by selecting Add Database from the Database drop-down menu.
  2. On the "Enter Database Information" panel, Userid refers to the installing user (e.g. QSECOFR), and Control Schema refers to the Agent user (e.g. SQDR)
  3. On the Control Database and Agent Authentication panel, enter the Agent password (i.e. the password for SQDR), the Privileged Owner ID (SQDRADM) and the password for SQDRADM.
  4. After the agent is installed and running, you can set the password for the privileged owner to *NONE:

CHGUSRPRF USRPRF(SQDRADM) PASSWORD(*NONE)

If all grants are not in place before adding a subscription to a journal, you may need to re-run the baseline for that subscription.

Autojournaling

Additional authorities may be needed when using autojournaling. Contact StarQuest support for details.

Exit Program

In standard deployments, SQDR Plus installs an exit program to prevent journal receivers from being deleted before they have been examined by SQDR Plus. Contact StarQuest Support for information about additional grants required to install an exit program when using a non-privileged ID for the Privileged Owner role.

Publishing

When using the Publishing feature of SQDR Plus, additional considerations may apply. Contact StarQuest support for details.

CL Example Using Authority Lists

The following CL script

  • Creates users SQDR & SQDRADM
  • Creates authority lists SQDR & SQDRADM
  • Reads a list of libraries from a physical file MYLIB/LIBLIST (column LIBLIST) and grants access to the objects in those libraries

PGM
DCLF FILE(MYLIB/LIBLIST)

CRTUSRPRF USRPRF(SQDR) PASSWORD(password) INLMNU(*SIGNOFF) LMTCPB(*YES) PWDEXPITV(*NOMAX) TEXT('SQDR Plus Agent user')

CRTUSRPRF USRPRF(SQDRADM) PASSWORD(password) INLMNU(*SIGNOFF) LMTCPB(*YES) PWDEXPITV(*NOMAX) TEXT('SQDR Plus Privileged user')

CRTAUTL AUTL(SQDR) AUT(*EXCLUDE)
ADDAUTLE AUTL(SQDR) USER(SQDR) AUT(*READ *EXECUTE *OBJOPR *ADD *OBJEXIST)
CRTAUTL AUTL(SQDRADM) AUT(*EXCLUDE)
ADDAUTLE AUTL(SQDRADM) USER(SQDRADM) AUT(*READ *EXECUTE *OBJOPR *ADD *OBJEXIST)

GETIN: RCVF
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(ENDPGM))
GRTOBJAUT OBJ(&LIBLIST/*ALL) OBJTYPE(*ALL) AUTL(SQDR)
GRTOBJAUT OBJ(*ALL/&LIBLIST) OBJTYPE(*LIB) AUTL(SQDR)
GRTOBJAUT OBJ(&LIBLIST/QSQJRN) OBJTYPE(*JRN) AUTL(SQDRADM)
GOTO CMDLBL(GETIN)

ENDPGM: ENDPGM

 


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.