DBA Tips Archive for Oracle

  


Privileged Connections in Oracle9i

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. CONNECT INTERNAL Deprecated
  3. Where is Server Manager?
  4. Default Security using Database Creation Assistant - (DBCA)



Overview

Oracle9i introduces several new methods in which DBA's make privileged connections to the database. This articles describes these changes and how to use them.



CONNECT INTERNAL Deprecated

Before Oracle9i, DBAs would often use the CONNECT INTERNAL or CONNECT INTERNAL/PASSWORD to obtain a privileged DBA connection to the database. This is not longer supported in Oracle9i. An attempt to CONNECT INTERNAL to the database will result in an ORA-09275 error:

DBAs will now need to connect as SYSDBA or SYSOPER instead of CONNECT INTERNAL to connect as a privileged database user. Tasks like starting and stopping the Oracle database will now require the DBA to login as SYSDBA or SYSOPER. You will need to connect to the database as SYSDBA in order to create a database. As with previous version of Oracle, both SYSDBA and SYSOPER are audited.

There are two ways in which to connect to Oracle as the SYSDBA user: Using a password file or enabling operating system (OS) authentication and use the OSDBA or OSOPER roles.

If you have a secure connection (logged onto the Oracle database server), you can use operating system authentication as follows. Keep in mind that when I installed the Oracle database server software, I selected the OSDBA and OSOPER role to be DBA (UNIX) or part of the ora_dba group (Windows). The "/" indicated that Oracle is to use OS authentication

% id
uid=173(oracle) gid=115(dba)

% sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 5 21:35:03 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> 
If you are connect to the Oracle database over a non-secure connection, then you will need to use a password file. To create the password file in UNIX for the database (SID) named "TARGDB" with the SYS password being "change_on_install", use the following:
% cd $ORACLE_HOME/dbs
% orapwd file=orapwTARGDB password=change_on_install
You will also need to set the Oracle instance parameter "remote_login_passwordfile" equal to "EXCLUSIVE". Then to login using the password file, use:
% sqlplus "sys/change_on_install as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 5 21:42:57 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>



Where is Server Manager?

If you attempt to run the Server Manager command "svrmgrl", you will notice that it no longer exists. The SQL*Plus application replaces the old Server Manager application for all command-line DBA tasks. Most of the Server Manager commands, like STARTUP and SHUTDOWN are part of SQL*Plus. You will need to be careful, however, for scripts that used to work in Server Manager; they may not work in SQL*Plus.

Take for example, the SET CHARWIDTH, SET DATEWIDTH, and SET LONGWIDTH commands; they are not supported in SQL*Plus. To accomplish this in SQL*Plus, you will need to use the "COLUMN column_name FORMAT" command. Remember that the "COLUMN" command does not work for all columns as they did in Server Manager; you will need to use the "COLUMN" command for each column you would like to format. If for example, you wanted to change the width of the "STATE" column to be two characters with a header of "ST", you would use the following:

SQL> COLUMN state FORMAT a2 HEADING ST

Also, the command SET STOPONERROR is not available in SQL*Plus. You will need to use WHENEVER SQLERROR or WHENEVER OSERROR command in SQL*Plus to specify whether to CONTINUE or EXIT upon errors.

Next, the Server Manager command SET MAXDATA and SET RETRIES are not available in SQL*Plus. There are no replacements for these commands in SQL*Plus. You will need to simply remove these commands from your scripts.

In Server Manager, it was legal to put comments at the end of a line that included a semi-colon:

SQL> SELECT *
  2  FROM my_table;   /* Select All Values from My Table */
To accomplish this in SQL*Plus, you can use the following:
SQL> SELECT *
  2  FROM my_table   /* Select All Values from My Table */
  3  /



Default Security using Database Creation Assistant - (DBCA)

Anyone that has worked with Oracle knows the default passwords for accounts like SYSTEM/MANAGER. This has always been a huge security problem for new DBAs; not changing or locking privileged accounts after creating the database.

In Oracle9i, changes where made to help tighten the default security for those default accounts.

Locked and Expired Default Accounts

After the DBCA creates the Oracle database, it will proceed to lock and expire most of the default user accounts. Some of the accounts that do not get locked by the DBCA are SYS, SYSTEM, and SCOTT.
SQL> connect mdsys/mdsys
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 15:44:17 EST
Page Count: 12024