Re: Basic Oracle Concepts

<sybrandb_at_yahoo.com> wrote in message
news:1122551255.818339.55450_at_g44g2000cwa.googlegroups.com...
> No, one instance manages one database. Period.
> You have
> instance
> |
> |
> database
> |
> ---------------------------------------
> | |
> scott schema hr schema
>
> That is 2 schemata in 1 database.
>
>
> Users are connected to a *database*, NOT to a *schema*, and they can
> access whatever schema, they have been allowed to access.
> --
> Sybrand Bakker
> Senior Oracle DBA
>

depending on your DBMS background, 'connect to a database not a schema' might give the wrong impression. here's a bit more detail..

users connect to a single schema in a single database -- ie, on connection they supply a schema/user name, password, and database service name (the schema/user credentials may be defaulted or be tied to the OS or network, and the database service name may also default)

once connected, all access privileges are in the context of that original connection schema -- which likely includes access that has been granted to objects owned by other schemas, whether that access has been granted explicitly to the connect schema (grant select on emp to calvin) or via a role that the connect schema has had granted to it (including the PUBLIC role)

all object references are assumed to be objects owned by the connection schema, unless a private or public synonym exists that encapsulates the objects owner (and perhaps a path to another database, ie, a database link)

additionally, the ALTER SESSION SET CURENT_SCHEMA statement sets any other schema as the default context for unqualified object references -- but it does not change the session's privileges (privileges assigned to the connection schema remain in affect)

there is also a confusing 'BECOME USER' system privilege, but this appears to only be used by Oracle utilities (such as EXP) and programs that access Oracle via the OCI (Oracle Call Interface)

++ mcs Received on Thu Jul 28 2005 - 07:35:03 CDT