[Debezium Connector for Oracle] Questions about creating oracle user

[masami310]

Hi Team,

My environment is CDB.

There are multiple PDB in a CDB.

Since each PDB has different jurisdiction, I would like to limit the authority of Debezium users, so let me ask the following questions.

[Question1]

Is there a way not to use c##dbzuser (global user)?

If possible, I would like to set it as a PDB user (local user).

[Question2]

Is it possible to limit the tablespace to be created only in the target PDB instead of creating the tablespace (logminer_tbs) in all PDB?

[Question3]

Is it possible to specify PDB for authorization (GRANT) instead of CONTAINER = ALL?

GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;

GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;

GRANT ALTER ANY TABLE TO c##dbzuser CONTAINER=ALL;

GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;

[Chris Cranford]

Hi, see my comments inline

On 10/22/21 00:37, masami310 wrote:

Hi Team,

My environment is CDB.

There are multiple PDB in a CDB.

Since each PDB has different jurisdiction, I would like to limit the authority of Debezium users, so let me ask the following questions.

[Question1]

Is there a way not to use c##dbzuser (global user)?

If possible, I would like to set it as a PDB user (local user).

Unfortunately, no.  Mining happens at the root database level in the CDB and this makes sense given that there is only a single set of redo buffers/logs and archive logs that span all PDBs.  Additionally there is ongoing work to explore allowing a single connector to capture changes that span across multiple PDBs, which necessitates the need for this user to be a common user in the landscape.  The purpose of a single connector is that its the optimal way to minimize the database stress by doing continuous mining steps.

[Question2]

Is it possible to limit the tablespace to be created only in the target PDB instead of creating the tablespace (logminer_tbs) in all PDB?

If the user is only ever going to operate in the root CDB and a specific PDB, I don't think you'd need to define a tablespace in the PDBs in which the user won't connect.

[Question3]

Is it possible to specify PDB for authorization (GRANT) instead of CONTAINER = ALL?

GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;

GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;

GRANT ALTER ANY TABLE TO c##dbzuser CONTAINER=ALL;

GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;

Yes, just keep the following behavior in mind when adjusting these grants to be more restrictive:

    * Connector creates the LOG_MINING_FLUSH table in the connector user's CDB tablespace.
    * Connector expects all tables being snaphotted to exist in the configured PDB if one is configured.
        * Locks to tables should be possible in the CDB c##dbzuser tablespace and for tables being snapshotted in the PDB.
        * Create/Alter should only apply to the CDB tablespace of the c##dbzuser; we don't create or modify tables in the PDB.

HTH,
Chris

[masami310]

Hi, Chris

thank you for your answer.

I will try based on the answer.

2021年10月22日金曜日 23:30:08 UTC+9 Chris Cranford:

[JavaDev]

Hi Masami,

Does limiting the Debezium users to specific PDB worked for you ? Also can you share more details how you restricted debezium user to specific PDB and root only. 

[Chris Cranford]

Hi Sandip -

First, I took the liberty of answering your questions on Zulip [1] as you had some more in-depth questions there. 

But to your point here, yes you can most certainly confine the user's permissions to only the objects, pluggable databases, and schemas/tables you are interested in as long as access to the V$ tables and specific database roles are retained.  If you run into any specific problems with the connector when using explicit CONTAINER=<PDB-NAME> clauses rather than CONTAINER=ALL, let me know.  I think the only concern that may come to mind is those used specifically by the Oracle LogMiner process, namely SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and SELECT ANY DICTIONARY.  These may require being kept as ALL rather than restricted, but I would suggest using a restricted setup first and see.

In order to restrict the common user to root and a single PDB, you first have to keep in mind that a common user is created in the root and all pluggable databases.  When applying GRANTs to a common user, if you don't specify the CONTAINER clause, the grants are assumed applicable to all PDBs and the ROOT database.  If you want to apply these to only specific PDBs and the ROOT, you may have to do this in a multi-step fashion:

-- Connect to the root database, apply grants for the user explicitly there 
ALTER SESSION SET CONTAINER=CDB$ROOT;
GRANT <role> TO C##DBZUSER CONTAINER=CURRENT;

-- Connect to the PDB, apply grants to the user explicitly there
ALTER SESSION SET CONTAINER=PDB1;
GRANT <role> TO C##DBZUSER CONTAINER=CURRENT;

Hope that helps
Chris

[1]: https://debezium.zulipchat.com/#narrow/stream/302529-users/topic/Restrict.20debezium.20connector.20user.20in.20multitenant.20Oracle/near/325158774

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/73cd20b7-0a63-4c95-985f-79f5027e6c50n%40googlegroups.com.