Database Authentication for Oracle Database XE

In this second part of a 10-part series on the security features of Oracle Database XE, you’ll learn how database authentication works, starting with passwords. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Understanding Database Authentication

Before the database can allow a person or an application access to objects or privileges, it must authenticate the person or application; in other words, the identity of who is attempting access to the database needs to be validated.

In this section, we first give an overview of the most basic method used to allow access to the database: database authentication. Next, we show you how database administrators authenticate with the database when, for example, the database itself is down and not available to authenticate the administrator.

Other authentication methods, such as network authentication, three-tier authentication, client-side authentication, and Oracle Identity Management are beyond the scope of this book. See the Oracle online documentation for more information on these advanced authentication methods.

Database Authentication Overview

In an environment where the network is protected with firewalls, and the network traffic between the client and the database server uses some method of encryption, authentication by the database is the most common and easiest method to verify the identify of the user with the database. All information needed to authenticate the user is stored in a table within the SYSTEM tablespace.

Very special database operations, such as starting up or shutting down the database, require a different and more secure form of authentication, either by using operating system authentication or by using password files.

In the next section, “User Accounts,” we go through all the options available to the DBA for setting up accounts in the database for authentication.

Database Administrator Authentication

The database is not always available to authenticate a database administrator, such as when the database is down because of an unplanned outage, or for an offline database backup. To address this situation, Oracle uses a password file to maintain a list of database users who are allowed to perform functions such as starting up and shutting down the database, initiating backups, and so forth.

In this section, we show you how to connect to the database using the password file, as well as create or re-create a password file if the password file is damaged or deleted, or you forgot all of the administrator passwords!

Note  When using a password file for authentication, ensure that the password file itself is in a directory location that is only accessible by the operating system administrators and the user or group that owns the Oracle software installation.  

{mospagebreak title=Authenticating Using the Password File}

We discuss system privileges in more detail later in this chapter in the section “Using System Privileges.” For now, though, you need to know that there are two particular system privileges that give administrators special authentication in the database: SYSDBA and SYSOPER . An administrator with the SYSOPER privilege can start up and shut down the database, perform online or offline backups, archive the current redo log files, and connect to the database when it is in RESTRICTED SESSION mode. The SYSDBA privilege contains all the rights of SYSOPER , with the addition of being able to create a database and grant the SYSDBA or SYSOPER privilege to other database users.

To connect to the database from a SQL*Plus or a SQL command-line session, you append AS SYSDBA or AS SYSOPER to your CONNECT command. Here’s an example:

C:>sqlplus /nolo g

SQL*Plus: Release – Production on Sun Sep 17 16:11:49 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect rjb/rjb as sysdba

Users who connect as SYSDBA or SYSOPER have additional privileges and the default schema is for these users when they connect to the database. Users who connect with the SYSDBA privilege connect as the SYS user; the SYSOPER privilege sets the user to PUBLIC :

SQL> show user

As with any database connection request, you have the option to specify the username and password on the same line as the sqlplus command, along with the SYSDBA or SYSOPER keyword:

C: > sqlplus rjb/rjb as sysdba

Please check back next week for the continuation of this article.

Google+ Comments

Google+ Comments