Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Creating User & Database Confusion

    I've successfully installed Oracle 11g R2 database Enterprise Edition on Oracle Linux 6.2 last week. I've been reading the manuals and this. I'm confused about two things:

    1. When I create a new user as shown below in SQL*Plus, I'm not sure what to assign / grant for each user so they can login and begin using the system...

    Code:
    CREATE USER carlos IDENTIFIED BY some_pass
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    ;
    So the above creates the user 'carlos' fine however I'm looking for at minimum what Oracle 11g requires each user to have to start using the system. I was told I had to grant 'connect' & some other permissions as well.

    2. Understanding how databases are created / owned:

    The last part I'm confused about is maybe due to my misunderstanding of the word 'database' and how Oracle manages work areas. In MySQL & PostgreSQL, you create a database with the 'CREATE DATABASE' command. Then you assign the database an owner and that user then connects to the newly created database. In Oracle, I'm using an 11g system with a SID of test. I'm not 100% clear on if the SID name is a 'database' name or just a system I.D. but I'm guessing since Oracle can only have one unique SID, it's just an identifier and under this system, I can create many different schemas which are treated like databases in PostgreSQL, no? After I create the role / user above and give him whatever grants / permissions one needs to interact w/ 11g, how can I create a database for 'carlos' to start working? I'm trying to create a database for carlos called 'fcs' and I need carlos to be able to connect to this 'fcs' database or schema and create relational-oriented tables for my project.

    Thanks for any clarification!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails Read The Fine Manual (RTFM)

    Contents

    In Oracle a schema (user) is equivalent to database in MYSQL or PostgreSQL.
    I recommend that you avoid case where "CARLOS" need to operate against "FCS" schema.
    If you insist then again RTFM via URL below
    http://www.oracle.com/pls/db112/sear...y+user&partno=
    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 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    In Oracle a schema (user) is equivalent to database in MYSQL or PostgreSQL.
    I recommend that you avoid case where "CARLOS" need to operate against "FCS" schema.
    One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database schema is owned by a database user and has the same name as the user name.
    So from what I understand above, every user created gets his/her own schema (which is like a database in PostgreSQL) to work with and start creating tables into, correct? Just want to make sure I understand this correctly.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > every user created gets his/her own schema (which is like a database in PostgreSQL) to work with and start creating tables into
    CORRECT!
    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
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    So from what I understand above, every user created gets his/her own schema
    Yes, correct.

  6. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thanks all and just to be clear after reading the fine manual, at minimum for a new user to connect to his/her schema and begin working in 11g, they require ONLY create session & connect, correct?

    Code:
    SQL> CONNECT / as sysdba
    Connected.
    
    SQL> CREATE USER carlos IDENTIFIED BY letmein;
    
    User created.
    
    SQL> GRANT CONNECT, CREATE SESSION TO carlos;
    
    Grant succeeded.
    According to this quote in the manual:

    Prerequisites

    To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.
    I'm not sure why the omitted CONNECT but is what I did right or should they not need the CONNECT privilege?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm not sure why the omitted CONNECT but is what I did right or should they not need the CONNECT privilege?

    CONNECT is a ROLE; not a privilege.
    the actual privileges assigned to CONNECT vary by Oracle version.
    Oracle wants/plans to deprecate CONNECT role.
    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
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    I'm not sure why the omitted CONNECT but is what I did right or should they not need the CONNECT privilege?
    Connect is a role and contains the privilege "create session". As anacedent has pointed out the usage of the roles "CONNECT" and "RESOURCE" are deprecated because the grant too much privileges (and are therefor a possible security issue).

    After creating a user grant those privileges that will be needed. Usually those are:

    CREATE SESSION
    CREATE TABLE
    CREATE VIEW
    CREATE INDEX
    CREATE SEQUENCE

    And possibly:

    CREATE SYNONYM
    CREATE TRIGGER
    CREATE PROCEDURE

Posting Permissions

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