Basic connections in Oracle Database

Basic connections and Appropriate Privileges in Oracle Database

Ordinarily, users like Scott, HR or any other user without the almighty SYSDBA system privilege cannot perform the startup or shutdown of any Oracle database because, the authentication for such users are processed against the data dictionary. For these users, it is impossible to startup or create any database, since the data dictionary can not be read until the database is created and opened.

When you launch a connection to the Enterprise Manager or Database Control (in 10g or higher), you don’t have the option to use the operating system authentication, but this connects via the listener, as if it is a remote process.
With that being said, your listener must be up and running for you to be able to establish a connection otherwise, you won’t be able to.

The following are various ways to establish connections to the database:

a.) Connect user/pass[@db]
This type of connection is established using the data dictionary authentication. Here Oracle will validate the username and password combination against values stored in the data dictionary. Obviously, the database must be up and running for the connection to go through. In other words, the database must be opened for the connection to be successful and even if you are Tom Kyte, you cannot issue the startup or shutdown commands. 

b.) Connect user/pass[@db] as sysdba
This type of connection instructs Oracle to go through the external password file to validate the username and password combination before the connection can go through.

c.) Connect user/pass[@db] as sysoper
This type of connection instructs Oracle to go through the external password file to validate the username and password combination before the connection can go through.

d.) Connect / as sysdba
This type of connection uses the operating system authentication by asking Oracle to go to the host operating system to validate if the user running SQL*Plus is a member of the operating system group that owns the Oracle binary/software and if the OS concurs with this, then the user will be able to log in as SYSDBA, without the username or password.

e.) Connect / as sysoper
This type of connection uses the operating system authentication by asking Oracle to go to the host operating system to validate if the user running SQL*Plus is a member of the operating system group that owns the Oracle binary/software and if the OS concurs with this, then the user will be able to log in as SYSOPER, without the username or password.

Any user using b, c, d, or e, examples above would be able to issue the startup or the shutdown commands and also will connect to the database no matter the state. Even, if the database is not yet created.

On the other hand, users using a, b, or c, examples above, include the database connection strings. This is necessary if establishing connection across the network.
Naturally, this type of connection is not an option for the operating system authentication because the OS authentication expects the user to log onto the machine hosting the Oracle server, either directly or indirectly – using SSH/Putty or telnet.