Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: Newbee: User Login Question

    Oracle XE 11g XE
    Windows Vista (32B)

    I created a workspace & user via APEX then created a table.
    I want to access the table outside of the APEX browser interface and do not know how to login as the new user. This is what I have the user defined.
    User Name: KCS
    Workspace: KCS
    Schema: TEST
    Password: cad

    What do I need to do to log-in as the new user from sqlplus?

    I tried: connect KCS/cad but that will not work.

    Thanks for any example you might provide.

    KCS
    Last edited by kenstupka; 10-06-11 at 21:33.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    usually schema & username are the same entity.
    by default in V11 passwords are CaseSensitive

    >What do I need to do to log-in as the new user from sqlplus?
    valid username & password

    >I tried: connect KCS/cad but that will not work.
    While above is likely true, it is 100% devoid of useful details.

    Is COPY & PASTE broken for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    5
    I don't get what you are saying - Copy & paste what?

    I am not an IT professional so I am pleading ignorance but let me try to clarify the situation.

    In the APEX (browser interface), there is a login dialog box and there is no problem with logging in as the new user from the dialog box - it is fill-in-the-blank format.

    The problem is trying to log on to the new user via SQLPlus or a 3rd party package that supports Oracle dbs.

    I know passwords are case sensitive. It isn't the password that is causing the problem. After all, the password is cad (all lower case) - that isn't an issue.

    Additionally, I can log on as sys, system or sys as dba through sqlplus but I cannot log on as the new user via sqlplus. The simple format: connect: username/password doesn't do it.

    It seems to me that Oracle cannot find the user and I am guessing that it has to be directed, some how to where that user resides and that is what I don't know how to do?

    The only thing that I have to copy & paste is the output from the SQL screen - is there something else? (yes, I did type the password at the prompt)

    SQL>
    SQL> connect KCS
    Enter password:
    ERROR:
    ORA-01017: invalid username/password; logon denied


    Warning: You are no longer connected to ORACLE.
    SQL>
    Last edited by kenstupka; 10-06-11 at 21:44. Reason: More info

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do as I did below.
    Do you not know how to COPY text from one Window & PASTE into a different window?
    Code:
    bcm@bcm-laptop:~$ sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 6 17:39:25 2011
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Enter user-name: user1/user1
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    login as SYS user to sqlplus & issue following SQL

    SELECT USERNAME, PASSWORD, CREATED FROM DBA_USERS WHERE CREATED > SYSDATE-7 ORDER BY 3;

    COPY the SQL & results, then PASTE all back here
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2011
    Posts
    5
    Thanks for the info.
    What you said earlier is that the User & Schema are usually the same thing is right on target.

    When I logged on using the schema name/password for the schema, I got logged-in.

    This is kind of confusing to me because my only experience with Oracle was through Oracle XE 10g and when I logged in, I just used the user name/password.

    My confusion is: that I thought that the schema is a container holding db objects and that in order to use the schema, you had to be a user of that schema but apparently one can log on to the schema and have access to it???????? I am sure that my assumptions are way off base.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect you won't be inclined to actually do so,
    but you'd really learn a lot by Reading The Fine Manual (below)

    Contents

    the whole Oracle Documentation set can be found at http://tahiti.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by kenstupka View Post
    Thanks for the info.
    My confusion is: that I thought that the schema is a container holding db objects and that in order to use the schema, you had to be a user of that schema but apparently one can log on to the schema and have access to it???????? I am sure that my assumptions are way off base.
    It seems that you were confused a little bit with all those "...names" and their "passwords", as well as tools you are using.

    USER (USERNAME), created in an Oracle database, along with objects he creates (tables, procedures, views, ...) make up his SCHEMA. They share the same name, so it is usual to say USER = SCHEMA. In order to access a schema, one has to have a PASSWORD. Combination of a USERNAME and his PASSWORD grant access to the database.

    APEX requires you to create a WORKSPACE, which is a virtual private database that allows many users to work within the same Apex installation, while keeping their objects, data and applications private. WORKSPACE "sees" database objects that belong to a certain SCHEMA, so you can create reports, forms, charts, whatever, based on data stored in tables that belong to that Oracle USER (i.e. SCHEMA).

    Furthermore, you need to create an APEX USER (which can be a DEVELOPER, an END USER or an ADMINISTRATOR). While developing APEX applications, you log on to a WORKSPACE as a DEVELOPER.

    That being said, it is clear that there are actually two PAIRS of "username + password" you use:
    • Oracle database USER + his PASSWORD (grant you access via SQL*Plus or SQL Developer or TOAD or ...)
    • APEX WORKSPACE + developer USERNAME + his PASSWORD (grant you access to your local Apex instance)

    In your case:
    • SQL*Plus: TEST/cad
    • APEX:
      - Workspace: KCS
      - Username: KCS
      - Password: <you didn't specify it, but might be "cad" as well. You probably know it>

  9. #9
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by Littlefoot View Post
    It seems that you were confused a little bit with all those "...names" and their "passwords", as well as tools you are using.

    USER (USERNAME), created in an Oracle database, along with objects he creates (tables, procedures, views, ...) make up his SCHEMA. They share the same name, so it is usual to say USER = SCHEMA. In order to access a schema, one has to have a PASSWORD. Combination of a USERNAME and his PASSWORD grant access to the database.

    APEX requires you to create a WORKSPACE, which is a virtual private database that allows many users to work within the same Apex installation, while keeping their objects, data and applications private. WORKSPACE "sees" database objects that belong to a certain SCHEMA, so you can create reports, forms, charts, whatever, based on data stored in tables that belong to that Oracle USER (i.e. SCHEMA).

    Furthermore, you need to create an APEX USER (which can be a DEVELOPER, an END USER or an ADMINISTRATOR). While developing APEX applications, you log on to a WORKSPACE as a DEVELOPER.

    That being said, it is clear that there are actually two PAIRS of "username + password" you use:
    • Oracle database USER + his PASSWORD (grant you access via SQL*Plus or SQL Developer or TOAD or ...)
    • APEX WORKSPACE + developer USERNAME + his PASSWORD (grant you access to your local Apex instance)

    In your case:
    • SQL*Plus: TEST/cad
    • APEX:
      - Workspace: KCS
      - Username: KCS
      - Password: <you didn't specify it, but might be "cad" as well. You probably know it>
    Many thanks. This is exactly what confused me.

    With 10g XE, I wasn't forced to create a specific workspace & tie to a specific schema (probably done by default), and for my simple usage, wasn't necessary but now I see the logic in doing so.

    Great Explanation- Thanks

  10. #10
    Join Date
    Oct 2011
    Posts
    5

    hello

    Quote Originally Posted by anacedent View Post
    I suspect you won't be inclined to actually do so,
    but you'd really learn a lot by Reading The Fine Manual (below)

    Contents

    the whole Oracle Documentation set can be found at Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    I most definitely am interested in reading the manual however the link that you sent does not work. (We're sorry, the page you requested was not found).
    The other link does me no good because it lists all documentation and I have no idea which one you were referencing.

    If you could be kind enough to supply a working link or the title of the reference, I would be grateful.

    Many thanks for your help.

    KCS
    Last edited by kenstupka; 10-07-11 at 14:43. Reason: more info

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Contents

    I'm not sure why COPY & PASTE had a problem.

    Above is for Concepts Manual.
    There is 1 for each version but V11.2 is most up to date.

    Enjoy!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •