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...

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!

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=

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.

> every user created gets his/her own schema (which is like a database in PostgreSQL) to work with and start creating tables into
CORRECT!

Yes, correct.

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?

According to this quote in the manual:

I'm not sure why the omitted CONNECT but is what I did right or should they not need the CONNECT privilege?

>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.

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