Dctm – Oracle DB permissions on recent versions

If you have been running Documentum on Oracle in the past, you might have noticed that recent versions of Dctm requires more Database permissions to be able to perform an install/upgrade. This blog will just be a quick one to show what I used to request for DB permissions for older versions of Documentum as well as what happen if you don’t have the needed permissions on the latest versions. So, let’s get right into it.

First of all, when working on big environments/customers, it is pretty rare to get full access to the Database. Therefore, I’ve always created the tablespace and repository owner accounts upfront, with the needed permissions, before starting a new Documentum installation that would then use this dedicated account. If you aren’t in this case, then I guess this blog isn’t very relevant, since the Documentum installer can create the account with the needed permissions by itself (since you provide it with the DB SYSADMIN account). Please also note that using the Dctm installer will grant unlimited tablespace usage to the repository owner, which DBAs don’t really like (and it’s not really required if you do things properly…).

For as long as I recall, these have been the permissions that I requested/granted on the repository owner account ( being the dm_dbo account name) for Oracle Databases:

GRANT CONNECT TO <REPO_NAME>;
GRANT RESOURCE TO <REPO_NAME>;
GRANT CREATE SESSION TO <REPO_NAME>;
GRANT ALTER SESSION TO <REPO_NAME>;
GRANT CREATE TABLE TO <REPO_NAME>;
GRANT CREATE CLUSTER TO <REPO_NAME>;
GRANT CREATE SYNONYM TO <REPO_NAME>;
GRANT CREATE VIEW TO <REPO_NAME>;
GRANT CREATE SEQUENCE TO <REPO_NAME>;
GRANT CREATE DATABASE LINK TO <REPO_NAME>;
GRANT CREATE PROCEDURE TO <REPO_NAME>;
GRANT CREATE TRIGGER TO <REPO_NAME>;
GRANT CREATE MATERIALIZED VIEW TO <REPO_NAME>;
GRANT CREATE JOB TO <REPO_NAME>;

The documentation does not really give you much details about the exact requirements and the content of the doc itself hasn’t changed much (at all?) either. However, with the above list, you usually had enough permissions to install/run/upgrade Documentum as well as perform usual administrative tasks (like create a database link to populate some custom tables with external data (ODBC?) or managing some archiving/cleanup).

With recent versions of Documentum though, trying to perform an upgrade, for example from 20.x to 23.x, if you had the above DB permissions only, then the upgrade (or OOTB installation) would fail with the Repository not being able to start. The Repository logs would show something like:

[dmadmin@cs-0 ~]$ cat $DOCUMENTUM/dba/log/<REPO_NAME>.log

    OpenText Documentum Content Server (version 23.4.0000.0143  Linux64.Oracle)
    Copyright (c) 2023. OpenText Corporation
    All rights reserved.

2024-06-25T14:06:31.324462      13781[13781]    0000000000000000        [DM_SERVER_I_START_SERVER]info:  "Docbase <REPO_NAME> attempting to open"

2024-06-25T14:06:31.324558      13781[13781]    0000000000000000        [DM_SERVER_I_START_KEY_STORAGE_MODE]info:  "Docbase <REPO_NAME> is using database for cryptographic key storage"

2024-06-25T14:06:31.324581      13781[13781]    0000000000000000        [DM_SERVER_I_START_SERVER]info:  "Docbase <REPO_NAME> process identity: user(dmadmin)"

2024-06-25T14:06:31.701344      13781[13781]    0000000000000000        Database Session is shutting down due to (Pq)
2024-06-25T14:06:31.701461      13781[13781]    0000000000000000        [DM_SERVER_I_START_ID]info:  "Docbase <REPO_NAME> was not successfully opened. Docbase id: (1000001)"

[dmadmin@cs-0 ~]$

The above message “Database Session is shutting down due to (XX)” can be caused by missing/wrong DB details (like username), but it can also be caused by missing permissions on the DB Repository owner account. The problem is that this can happen even if you made sure to execute the “$DM_HOME/bin/dmdbtest” utility just before the Repository install/upgrade. This utility is(was) rather good, to test the DB connection and to make sure that you have(had) enough permissions. However, in this specific case, it doesn’t detect the issue, and it just completes successfully:

[dmadmin@cs-0 ~]$ ### Execution with initial DB permissions
[dmadmin@cs-0 ~]$ $DM_HOME/bin/dmdbtest -S<TNS_ALIAS> -U<USER_NAME> -P<PASSWORD>
Database successfully opened.
Test table successfully created.
Test view successfully created.
Test index successfully created.
Insert into table successfully done.
Index successfully dropped.
View successfully dropped.
Database case sensitivity test successfully past.
Table successfully dropped.
[dmadmin@cs-0 ~]$

Starting on Dctm 22.x (it was apparently around that time), some additional permissions are definitively required, but this utility either cannot check that, or it wasn’t updated for that (I let you have your own opinion on that matter ;)). To be able to install/upgrade your environment, you will need these additional permissions now (the first one being the most important apparently):

GRANT SELECT_CATALOG_ROLE TO <REPO_NAME>;
GRANT CREATE TYPE TO <REPO_NAME>;
GRANT CREATE INDEXTYPE TO <REPO_NAME>;
GRANT CREATE ANY INDEX TO <REPO_NAME>;

After a successful grant, if you would like to check the permissions of the DB account, you can execute these commands:

• SELECT USERNAME, GRANTED_ROLE FROM USER_ROLE_PRIVS;
  • Should display the SELECT_CATALOG_ROLE role
• SELECT USERNAME, PRIVILEGE FROM USER_SYS_PRIVS;
  • Should display the other three privileges

Note: yes, the documentation has been mentioning the Select Catalog Role as being required for a very long time and it’s part of the Dctm binaries since Aug-2001 (24y ago…) but I don’t think it was really required so far, since I installed dozens (hundreds?) of Dctm environment without that role on Oracle (it’s a sensitive one), and I never had any issues before, as I recall. But in recent versions, something changed, and we now need more permissions for certain.

In any cases, after adding the Repository owner account’s permissions on the Database, if you try to trigger the install/upgrade again, it should work properly:

[dmadmin@cs-0 ~]$ cat $DOCUMENTUM/dba/log/<REPO_NAME>.log

    OpenText Documentum Content Server (version 23.4.0000.0143  Linux64.Oracle)
    Copyright (c) 2023. OpenText Corporation
    All rights reserved.

2024-06-25T15:20:28.223973      6372[6372]      0000000000000000        [DM_SERVER_I_START_SERVER]info:  "Docbase <REPO_NAME> attempting to open"

2024-06-25T15:20:28.224045      6372[6372]      0000000000000000        [DM_SERVER_I_START_KEY_STORAGE_MODE]info:  "Docbase <REPO_NAME> is using database for cryptographic key storage"

2024-06-25T15:20:28.224709      6372[6372]      0000000000000000        [DM_SERVER_I_START_SERVER]info:  "Docbase <REPO_NAME> process identity: user(dmadmin)"

2024-06-25T15:20:29.048972      6372[6372]      0000000000000000        [DM_SESSION_I_INIT_BEGIN]info:  "Initialize Post Upgrade Processing."

2024-06-25T15:20:29.050037      6372[6372]      0000000000000000        [DM_SESSION_I_INIT_BEGIN]info:  "Initialize Base Types."
...