StarQuest Technical Documents

SQDR Plus IBM i User Authorities

Last Update: 8 March 2022
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. In addition, an End User is used by Tier 3 (SQDR).

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.

It is also possible to combine rolls - for example, if you use the CL Setup Scripts, the SQDRADM user can also be used as Installing User and End User.

Solution

User Accounts used by SQDR Plus (Tier 2)

• Installing User (default QSECOFR)
• Agent User (default SQDR)
• Privileged Owner (default SQDRADM)

User Accounts used by SQDR (Tier 3)

• End User (no default)

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.

To use a non-privileged user for this purpose, create the users and grants in advance using the SQDR Plus IBM i CL setup scripts and use the SQDRADM user as the installing user as described in the section Creating the Agent in SQDR Manager.

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.

If the QPWFSERVER authorization list is configured as *PUBLIC *EXCLUDE (restricting access to QSYS.LIB objects from remote clients) rather than the default of *USE, add this user with ADDAUTLE AUTL(QPWFSERVER) USER(SQDR) AUT(*USE).

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 unless this user is also being as the Installing User or the End User.

This user can also be used in place of the Installing User and End User if the SQDR Plus IBM i CL setup scripts have been used to create the users and grants in advance - see Creating the Agent in SQDR Manager.

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)

2. 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)

3. 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)

4. 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)

5. Grant these additional authorities for the journal:

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

6. Now use SQDR Control Center to install the agent by selecting Add Database from the Database drop-down menu.
7. 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)
8. 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.
9. 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.

End User (no default)

All of the above users are related to Tier 2 (SQDR Plus Staging Agent).

This user (typically an existing end user) is used from Tier 3 (subscription configuration and Apply). This user requires no special privileges other than read access to the tables of interest (and the libraries that contain them) to acquire schema information when creating subscriptions and SELECT when running baselines.

If the SQDR Plus IBM i CL setup scripts have been used to create the users and grants in advance, and the SQDRADM user has been granted access to the tables (e.g. using Authority Lists), then the SQDRADM user can be used for this purpose.

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