Forum Stats

  • 3,750,269 Users
  • 2,250,146 Discussions
  • 7,866,889 Comments

Discussions

Understanding schema and schema password in Oracle APEX 18c XE

M.Emmanuel
M.Emmanuel Member Posts: 304
edited Dec 23, 2018 6:12PM in APEX Discussions

When you install Oracle APEX 18.2, and before creating a new application you create an SCHEMA.

A password is given to that schema.

Then you create an admin user and you can create other users.

My understanding is that the SCHEMA is an actual database user which can be used to log into the database.

If I connect as SYS to the PDB where APEX is installed with SQL Developer I can see in "Other Users" my schema user.

However, when I try to connect to database using it it does not work:

So this works fine:

     sqlplus sys/[email protected]:1521/xepdb1 as sysdba

But this (given that my schema is scott and the password is tiger) does not work:

     sqlplus scott/[email protected]:1521/xepdb1

     ERROR:

     ORA-01017: invalid username/password; logon denied

As the schema user is not working, I start to think that I misunderstood what a schema is and what shall be used for.

I have the following questions:

1. Shall the schema created by APEX be available as a database user?

2. Shall I use it when I want to access externaly to APEX (for example, another external progra which needs to interact with APEX application tables). If so, how can I check that the user/schema can be accessed? Could it be a permissions issue?

3. If not, why I am providing a schema password, and how shall I achieve point 2 (accessing the database externaly to interact with APEX application tables)

Thanks

Tagged:
M.Emmanuel

Answers

  • cormaco
    cormaco Member Posts: 1,644 Bronze Crown
    edited Dec 23, 2018 7:24AM
    My understanding is that the SCHEMA is an actual database user which can be used to log into the database.

    This is correct.

    sqlplus scott/[email protected]:1521/xepdb1

    This should work if the user exists in xepdb1 and the password is correct and the user is unlocked.

    Make sure the user name appears as SCOTT in the database, not "scott"

    This worked for me:

    -- USER SQLCREATE USER "SCOTT" IDENTIFIED BY "tiger"  DEFAULT TABLESPACE "USERS"TEMPORARY TABLESPACE "TEMP";-- ROLESGRANT "CONNECT" TO "SCOTT" ;GRANT "RESOURCE" TO "SCOTT" ;ALTER USER "SCOTT" DEFAULT ROLE "CONNECT","RESOURCE";

    $ sqlplus scott/[email protected]:1521/xepdb1SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 23 12:35:17 2018Version 18.4.0.0.0Copyright (c) 1982, 2018, Oracle.  All rights reserved.Verbunden mit: Oracle Database 18c Express Edition Release 18.0.0.0.0 - ProductionVersion 18.4.0.0.0
    M.EmmanuelM.Emmanuel
  • M.Emmanuel
    M.Emmanuel Member Posts: 304
    edited Dec 23, 2018 6:12PM

    There was a missing letter in the password. Copy&Paste issue. This works as you state, thanks for confirming that there is no need to do anything else to grant access to schema/user.