0

I want to create a user from JDBC (if possible) in an ORACLE Express database. That user can't have the ORACLE_MAINTAINED flag in the DBA_USERS view.

Trying CREATE USER newuser IDENTIFIED BY xxx, but it throws a ORA-65096: invalid common user or role name. To avoid it, I had to set the flag ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE, as other posts recommend. But that way, it's marked as ORACLE_MAINTAINED.

I'm connecting to the xe default database.

Context:

I'm working with the Oracle Docker image; it's for integration testing purposes. The metadata queries the app uses exclude ORACLE_MAINTAINED='Y' users/tables, and I'm not able to list them (not working with data, just metadata). I could remove the WHERE from the queries, but I would prefer to avoid it, and keep the app queries untouched.

Thanks!

Iván
  • 971
  • 6
  • 20
  • 3
    https://stackoverflow.com/questions/33330968/error-ora-65096-invalid-common-user-or-role-name-in-oracle That error normally indicates that you're creating a user in a CDB rather than in a PDB. You'd almost always want to create users in a PDB not in a CDB. I'd wager you want your JDBC application to connect to the appropriate PDB rather than the CDB you're currently connected to – Justin Cave Sep 27 '21 at 16:00
  • How can I connect to the PDB? I only know the `xe` SID. I saw `XEPDB1` in the database logs, but I can't connect to it with the default users (SYS/SYSTEM). When I list `v$pdbs`, it's empty. Maybe there's not PDB by default? Should I create it first? – Iván Sep 27 '21 at 16:22
  • 1
    Which Oracle Docker image are you using? If you're using this one (https://blogs.oracle.com/oraclemagazine/post/deliver-oracle-database-18c-express-edition-in-containers) you should be able to connect to XE (the CDB) and XEPDB1 (the PDB) with the same `sys` password as documented in that article. You'd want to connect to the PDB to create your user. If you're using a different Docker image, the database could be configured differently. – Justin Cave Sep 27 '21 at 16:28
  • Gonna check that, thanks. Well, the image I'm using is a corporative one... Not the Oracle official, not even sure if this image is based at all in the Oracle one, or if it is custom. Now I have doubts. Anyway, checking it, and going to do some tests. I'll update later! – Iván Sep 27 '21 at 16:35
  • First of all, if I run `select * from v$pdbs` and there are no rows, does it mean that there are not PDBs, or may be that the user doesn't have roles to see them? – Iván Sep 27 '21 at 16:39
  • 1
    Yes. If you have permission to query `v$pdbs` from the CDB and there are no rows then there are no PDBs. If you're using a corporate Docker image it would seem weird that it would be delivered with a CDB and no PDB. I'd guess that maybe there was a separate install step to create the corporate-standard PDB. Having everyone create their own PDB if you're doing integration testing seems like a recipe for some less-than-fun times. – Justin Cave Sep 27 '21 at 16:43
  • After some research, it's based on: https://github.com/oracle/docker-images/tree/main/OracleDatabase/SingleInstance/dockerfiles/18.3.0 Not 100%, but most of the scripts are the same. I can't find any explicit reference to the PDB name or the PDB creation in that repo (I'm not an Oracle "expert" myself anyway, as you see) – Iván Sep 27 '21 at 17:07
  • 1
    The `createDB.sh` file in that directory appears to create the PDB. – Justin Cave Sep 27 '21 at 17:12
  • 1
    Nevermind, I just found in in the `createDB.sh` script. I'm gonna report/talk it with the people in charge of this images... Sorry, and thanks! – Iván Sep 27 '21 at 17:12
  • Before closing the question... Is there a way to create a non-ORACLE_MAINTAINED user in a CDB? – Iván Sep 27 '21 at 17:15
  • I assume that there probably is. But it's sort of like "Is there a way to change the oil in my car while I'm travelling down the highway?" Technically, sure. But it's not what a sensible person would want to do and would likely require bypassing a host of checks that are designed to make sure that doesn't happen and has a ridiculously high chance of ending up in a large ball of fire and much heartache. – Justin Cave Sep 27 '21 at 17:20
  • Ok, I understand. Actually, the people that made the images I'm using already left, so there is nobody that knows them, and it will probably be a long way until they fix them, probably... But yeah, not the way to take. Thanks! – Iván Sep 27 '21 at 17:24
  • @Iván you you can, usually the only purpose of creating user in CBD is monitoring. So either you can use built in use DBSNMP, or you can use this `_ORACLE_SCRIPT` (undocumented hack). Last option is to create a "Common user" i.e. an user whose name starts with `c##`. See https://docs.oracle.com/database/121/ADMQS/GUID-DA54EBE5-43EF-4B09-B8CC-FAABA335FBB8.htm – ibre5041 Nov 08 '21 at 08:52
  • @ibre5041 Both options generated the user with the flag ORACLE_MAINTAINED when I tried them – Iván Nov 08 '21 at 14:00

0 Answers0