An Inherited Oracle Instance

What do you do when you become responsible for an Oracle database that has been running for three years, accumulating nobody really knows what, other than it’s all very important.

You have the Window 2008 r2 server administrator password, and the Oracle “system” password. You are the administrator of something else, and now, this is yours. You have little additional information. You might only have “system” because you found it embedded in a couple of scripts. That’s done more often than with the “sys” account credentials.

There’s a ton of stuff to learn with regard to schemas, exports and backups; but, you must first get connected properly. Here is what follows with your system user password:

ORA-1031-and-sysdba-user-id

The syntax “connect / as sysdba” is familiar with the slash really representing the separator between the user ID (schema name) and the password. Both of these values are implied because of the connection.

The external password file shows the only user who had Oracle “sysdba” privilege is the Oracle user “sys”. You have “system”. System is not in the password file, and typically, users are granted privileges with or without the ability to pass them on. They don’t have ability to pass them on without owning them… one would think.

One might be wrong in this case. The connection above was wrong without sysdba permissions. Look at the following login, no slash…

sys as sysdba

No slash, and oh, no password, just a return push. The particular case of logging on from system, and needing sys, to grant sysdba to a new, dba user, in order to user RMAN to do a backup, or any other maintenance for that matter, with no other documentation.

The emerg_dba was created with connect, resource and dba; but, could not have sysdba without it being granted by “the” only existing sysdba. The way the connection appears when actually connected as sysdba is that you are “sys”, it doesn’t matter that you used no password when prompted, it knows who you are.

Now, connected this way, give sysdba to your emerg_dba user, as follows:

success-sysdba-to-emerg_dba

Go off. Get your backup and test it somewhere. This account has just become your Oracle DBA account. You really should not use the sys or system accounts for 99.9% of the things you might be doing anyway. True fact, you might want to create another account for yourself for day-to-day operations. You don’t need to have “dba” to do non-related, non-dba work. Oh, and change your password from Password 🙂

Keep your eyes open for clear text passwords for “sys” or any other user, for that matter. Make sure accounts for features are locked down with non-default passwords.

ORA-01031? Don’t you necessarily believe it!

It took a lot of effort to get to this simple conclusion. Don’t always accept things when told it can’t be done. Sometimes it’s as simple as not applying logic and not accepting an answer because it was logical. It was a mistake.

It was as simple as “connect / as sysdba” was wrong, “sys as sysdba” allowed full access to everything you needed to do.

Several years ago, I wrote a page about the distinctions of who owns various components of Oracle. That post may be found here. With this new understanding that the Oracle “system” account can modify the “sys” account (and therefore, password, access, code, etc.), those boundaries have been crossed.

Don’t use sys and system, and refrain from using dba permissions. Make sure you can use them, then don’t.

30 year career in data, analysis, architecture, transformations, migrations, replication, using Oracle, Sybase, SQL Server, DB/2, XDB, Informix, Ingres, Seed, SQL DB/4381, and others. Nobody buys a database. People buy solutions. AWS is my network.

Leave a Reply