StarQuest Technical Documents

SQDR Plus Oracle User Authorities

Last Update: 21 September 2021
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL018

Abstract

The Quick Start Guide to Using SQDR Plus (see Tier 1 Oracle host computer requirements) and its Worksheet (see Tier 1 Source Database (Oracle) table) describe the Oracle 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 user ID's and the grants necessary to enable non-privileged userID's to perform those tasks. A set of SQL*Plus scripts is provided to streamline the manual setup process.

Solution

• Scripts
• Installing User - default SYS AS SYSDBA
• Agent User (Schema owner) - default SQDR or C##SQDR
• Publishing User
• Replication User - recommended name SQDRUSER
• Considerations for Oracle 12c & later configured as a multitenant container database (CDB)
• Supplemental Logging Considerations
• Appendix: Expected GRANT failures

Scripts

The Quick Start Guide to Using SQDR Plus describes the typical method of creating an SQDR Plus Agent for an Oracle host using the Add Agent Wizard of SQDR Control Center. This GUI wizard performs all the necessary setup on the Oracle host, including creating a new userID with appropriate authorities, and creating and populating the agent schema. The Add Agent Wizard prompts for the credentials of a SYS AS SYSDBA account. The actions are performed using a JDBC connection from the system running SQDR Plus. This method is designed for quick and trouble-free installation, without the need to log on to the Oracle server through a terminal session or an administration application.

For situations where data center security policies restrict the use of privileged user accounts, StarQuest provides a set of SQL*Plus scripts that can be used by an Oracle database administrator to perform the host setup tasks prior to creating the agent, including creating the userID's and grants described below. We recommend reading this entire document to understand the concepts of the actions performed by the scripts.

The SQDRPlusOracleScripts.zip package contains the following scripts that should be run in the following order:

CreateUser - Create the Agent User
CreateTables - Create the control tables
CreateProcs - Create the stored procedures and functions
CreatePub - Create the Publishing User

After running the above scripts, use the SQDR Control Center Add Agent Wizard to create the agent, supplying the credentials of the newly-created Agent User as both the Installing User and the Agent User.

Optionally, the CreateTableGrants script can be run as the owner of the tables to be replicated. This script generates another script TableGrants that enables logging and performs a grant to the Publishing User for all the tables in the table owner's schema. This script is provided as an example, as performing these operations on all tables in a schema may not be the desired scenario.

See the readme.txt and the comments in each script for details. Note that there are two complete directories, for PDB and non-PDB environments.

The scripts for a PDB environment create common users for the Agent user (default C##SQDR) and the Publishing user (default C##SQDRPUB). After creating an agent for a PDB environment using the Add Agent wizard of SQDR Control Center, we recommend disabling the Agent for the root, and then starting the Agent for the PDB.

Warning: The table and procedure definitions contained in the scripts are based on the version of SQDR Plus at the time of their creation. Future versions of SQDR Plus may introduce new tables or procedures, or alter the existing tables or procedures. In a typical scenario, the Agent User has sufficient authority to perform these updates automatically after a software product update. However, in this manual setup scenario, it will be necessary for the database administrator to perform the updates under direction of StarQuest Support.

User Accounts used by SQDR and SQDR Plus

Installing User - default SYS AS SYSDBA

During creation of an SQDR Plus Staging Agent for the Oracle host, you will be prompted for the user name and password of a user with sufficient authority to create new users and grant authorities. A schema will be created on the host, and the new user (known as the Agent User) is either created or verified; the Agent User is a non-privileged user that is the owner of the schema/collection (e.g. SQDR).

This credential can also used to publish tables, but is otherwise not used in the normal operations.

If you are not using a privileged user such as SYS AS SYSDBA as the installing user, create the Agent User (SQDR) with associated grants as described in the next section, and use that user as the Installing User when creating the Agent; i.e. you will be using SQDR as both the Installing User and the Agent User.

Agent User (Schema owner) - default SQDR or C##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 other than those granted by the installing user. In addition to the user, SQDR will create and populate a schema by the same name. The schema contains control tables and stored procedures used by SQDR and SQDR Plus.

These are the privileges granted to the Agent User if it is created during creation of the Agent. To create the Agent User in advance, issue the following grants:

CREATE USER "SQDR" DEFAULT TABLESPACE "USERS" IDENTIFIED BY "mypassword" ACCOUNT UNLOCK;
alter user SQDR quota 100M on USERS;
grant SELECT ANY TRANSACTION to SQDR;
grant execute on sys.DBMS_LOGMNR to SQDR;
grant select on V_$DATABASE to SQDR;
grant SELECT_CATALOG_ROLE to SQDR;
grant SELECT ANY DICTIONARY to SQDR;
grant CREATE SESSION to "SQDR";
grant EXECUTE_CATALOG_ROLE to SQDR;

-- The next two grants are not needed if the database administrator creates the control tables and stored procedures with appropriate grants; see the scripts above for details:
grant create table to SQDR;
grant CREATE PROCEDURE to "SQDR";

-- The next two grants are performed by the Create Agent -- wizard but are now considered optional:
-- GRANT ANALYZE ANY TO "SQDR";
-- GRANT ANALYZE ANY DICTIONARY TO "SQDR";

-- (and for Oracle 12):
GRANT LOGMINING TO "SQDR";
grant SET CONTAINER to "SQDR";

Once the Agent User has been created by a system administrator and the above GRANTs issued, you can create an SQDR Plus Agent, supplying the credentials of the new Agent User as both the Installing User and the Agent User. You can expect to receive a warning about some grants failing; see below for details about the expected grant failures.

If you are using a multitenant container database (CDB) and pluggable databases (PDB's), see Considerations for Oracle 12c & later configured as a multitenant container database (CDB).

Publishing User - use either SYSTEM or a new user (suggested name SQDRPUB or C##SQDRPUB) with adequate authority

Publishing (described in the Publishing topic of the SQDR Control Center Help) allows you to specify which database tables to make available to SQDR clients for replication. In addition, it performs certain grants and operations that are needed by the Agent User. For most of the other source database systems supported by SQDR Plus, publishing is either optional or recommended. However, it is required for Oracle sources.

To publish tables in SQDR Control Center, select the agent, select the Publications panel, and select the Plus sign from the top of the window. You will be prompted for the credentials of a "privileged Id". This section describes the authorities needed by that userID. The user supplied at this prompt is cached until you log off from SQDR Control Center.

Publishing can be performed by one of the following methods:

• Use an existing privileged user (e.g. SYSTEM) to publish the tables. In this case, the publishing user has sufficient authority to perform the necessary grants and ALTER TABLE operations.
• Create a new user to be used as the Publishing User and grant it SELECT authority to the tables to be replication, as well as all authorities to the SQDR control table SQDR_SQ_CATALOG. Note that prior to the GRANT, the SQ_CATALOG table must already exist, either created by using the CreateTables.sql script (part of the SQDRPlusOracleScripts package referenced above), or by starting the Agent (assuming the Agent user has sufficient authority to create the control tables).
• In addition, use ALTER TABLE to enable logging on the tables to be replicated:

As a SYSDBA, create the Publishing User:

CREATE USER "SQDRPUB" DEFAULT TABLESPACE "USERS" IDENTIFIED BY "mypassword" ACCOUNT UNLOCK;
alter user SQDRPUB quota 100M on USERS;
grant CREATE SESSION to "SQDRPUB";
grant all on SQDR.SQ_CATALOG to SQDRPUB;

As either the owner of the tables or as an existing privileged user (e.g. SYSTEM), perform the following operations for each table to be replicated. See Supplemental Logging Considerations if you wish to avoid adding supplemental logging to all columns of a table.

GRANT SELECT ON table TO SQDRPUB with GRANT OPTION;
ALTER TABLE table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS.

• Alternatively, you can create a publishing user as above and GRANT ALL to the tables to be replicated. The ALTER TABLE will occur as part of the act of publishing:

GRANT ALL ON table TO SQDRPUB with GRANT OPTION;

To issue GRANT and ALTER TABLE statements on multiple tables, you can use statements like these to generate a script that performs the GRANT and ALTER statements for all tables in a schema:

select 'grant select on user_name_owner.'||table_name|| 'to user_name1 with GRANT OPTION ;' from all_tables t where t.owner='user_name_owner'

select 'ALTER TABLE user_name_owner.'||table_name|| 'ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS ;' from all_tables t where t.owner='user_name_owner';

Replication User - suggested name SQDRUSER

In addition to the userID's used by SQDR Plus, you will be asked to provide credentials when creating a source in Data Replicator Manager. This userID may already exist and does not need to be dedicated to use by SQDR. This userID is used to examine schemas and table structures when creating subscriptions, and is used for performing the initial baseline replication. It requires SELECT on the tables to replicated.

Considerations for Oracle 12c & later configured as a multitenant container database (CDB)

In the case of connecting to an Oracle12c & later multitenant container database (CDB), the following considerations apply:

• The Installing User (default SYS AS SYSDBA) should be a common user with system-level authority in both the root container and the pluggable database (PDB) of interest.
• The Agent User (default C##SQDR) should be a common user. If you are creating the user in advance, rather than letting it be created by the SQDR Control Center, perform the grants in both the root container and the PDB as described above.
• This additional grant is necessary when using C##SQDR as the installing user. If this grant is not done, the list of PDB's will not appear in the dropdown of the second panel of the Add Agent Wizard:
  alter user c##sqdr set container_data=all for dba_pdbs container=current;
  
• Note that Add Agent wizard creates two agents - one for the root, and another for the PDB. We recommend disabling the Agent for the root, and then starting the Agent for the PDB.
• When using Publications to grant access to user tables to the Agent User, the Publications function should be performed by a user with system-level authority in the PDB; this can be either a common user (e.g. SYSTEM or PDBADMIN) or a local user with sufficient authority.
• If you create a new user to act as a publishing user, it should be a common user, with appropriate authorities in the PDB.
• When configuring a source in Data Replicator Manager using either an ODBC data source or a connection string, specify a connection to the PDB (Service_Name=mypdb.mydomain.com in tnsnames.ora) rather than to the root container, and specify a Replication User (either common or local) that has access to the tables of interest and to the SQDR control tables located in the PDB.

Supplemental Logging Considerations

The default behavior of SQDR Plus is to require that supplemental logging be enabled for ALL columns of the subscribed tables. Configure the optional supplementalLogging and compareChangeData properties of the agent configuration when supplemental logging is enabled for PRIMARY KEY or PRIMARY KEY and UNIQUE columns. See the Configuration Reference section of the SQDR Control Center Help for details.

Appendix: Expected GRANT failures

When you use a non-privileged user as the Installing User when creating an agent, there are some grants that will fail, either because the Installing User does not have GRANT GRANT authority, or the authorities have already been granted to the Agent User. If the Agent User has been created as described above, these failures are harmless. To view the GRANT failures, examine the Jetty wrapper log (C:\ProgramData\StarQuest\sqdrplus\jetty\logs\wrapper.log).

These are the expected failures (subject to change in future versions):

alter user "SQDR" quota 100M on "USERS"
grant SELECT ANY TRANSACTION to "SQDR"
grant execute on SYS.DBMS_LOGMNR to "SQDR"
grant create table to "SQDR"
grant CREATE SESSION to "SQDR"
GRANT ANALYZE ANY TO "SQDR"
GRANT ANALYZE ANY DICTIONARY TO "SQDR"
grant SELECT ANY DICTIONARY to "SQDR"
grant CREATE PROCEDURE to "SQDR"
GRANT LOGMINING TO "SQDR"
grant EXECUTE_CATALOG_ROLE to "SQDR"
ORA-01031: insufficient privileges
(for all the above commands)

grant SELECT_CATALOG_ROLE to "SQDR"
ORA-01932: ADMIN option not granted for role 'SELECT_CATALOG_ROLE'

grant select on V_$DATABASE to "SQDR"
ORA-00942: table or view does not exist