Understanding SQL Developer user accounts

dockeroracle

I have installed this Docker image today: https://hub.docker.com/_/oracle-database-enterprise-edition

I followed the instructions here: https://www.youtube.com/watch?v=ciYsDbBx80s. Basically I ran the following commands:

docker pull store/oracle/database-enterprise:12.2.0.1
docker run -d -p 1521:1521 --name oracle store/oracle/database-enterprise:12.2.0.1
docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Then in sqlpus:

connect sys as sysdba
Enter password: anyrandompassword
alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;
SQL> GRANT ALL PRIVILEGES TO dummy;
SQL>ALTER USER sysdba IDENTIFIED BY Password1 ACCOUNT UNLOCK;

I have two questions:

  1. Why is the SQLDeveloper password for the dummy user: dummy. I have not specified this.

  2. It appears that I can now login to SQL Developer with the following credentials (as described here: https://stackoverflow.com/questions/9129664/unable-to-log-in-to-database-as-sys-with-oracle-sql-developer):

    Username: SYS AS SYSDBA
    Password: Password1

However, when I run the following command in sqlplus

connect sys as sysdba

I am then prompted for a password, however any password appears to work.

Best Answer

  1. Yes, you did:

create user dummy identified by dummy;

  1. By default, for local logins, Oracle databases use OS authentication based on OS user group membership. Any user who is a member of the dba group or whatever group was specified as the OSDBA group can log in locally as sysdba without providing a password (or providing any password).

I recommend that you forget this part:

alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;

Tutorials with this workaround (alter session set "_ORACLE_SCRIPT"=true;) are the result of laziness or lack of knowledge about the CDB architecture introduced in Oracle 12c. That is how incorrect "solutions" spread. The author forgot to tell you that by doing the above, you break a basic principle of the CDB architecture (because you create a new user that is treated as built-in Oracle-managed users + your data in the future should be stored in pluggable databases, not the root container). That is not how a container database is used.

Instead of doing the above, after getting the SQL prompt with this docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog", you should follow as below.

Connect to the database instance, below connects to the root container of the database. Do not bother with a password at this point:

connect / as sysdba

List pluggable databases, you should see a pluggable database named ORCLDB1.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
SQL>

Connect to that pluggable database:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>

And create the user there:

SQL> create user dummy identified by dummy;

User created.

SQL> GRANT ALL PRIVILEGES TO dummy;

Grant succeeded.

SQL>

Now you can connect with the dummy user, which is in the ORCLPDB1 pluggable database, and not in the root container. For that, use orclpdb1.localdomain as service name and not ORCLCDB.localdomain.

Related Question