Oracle user is just that - a login name with password and some privileges
assigned to it. User's schema is all database objects owned by the user (user
can have no schema if this user didn't create any objects in the database).
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Leon" <litomail_at_yahoo.com> wrote in message news:a4ktnf$1gg$1_at_spacebar.ucc.usyd.edu.au...
> Thank you for your time reading my message.
> First of all, may I ask what's the different between Schema and User in
> Oracle? They are so subtle.
>
> Second, I used the Bobrowski book to start on Oracle8i. I examine the code
> from the book, here's the snippet of the SQL code:
>
> ... //code omitted
>
> DEFINE schema = PRACTICE04
> DEFINE default_tablespace = "USERS"
> DEFINE temp_tablespace = "TEMP"
>
> ...
>
> --Part1
>
> CONNECT system/&system_password;
>
> ...
>
> ACCEPT system_password PROMPT 'Please enter the password for the SYSTEM
> account'
>
> ...
>
> DROP USER &schema CASCADE;
>
> ...
>
> WHENEVER SQLERROR EXIT;
>
> ...
>
> --Part2
>
> CREATE USER &schema
> IDENTIFIED BY password
> DEFAULT TABLESPACE &&default_tablespace
> TEMPORARY TABLESPACE &&temp_tablespace
> QUOTA 200K ON &&default_tablespace;
>
> --Part3
>
> GRANT CONNECT, RESOURCE TO &schema;
>
> CONNECT &schema/password;
>
> -- Part4
>
> CREATE TABLE ITEMS
> (O_ID NUMBER(38)
> ,ID NUMBER(38)
> ,P_ID NUMBER(38) NOT NULL
> ,QUANTITY NUMBER(38) DEFAULT 1 NOT NULL
> );
>
> ...
> COMMIT;
>
>
> The code is executed using SYSTEM account (passwd is default: manager).
>
> The problem is:
>
> a.. Everytime I exit SQLPLUS, I loose all the objects (tables)
> b.. In the new session of SQLPLUS, I cannot log in as PRACTICE04
>
> I don't understand why, since the facts:
>
> a.. In part 4, all tables are created and committed, this should keep the
> object in the db permanently
> b.. In part 2 and 3, a user (schema .. whatever) called PRACTICE04 with
> passwd "manager" is created. And when I use the 'connect PRACTICE04/manager'
> in a SQLPLUS session, it tells me the password is invalid.
> And, ... in part 1 I connect using SYSTEM account and then create the
> so-called PRACTICE04 schema. Is that mean SYSTEM account is the owner of
> PRACTICE04 and only SYSTEM account can access PRACTICE04? Why often DBA can
> create a user that can be access by the user directly without through the
> login of the creator(DBA)?
> I want to thank you if you could help clarify this problem, since I almost
> finish the book but still don't understand this phenomenon.
>
>
>
Received on Mon Feb 18 2002 - 13:32:24 CST
