Friday, May 19, 2006

SYS and SYSTEM user accounts

For those of you who are Oracle DBAs (in fact, if you deal with Oracle RDBMS), you know that by default, the SYS and SYSTEM accounts are created when a database is created. You know (or should know) too that Oracle Corp recommends that these accounts should not be used for daily administrative tasks:
"It is suggested that you create at least one additional administrator user, and grant that user the DBA role, to use when performing daily administrative tasks. It is recommended that you do not use SYS and SYSTEM for these purposes."

The other part is to secure SYS and SYSTEM so that no one can access these accounts without first having to change the passwords. The accounts can be secured by setting the encrypted passwords to an uncryptable value by utilizing the undocumented command of ALTER USER IDENTIFIED BY VALUES "password". e.g. ALTER USER SYSTEM IDENTIFIED BY VALUES "unbreakable" would set the encrypted password value to "unbreakable". This effectively prevent brute-force password guessing as there is no way that the cleartext login credentials will be encrypted to the password value specified.

Now, I am curious to know how many organizations are actually following Oracle's recommendations and issuing individual DBA accounts and securing the SYS and SYSTEM accounts. I have had discussions with one of my DBA on these very issues where he insisted that it is almost a daily occurance where he has to be logged as SYSTEM to do his work. My counter arguements had (still are) that SYS and SYSTEM should only be required if there are reconfiguration work that needed to be done where the DBA role does not have the required privileges.

As of today, we are still not further ahead with implementing individual DBA accounts but the plan for me is to push ahead with the change and know that there will be times when it might cause my DBA some additional steps to get certain things done.


Anonymous said...

I work for a (very) large online retailer. We do use a separate DBA account for day-to-day admin work.

However, there are also regualryly times where we use the SYS/SYSTEM accounts, and these accounts are not locked/secured by changing the password.

Peter K said...

Anonymous said...
However, there are also regualryly times where we use the SYS/SYSTEM accounts,...

What are the situations where you need to be SYS or SYSTEM? The only things that I can think off are reconfiguration kind of tasks that the DBA role would not provide. Think of what the SYSTEM account has in terms of privileges and roles. SYSTEM has by default the DBA role and also certain privileges on selected group of SYS objects (views, tables, procedures) which could be granted to the individual DBAs.

If the SYS and SYSTEM accounts are "secured", I have no way of telling from selecting the ALL_USERS view who the DBAs are unless they are obviously named.

Doug Burns said...

Every site I work at is different, but here are a few approaches I've seen

1) People connect as SYSTEM all the time, with no personal accounts, and connect as SYSDBA when performing dictionary maintenance - for example running catpatch.sql or starting/stopping instances. I would say this is the most common and is problematic because you can never keep track what anyone does or, as you mentione, identify who the DBAs are because you're using a shared account.

2) Everyone connects as oracle to the server and uses SYSDBA for everything. This seems like bad news because you have far more privileges than are necessary, all of the time. The possible advantage of this is that you can limit the number of people who have server access more easily. SYS and SYSTEM remain locked and with obscure password until needed.

3) DBAs have their own individual accounts. This is more rare than perhaps it should be but I think the reason is that, ultimately, you will need to log in as SYSDBA at some point to patch, startup, recover etc, so you kind of undermine the personal accounts.

I'm not sure I've said anything conclusive here except to say that everyone does it differently and the fact that you'll need to connect as sysdba at some point undermines efforts to segregate privileges at a lower level

Doug Burns said...

"2) Everyone connects as oracle to the server and uses SYSDBA for everything. "

I should revise that one. Just because some sites do it, I wouldn't recommend it. Even if you did want to use this approach, you'd have individual unix accounts for each DBA, members of the dba group. That would achieve the same thing, with less risk. If you're really interested in security (like the odd merchant bank I worked at), there are good operating system security libraries that can then do keystroke recording etc, to capture what each DBA did.

Herod T said...

Everybody has a unix account to get onto the system, then su to the oracle account. Oracle account is not accessible from the outside, connections can only be done via ssh - telnet is disabled.

Oracle can connect to any database on the server (yes we have more than one) and do a sqlplus '/ as sysdba' and do whatever work is necessary. The OS tracks who su to oracle etc.

NOBODY has DBA but sys and system in any of our databases. We have a nightly security script that runs on every database, looks for user/pass the same, easy passwords, default passwords, and everybody with DBA or %ANY% privilege that shouldn't and
alerts the department when there are breaches. Also, the sys and system users have nice strong passwords.

We found that creating pseudo DBA roles or assigning DBA to those that needed was prone to forgetfullness and mistakes later down the line, just became a housekeeping nightmare.

Vendors hate us. They always seem to develop with DBA privs and then run into issues when they send us "perfect working" patches that they designed using a DBA or worse the sys or system accounts but we get told to run as a real user.

Now, comes the problem, is we have probably 30 distinct sys/system passwords throughout just the local data center, and more throughout the company. So, passwords get written down, emailed, posted on monitors etc.

We frown heavily upon that, we found instead of threatening jobs etc, public humiliation and ridicule works the best for "minor" infractions, and first round and appys at the pub for more serious or multi offenders. And when all 30+ people show up, first round is a rather large bill.

It doesn't happen often.

Oh... and "the" windows box.

Administrator owns the world and no other account can be created, and no domain either.

Application support people do not know sys/system only their personal account and/or the applications schema(s).

Peter K said...

Agreed. At my site, our internal auditors are obviously very concerned with the lack of accountability of the DBAs utilizing the SYS and SYSTEM account for daily administrative tasks. SYS and SYSTEM has been around forever and dated back to when I first started using Oracle back in 1986 (version 3) back when there is no such thing as SYSDBA or DBA role and SYSTEM was the only account that you used to do your daily DBA tasks.

In essence, your organization is utilizing the SYS account to do their daily DBA tasks.

Agreed with you there about vendors developing utilizing SYS or SYSTEM (in fact, doing their system testing with those accounts) and then wondered why things don't work when they set up an application account which do not have the far-ranging privileges as SYSTEM.

Pete Finnigan said...

ultimately, you will need to log in as SYSDBA at some point to patch, startup, recover etc

Hi Doug,

AS SYSOPER which offers less risk than being connected AS SYSDBA can be used for most of these tasks, i.e. startup, shutdown, recover (full only I admit), create spfile, backup, open mount. SYSOPER is not often used in my experience but it should be. you can grant sysoper to someone and allow basic maintenance tasks without giving them access to production data.