Re: Basic Oracle Concepts

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:7jnhe1lqsnl2q3bm5lapoancd2mv34t30m_at_4ax.com...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
>
>>
>><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
>>
>
> The language does get circular and confusing sometimes..You connect to an
> instance ( called a database, sometimes,
> incorrectly. In Oracle, as has been posted by DA Morgan,a database is
> physical files, datafiles, undo files,
> tempfiles residing on a hard disk along with their associated control
> files and log files.)
>
> The schemas are collections of objects 'owned' by USERs ( including SYS
> and SYSTEM ) and those users can grant other users
> rights to access, modify, remove or otherwise treat those objects as their
> own .
>
> You connect to an instance as a USER ( not a schema, a schema is not a
> person ) ...
>
>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
> News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----

Yeah, the language does get interesting... especially when you throw the term 'server' into the mix.

I'll go with connecting to an instance (which has mounted and opened a database) using database credentials, with the instance (not database) identified by a TNS service name -- in other words, my use of the term database was a bit sloppy, but not totally inappropriate, since it is in the database that the user's credentials and validation method are defined.

Regarding schema/user ... Oracle typically has treated these terms as synonomous; my usage of the terms was to show their basic equivalence in Oracle, yet, as you state, more specifically the connection is for a user and each user has a schema. To create a new schema, you must create a new user (note that the CREATE SCHEMA statement simply groups a set of DDL statements into a single transaction, it does not create a new named schema in the database).

So more precisely, you connect as a user and each user has a schema. Once connected you access your connection user's schema by default, but can access any other user's schema (if privileges have been granted).

++ mcs Received on Thu Jul 28 2005 - 11:30:08 CDT