Trying to understand exactly what a schema is and how it relates to another persons schema. In other words, the definition is

Schema
A schema is the set of objects (tables, views, indexes, etc) belonging to an account. It is often used as another way to refer to an Oracle account. The CREATE SCHEMA statement lets one specify (in a single SQL statement) all data and privilege definitions for a new schema. One can also add definitions to the schema later using DDL statements.

Am I right to assume that a schema are all the objects that exist in a database of my own creation. If I give access to someone to the objects in my database am I therefore giving them access to my schema? Or do they have their own schema in my database? If they are using my schema then I assume that any DDL statements that they commit affects the objects that I use. Otherwise, if they have their own schema in my database then do update,deletes, inserts affect my data?

As I am starting a new job with multiple schemas exist I want to fully understand how the statement that I commit affects those around me.  A little background, I am accustom to Microsofts Sql Server 2000, new to using Oracle.

A schema is a collection of objects owned by a user (so that makes a schema owner a user as well). Schema owners (you can have more than one in a database) are users, but not all users are schema owners, or more precisely, you can have users whom do not own any objects of their own.

What are objects? Tables, views, indexes, triggers, packages, functions, procedures, etc. Suppose you have an application with a schema owner (keep the schema owner separate from sys and system, or "sa" in SQL Server terms). The application allows for users to access (perform DML, but not necessarily any DDL) data. The schema owner has granted privileges on his tables, for example, so the users can perform select, insert, update and delete on records.

To make this more concrete in terms of an example you can touch, do you have the Scott schema installed? If so, try this experiment. As system (or as a user with the DBA role):

select object_name from dba_objects where owner='SCOTT';

You'll see everything Scott owns. Now create a user:

create user some_name identified by some_password;

Run the same query as before, but this time the owner is some_user.

SQL> show user
USER is "SYSTEM"
SQL> create user some_user identified by some_password;

User created.

SQL> select object_name from dba_objects where owner='SOME_USER';

no rows selected

Now connect as some_user:

SQL> conn some_user/some_password
ERROR:
ORA-01045: user SOME_USER lacks CREATE SESSION privilege; logon denied
 

Warning: You are no longer connected to ORACLE.

Re-connect as system and grant connect to some_user, then logon as some_user, and the rest of the output should be fairly self-explanatory.

SQL> conn system/manager
Connected.
SQL> grant connect to some_user;

Grant succeeded.

SQL> conn some_user/some_password
Connected.
SQL> select empno from emp;
select empno from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
 

SQL> select empno from scott.emp;
select empno from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
 

SQL> conn scott/tiger
Connected.
SQL> grant select on emp to some_user;

Grant succeeded.

SQL> conn some_user/some_password
Connected.
SQL> select empno from emp;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

SQL>

The error when some_user tried to select from emp is due to the fact that the table name does not exist in some_user's schema, so for some_user to see the contents of emp, he may have to qualify the schema name via scott.emp. If the emp table has to be referenced or qualified by schema.table_name, it would be apparent that there is not a public synonym for the scott.emp table. That's a little beyond what you asked about, but it should help convey the concept about accessing objects (e.g., tables) in other schemas. In this case, Scott has a public synonym for his emp table, and that is why "select whatever from emp" works for some_user. (You may want to "drop user some_user" when finished.)

So basically, a database can have more then one schema inside it. Access to a particular schema object depends on whether you have been given access to that particular object by the schema owner.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips