search

gvenzl / oci-oracle-free

Build scripts for Oracle Database FREE container/docker images
Apache License 2.0
113 stars 28 forks source link

ORA-01045: Login denied. User does not have CREATE SESSION privilege. #14

Closed jnewton03 closed 11 months ago

jnewton03 commented 11 months ago

Hello,

I'm having an issue with adding a user via an init script. The script worked on previous versions, but is not working correctly on version 23.2.0.

Here is my init script:

CREATE USER "TEST" IDENTIFIED BY "Secret321"  ;
GRANT "DBA" TO "TEST" ;
GRANT CREATE SESSION, CONNECT, RESOURCE TO "TEST";

Here is my docker compose:

  oracle-xe-23.2.0:
    image: gvenzl/oracle-free:23.2-slim-faststart
    ports:
      - "1524:1521"
    restart: always
    environment:
      ORACLE_PASSWORD: Secret321
    volumes:
      - "./startup:/container-entrypoint-initdb.d"

Here is the logs on startup:

oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: Resetting SYS and SYSTEM passwords.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | User altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | User altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: Executing user-defined scripts...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: running /container-entrypoint-initdb.d/01_users.sql ...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | User created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: running /container-entrypoint-initdb.d/01_users.sql

All looks good. However, when I try to login via dbeaver:

ORA-01045: Login denied. User TEST does not have CREATE SESSION privilege.

If I connect to the database as sys as sysdb via dbeaver and then run my script, then I can connect via my TEST user. Any idea why the init script isn't working?

gvenzl commented 11 months ago

Hey @jnewton03,

First of all, thanks a lot for using these images!

Looking at the output, the GRANTs never seem to be executed:


oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: Executing user-defined scripts...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: running /container-entrypoint-initdb.d/01_users.sql ...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | User created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: running /container-entrypoint-initdb.d/01_users.sql

There should be two more lines for each grant statement after User created.

Could it ne a Windows/Linux line feed issue, perhaps? Or something else that would SQL*Plus inside not detect the new lines?

jnewton03 commented 11 months ago

hi @gvenzl ! Thanks for the quick reply. Sorry, I tried to just combine information, but really II have 3 different scripts that run:

01_users.sql:

CREATE USER "C##LIQUIBASE" IDENTIFIED BY "Secret321"  ;

02_permissions.sql:

GRANT "DBA" TO "C##LIQUIBASE" ;
GRANT CREATE SESSION, CONNECT, RESOURCE TO "C##LIQUIBASE";

03_ddl_dml.sql:

--------------------------------------------------------
--  DDL for Table AUTHORS
--------------------------------------------------------

  CREATE TABLE "C##LIQUIBASE"."AUTHORS"
   (    "ID" NUMBER(*,0), 
    "FIRST_NAME" VARCHAR2(50 BYTE), 
    "LAST_NAME" VARCHAR2(50 BYTE), 
    "EMAIL" VARCHAR2(100 BYTE), 
    "BIRTHDATE" DATE, 
    "ADDED" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index AUTHORS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "C##LIQUIBASE"."AUTHORS_PK" ON "C##LIQUIBASE"."AUTHORS" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table AUTHORS
--------------------------------------------------------

  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("ID" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("FIRST_NAME" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("LAST_NAME" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("EMAIL" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("BIRTHDATE" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" MODIFY ("ADDED" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."AUTHORS" ADD CONSTRAINT "AUTHORS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS"  ENABLE;

--------------------------------------------------------
--  insert data into authors
--------------------------------------------------------

INSERT INTO "C##LIQUIBASE"."AUTHORS" VALUES('1','Eileen','Lubowitz','ppaucek@example.org','04-MAR-91', TO_DATE('1996-05-04', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."AUTHORS" VALUES('2','Tamia','Mayert','shansen@example.org','27-MAR-16', TO_DATE('2000-05-25', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."AUTHORS" VALUES('3','Cyril','Funk','reynolds.godfrey@example.com','21-APR-88', TO_DATE('1997-09-22', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."AUTHORS" VALUES('4','Nicolas','Buckridge','xhoeger@example.net','03-MAR-17', TO_DATE('1978-12-13', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."AUTHORS" VALUES('5','Jayden','Walter','lillian66@example.com','27-FEB-10', TO_DATE('1979-12-06', 'yyyy-MM-dd'));

--------------------------------------------------------
--  DDL for Table POSTS
--------------------------------------------------------

  CREATE TABLE "C##LIQUIBASE"."POSTS"
   (    "ID" NUMBER(*,0), 
    "AUTHOR_ID" NUMBER(*,0), 
    "TITLE" VARCHAR2(255 BYTE), 
    "DESCRIPTION" VARCHAR2(500 BYTE), 
    "CONTENT" VARCHAR2(4000 BYTE), 
    "INSERTED_DATE" DATE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

--------------------------------------------------------
--  DDL for Index POSTS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "C##LIQUIBASE"."POSTS_PK" ON "C##LIQUIBASE"."POSTS" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table POSTS
--------------------------------------------------------

  ALTER TABLE "C##LIQUIBASE"."POSTS" MODIFY ("ID" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."POSTS" MODIFY ("AUTHOR_ID" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."POSTS" MODIFY ("TITLE" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."POSTS" MODIFY ("DESCRIPTION" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."POSTS" MODIFY ("CONTENT" NOT NULL ENABLE);
  ALTER TABLE "C##LIQUIBASE"."POSTS" ADD CONSTRAINT "POSTS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS"  ENABLE;

--------------------------------------------------------
--  insert data into posts
--------------------------------------------------------

INSERT INTO "C##LIQUIBASE"."POSTS" VALUES('1','1','temporibus','voluptatum','Fugit non et doloribus repudiandae.', TO_DATE('1996-05-04', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."POSTS" VALUES('2','2','ea','aut','Tempora molestias maiores provident molestiae sint possimus quasi.', TO_DATE('2000-05-25', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."POSTS" VALUES('3','3','illum','rerum','Delectus recusandae sit officiis dolor.', TO_DATE('1997-09-22', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."POSTS" VALUES('4','4','itaque','deleniti','Magni nam optio id recusandae.', TO_DATE('1978-12-13', 'yyyy-MM-dd'));
INSERT INTO "C##LIQUIBASE"."POSTS" VALUES('5','5','ad','similique','Rerum tempore quis ut nesciunt qui excepturi est.', TO_DATE('1979-12-06', 'yyyy-MM-dd'));

These are used in automated testing. Here you can see that the scripts ran:

oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: Executing user-defined scripts...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: running /container-entrypoint-initdb.d/01_users.sql ...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | User created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: running /container-entrypoint-initdb.d/01_users.sql
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: running /container-entrypoint-initdb.d/02_permissions.sql ...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Grant succeeded.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Grant succeeded.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: running /container-entrypoint-initdb.d/02_permissions.sql
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: running /container-entrypoint-initdb.d/03_ddl_dml.sql ...
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Index created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Index created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | Table altered.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 1 row created.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: running /container-entrypoint-initdb.d/03_ddl_dml.sql
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | CONTAINER: DONE: Executing user-defined scripts.
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | 
oracle-xe-oracle-xe-23.2.0-1  | #########################
oracle-xe-oracle-xe-23.2.0-1  | DATABASE IS READY TO USE!
oracle-xe-oracle-xe-23.2.0-1  | #########################

But when I try to connect via either dbeaver or sqlplus I get denied:

sqlplus C##LIQUIBASE/Secret321@//localhost:1524/FREEPDB1                                                                                                                                                               

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 31 11:58:46 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01045: Login denied. User C##LIQUIBASE does not have CREATE SESSION
privilege.

I tried with a username that didn't have special chars and that failed as well. Lastly I tried using APP_USER and APP_USER_PASSWORD in place of scripts 1 and 2, but that fails due to the special character in C##LIQUIBASE regardless of if I include single or double quotes in the username inside of the docker compose file.

jnewton03 commented 11 months ago

For reference, This same workflow works fine for oracle-xe:21.3.0-slim-faststart:

docker compose

  oracle-xe-21.3.0:
    image: gvenzl/oracle-xe:21.3.0-slim-faststart
    ports:
      - "1523:1521"
      - "5503:5500"
    restart: always
    environment:
      ORACLE_PASSWORD: Secret321
    volumes:
      - "./startup:/container-entrypoint-initdb.d"

output

sqlplus C##LIQUIBASE/Secret321@//localhost:1523/XE                                                                                                                                                                  

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 31 12:31:23 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 31 2023 12:30:50 -05:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
gvenzl commented 11 months ago

Hey @jnewton03,

Thanks for posting this, I think I know what's going on.

The scripts you are running are running against the container database (aka CDB, or Oracle DB instance). This is also indicated that you are using C## but that's a side note.

In short, in your oracle-xe:21.3.0-slim-faststart image you are connecting to the container database XE, see the last part /XE here:

sqlplus C##LIQUIBASE/Secret321@//localhost:1523/XE

In the oracle-free equivalent, you are tyring to connect to the pluggable database (aka PDB), see the last part /FREEPDB1 here:

sqlplus C##LIQUIBASE/Secret321@//localhost:1524/FREEPDB1

If you simply change this to FREE, aka the CDB or Oracle DB instance, your will be able to connect as expected:

sqlplus C##LIQUIBASE/Secret321@//localhost:1524/FREE

gvenzl commented 11 months ago

I should add that technically speaking, you should only ever create user data in the PDB. Think of the CDB as the MySQL mysql database, it's there and you can do stuff with it, but it's really mostly there for the database instance to hold the data dictionary and for share common data across PDBs.

Your first two scripts above should be really one script (create user and define its permissions and properties in one step) and should start with ALTER SESSION SET CONTAINER=FREEPDB1;

Your second script should start with

connect <user>/<password>@localhost/FREEPDB1 which will connect you to the PDB as the actual user who can then create its tables and load data, etc. At this stage, there is also no need anymore to prefix each object (aka table name) with <user>. because you are already connected as that user.

I'm happy to elaborate more.

jnewton03 commented 11 months ago

@gvenzl this is great! thank you so much! For now I updated the JDBC url to use the CDB and it works great. We will create an enhancement ticket based on your suggestions. Thanks again!

gvenzl commented 11 months ago

Glad to hear that it's working and you are very welcome!

Thanks again for using these images!

Let me know whether you want me to lost your project(s) as users of these images, happy to do so!