0

I am struggling connecting to a pluggable DB for which I created local users.

I have an Oracle 21c XE on my machine. The CDB is XE. I logged into SID=XE with sys as sysdba. There I created a PDB as follows:

create pluggable database GRE
    admin user manager identified by manager
    roles = (dba)
    file_name_convert = ('C:\ORADATA\XE\PDBSEED', 'C:\ORADATA\XE\GRE')
;

ALTER PLUGGABLE DATABASE GRE OPEN;

Then I switched to the pluggable DB:

alter session set container = GRE;
select sys_context('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
GRE

Then I created a user and assigned roles and priviledges:

-- USER SQL
CREATE USER "user1" IDENTIFIED BY "user1"  
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP" container=current;

-- QUOTAS
ALTER USER "user1" QUOTA UNLIMITED ON "DATA";

-- ROLES
GRANT "CONNECT" TO "user1" ;
GRANT "RESOURCE" TO "user1" ;
ALTER USER "user1" DEFAULT ROLE "CONNECT","RESOURCE";

-- SYSTEM PRIVILEGES
GRANT DROP ANY MATERIALIZED VIEW TO "user1" ;
GRANT CREATE ANY SYNONYM TO "user1" ;
GRANT DROP ANY SYNONYM TO "user1" ;
GRANT DROP ANY VIEW TO "user1" ;
GRANT CREATE ANY VIEW TO "user1" ;
GRANT CREATE ANY MATERIALIZED VIEW TO "user1" ;

This is the output of lsnrctl status:

Services Übersicht...
Dienst "541e76d5c7264140b887e5606a1a0486" hat 1 Instanzen.
  Instanz "xe", Status READY, hat 2 Handler für diesen Dienst...
Dienst "CLRExtProc" hat 1 Instanzen.
  Instanz "CLRExtProc", Status UNKNOWN, hat 1 Handler für diesen Dienst...
Dienst "XE" hat 1 Instanzen.
  Instanz "xe", Status READY, hat 2 Handler für diesen Dienst...
Dienst "XEXDB" hat 1 Instanzen.
  Instanz "xe", Status READY, hat 1 Handler für diesen Dienst...
Dienst "gre" hat 1 Instanzen.
  Instanz "xe", Status READY, hat 2 Handler für diesen Dienst...
Der Befehl wurde erfolgreich ausgeführt.

It can be seen that the PDB gre is available.

When I try to connect to the PDB using the local user, the login is denied with ORA-01017.

Login details in SQL Developer

I tried to figure out how to deal with PDBs and local users, but I am unable to understand how I can get this working. Maybe I understand the concept wrongly, and I need to use common users and connect to the CDB first and from there switch to the PDB?

2
  • You need to connect to the PDB using its service_name, not the SID. SID will always connect you to the CDB. Use lsnrctl status to get a list of active service names for the server.
    – pmdba
    Commented Aug 10, 2023 at 0:40
  • I made the connection to SID only to create the PDB, when connecting to the PDB, I used its service name, which I read from lsnrctl status.
    – Joysn
    Commented Aug 10, 2023 at 5:30

1 Answer 1

0

This is not related to the Multitenant architecture.

Unfortunately you created the user with a case-sensitive username by using double quotes.

The username in SQL Developer should be "user1" instead of user1.

1
  • oh, thank you for this hint. When I looked in SQL Developer DBA view under Security / Users, the " were not visible and the user names looked ok.
    – Joysn
    Commented Aug 10, 2023 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.