Users and Passwords

A big part of the confusion over authentication is due to the fact that there are two diffrent forms of authentication! Authentication can be done using password authentication (also called internal authentication) or OS authentication(external authentication).

If the database is configured for OS authentication you can log into the database without authenticating to Oracle based on your UNIX UID. OS authentication is enabled or disabled based on the "os_roles" parameter. When you create a database using dbca OS authentication is disabled by default. In fact, you won't be able to enable it unless you edit the initialization parameters in step 10 by selecting "All Initialization Parameters" and then selecting "Show Advanced Parameters"! If you scroll down the long list you'll see "os_roles" is set as false, which you can then enable if you choose. All this tells you one thing: Oracle isn't keen on you using it. Therefore... don't.

When you create a database with dbca, in step 5, it will ask you to assign passwords to the default Oracle accounts. The default accounts are: sys, system, dbsnmp, and sysman. Lets break down the default users. (Descriptions taken directly from the DBCA help.)

SYSThe SYS user owns all base tables and user-accessable view of the data dictionary (Oracle configuration information). No Oracle user should ever alter (update, delete, or insert) any rows or schema objects conatained in the SYS schema, because such activity can compromise data integrety. The security administrator must keep strict control of this central account.SYSTEMThe SYSTEM user is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools.SYSMANThe SYSMAN user represents the Enterprise Manager super admin account. This EM admin can create and modify other EM admin accounts as well as admin the database instance itself.DBSNMPThe DBSNMP user is used by EM to monitor the database. EM uses this account to access performance stats about the database. The DBSNMP credentials sometimes referred to as the monitoring credentials.

In addition to these users, a user can connect with diffrent levels of privileges, namely SYSDBA and SYSOPER. When you connect using "connect sys/passwd as sysdba" your connecting as the SYS user and requesting SYSDBA privs. Because the SYS user is the Oracle equivilent to the UNIX root user Oracle makes you specify the amount of control you have, which is why you'll get an error if you try to connect without specifying the privs:

SQL> connect sys/passwd
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
SQL> connect sys/passwd as sysdba
Connected.
SQL>

The big diffrence between SYSDBA and SYSOPER privs is that SYSDBA can do anything (just like root). The SYSOPER privs allow you just about the same amount of control but won't allow you to look at user data. Both privs allow you to ALTER DATABASE, CREATE SPFILE, STARTUP or SHUTDOWN, ALTER DATABASE ARCHIVELOG, and includes RESTRICTED SESSION privs. However, only SYSDBA can CREATE or DROP DATABASE, and the ALTER DATABASE RECOVER options for SYSOPER are limited to complete recovery only.

Naturally these are all administrative accounts and therefore shouldn't be used for general database usage. (We used sysin our basics chapter because its already there and I wanted to focus on the basics without jumping straight into authentication).