More about Oracle DB ...

Following on from my earlier post about Oracle DB: -

Oracle Database - My First Few Baby Steps .... 

one of my colleagues picked up on my point about starting the SQLPlus application with authentication: -

$ sqlplus sys/passw0rd@orcl as SYSDBA

by saying: -

"Whenever you use "as sysdba" as a mechanism to log on, Oracle will assume you are logging in using OS level authentication.  As such you don't need to provide a username and password.  This method of logon is only available to a few ....

$export ORACLE_SID=orcl 
$sqlplus / as sysdba

For the above to work, the OS user you are logged on with as you run the above, must be a member of the DBA OS user group.  So how come the way you've logged on works?  Simple, Oracle ignores any credentials provided when you use 'as sysdba'.  So try it, change the username and password to anything and you should still get on ( It's important that someone, usually the sys user, can be authenticated in this manner, i.e. externally to the database, as when the db's shut down, someone needs to be capable of starting the thing up.  As all credentials are only available for querying once the db is up, this would provide a catch 22 situation for db startup.  Not sure what DB2 and SQL Server etc. employ? ).  As for all other 'normal' users, they can't log on to the db until the dba has started the db, by which time the Oracle data dictionary is open and can now be queried for authentication purposes.

The 'create user' command is fine, but it will create a user called 'monitor' which won't be able to effectively log on interactively, but if as I suspect it's a system account, you wouldn't want anyone to log on as that user? If you do want the monitor user to actually be capable of logging on you would grant the user that privilege with 'grant create session to monitor'.  You would normally allocate a default tablespace for the newly created user, otherwise it will use whichever tablespace is defined as the catch all default tablespace which isn't a great idea going forwards as then every user gets thrown in to this catch all tablespace and makes management more difficult.  A newly created standard user would often be created along the lines of ...

CREATE USER monitor
    IDENTIFIED BY passw0rd 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    PASSWORD EXPIRE;

Clearly one wouldn't expire the password on a system account.  Often a newly created user is simply created using an existing profile which is fit for purpose and the profile would give most of the above and more in one slice."

PS With regard to his comment about DB2, the answer is pretty simple - DB2 "delegates" authentication to the OS, so one can only start the database instance ( or the DAS ) once one has authenticated to the underlying OS e.g. su - db2inst1 -c db2start or su - dasusr1 -c "db2admin start" - in both cases, one will likely need to pass the password for the Unix account.