the oracle system account

Dear all,

is it say that all oracle server (10gR2, 11gR2) has a SYS account, this account existing as the oracle admin and it Is here when oracle just been installed?

so this can be the same type of account as the MS SQL sa account?

if so, changing the SYS account password alone should not impact the oracle installation and operation in anyway, right?

application connect to it should keep running well except the application also use this SYS account to connect to Oracle? which quit not possible.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
>>> same type of account as the MS SQL sa account?

yes, similar.  SYS is a super user.  Normal database operations don't apply to it


>> > the application also use this SYS account to connect to Oracle

that is very bad practice.

applications should have their own schemas and users.

It's pretty rare when anyone actually needs to log in as SYS (even dbas don't need to most of the time)


If your applications are using SYS then you probalby cant change the password without impacting them.
However, you should work very hard and soon to fix that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"yes, similar.  SYS is a super user.  Normal database operations don't apply to it"

so we don't touch it and we only set it up during installation ?

"yes, similar.  SYS is a super user.  Normal database operations don't apply to it"

"that is very bad practice.

 applications should have their own schemas and users.

 It's pretty rare when anyone actually needs to log in as SYS (even dbas don't need to most of the time)
"

I just give an example but answer very right ! tks.

"If your applications are using SYS then you probalby cant change the password without impacting them."

yeah, but for security concern, This SYS and SYSTEM password can change anytime and as nothing else except oracle administrator use it, right?

it will not impace the oracle operation in any way, right?
Kent OlsenDBACommented:
Hi marrowyung,

sdstuber is one of the best Oracle people you'll ever encounter so anything that I say here is intended to augment his answer, not replace it.

The SYS and SYSTEM accounts are the core of Oracle.  They are real users (schemas) and the most powerful entities in Oracle.  The system functions, procedures, tables, views, etc. are all owned by one of these schemas.  Even a small change to anything owned by SYS or SYSTEM can have a huge impact on Oracle and it can easily be catastrophic.  Perhaps the most important reason to never connect to Oracle as SYS or SYSTEM is so that there is no chance that an unintended change can occur to the system owned objects.

SYSDBA is the most powerful access that most DBAs ever need to Oracle.  SYSDBA is actually a role, not a user.  People often think of it as a user, but there is no schema associated with it.

Follow sdstuber's advise.  Have your applications connect with a normal user.  Otherwise you run the risk of something simple like:

  DELETE * FROM sometable;

when the application should say:

  DELETE * FROM schema.sometable;

The results would be catastrophic.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
>>> so we don't touch it and we only set it up during installation

Yes


>>> This SYS and SYSTEM password can change anytime and as nothing else except oracle administrator use it, right?

Yes
marrowyungSenior Technical architecture (Data)Author Commented:
Kdo,


"sdstuber is one of the best Oracle people you'll ever encounter so anything that I say here is intended to augment his answer, not replace it."

I am not sure if I said somethign wrong, I don't mean anyting and I respect him so much and I respect him more after you said it!

I don't konw oracle much and I trust you all.

"Even a small change to anything owned by SYS or SYSTEM can have a huge impact on Oracle and it can easily be catastrophic. "

but because of a handover issue some very good oracle DBA,  tell us to chagne the SYSTEM account password, but one that meeting done he change his word and tells aroud the same thign.

2 x DBA told us to change the password of system account and only god konw why they seems sleeping during the meeting and later on change their OWN idea, we knwo there are some preobelm and we rise question after that and they argue why change it.

THEY SUGGEST US do this , not us !   can't see why thing happen like this and this is the first time.

they say once change that password the oracle is manage by us !!!! then why they say this at the beginning and this is the whole idea of the meeting, handover please.!!

"Perhaps the most important reason to never connect to Oracle as SYS or SYSTEM is so that there is no chance that an unintended change can occur to the system owned objects."

did you try to change the SYS password / SYSTEM password ? it cause trouble to you befire?

in MS SQL, changing SA account WILL NOT do anything wrong and we suggested to change the sa password once the installation is done and once a dba gone.

"SYSDBA is the most powerful access that most DBAs ever need to Oracle.  SYSDBA is actually a role, not a user.  People often think of it as a user, but there is no schema associated with it."

so this mean The SYS and SYSTEM accounts belongs to SYSDBA ROLE ? I knew we should use SYSOPER at most situtation.

"Have your applications connect with a normal user. "

yes, we are !

sdstuber,

by this

>>> This SYS and SYSTEM password can change anytime and as nothing else except oracle administrator use it, right?

 Yes "

so I tihnk you have tried to cahnge the password of both before and there are no problem at all, right?
marrowyungSenior Technical architecture (Data)Author Commented:
one question,

what is the diff between SYS and SYSTEM ? they are now saying we can change the password, but which one to change?

SYS or SYSTEM is safer ?
sdstuberCommented:
>>> so I tihnk you have tried to cahnge the password of both before and there are no problem at all, right?

yes,   if no application is using them, then there is no problem at all in changing the sys and system password



>>> what is the diff between SYS and SYSTEM

they are 2 different schemas.  SYS is more powerful as a user though, because different security rules apply to SYS.  The database behaves differently.


>>>> SYS or SYSTEM is safer ?

system is safer to use. - but again NOT for application use.  only for administrative use.


you're over-thinking this.
marrowyungSenior Technical architecture (Data)Author Commented:
"yes,   if no application is using them, then there is no problem at all in changing the sys and system password"

excellent, yeah I guess so, our DBA start to say sth like this.  but one of them having more than 10 years of Oracle DBA said they never do it before.

"they are 2 different schemas.  SYS is more powerful as a user though, because different security rules apply to SYS.  The database behaves differently.
"

Any article about this in detail?  I tried to clone the sys account and create another name for it. please explain briefly the diff between 2 x accounts and which one is more powerful ?

but once I create that, that account has no right to login, what right should I grant the cloned SYS account with diff name to login successfully ?

"system is safer to use. - but again NOT for application use.  only for administrative use.
"

tks.
sdstuberCommented:
>>> Any article about this in detail?  I tried to clone the sys account and create another name for it. please explain briefly the diff between 2 x accounts and which one is more powerful ?


SYS is more powerful than any other.  You can't completely clone it.  SYS can do things that have nothing to do with its roles or granted privileges.

SYS owns x$ views and is the only user that can read them.
SYS ignores logon triggers.
only SYS can grant privileges to SYS objects.  Even if you have  "grant any" privileges they still have it.
SYS can shutdown the database


For articles - read the DBA, Security and PL/SQL guides.
marrowyungSenior Technical architecture (Data)Author Commented:
"SYS is more powerful than any other.  You can't completely clone it.  SYS can do things that have nothing to do with its roles or granted privileges."

the problem is, the new created login can't even login, this is what worry me.

I use Toad for Oracle to clone.

SQL developer can't do it !

"SYS owns x$ views and is the only user that can read them. "

X$view is system only view ?

"only SYS can grant privileges to SYS objects.  Even if you have  "grant any" privileges they still have it."

you mean if we changed the SYS's Privileges, nothing will be changed?

"SYS can shutdown the database"

the rest of user account created by any of the system account can't shutdown the system?

"SYS ignores logon triggers."

what is the point of it ?
sdstuberCommented:
you're chaining questions.


You could go on for days asking more and more followup questions about SYS.
Read the manuals suggested above.  You don't have to read them cover-to-cover, but search for SYS in each and read the related sections.
slightwv (䄆 Netminder) Commented:
I agree:  The online docs are your friend.  I would also suggest the Concepts guide it that isn't one of the ones above.

I also agree that you are over-thinking this.

In the Operating system world:  SYS is root in Unix and Local Administrator in Windows.  It is the supreme leader!

Think about it this way:
Something has to be superior to everything else and own everything else.

In Oracle, that is SYS,  It owns the core data dictionary and nothing else superior to it.  SYS rules over EVERYTHING else.

That supreme power needs a close second in command.  The initial DBA from which all other DBA's and other users/schemas are created.  That is SYSTEM.  SYSTEM has almost all the power but not quite.

In the universe that is Oracle:  SYSTEM can handle most of the day to day admin activities.  You really only need SYS when you need more God-like powers.

If your systems/apps use SYS or SYSTEM for specific tasks, those systems/apps need to be reevaluated to see if they really need those levels of access or it was a shortcut given out of laziness or lack of knowledge.
marrowyungSenior Technical architecture (Data)Author Commented:
sdstuber,

"you're chaining questions."

I agree I am asking more and more, but just SYS and SYSTEM.

slightwv,

"Something has to be superior to everything else and own everything else."

I knew, but what i mean is if SYS is that root, why need SYSTEM ?

"In Oracle, that is SYS,  It owns the core data dictionary and nothing else superior to it.  SYS rules over EVERYTHING else.
"

very good

"In the universe that is Oracle:  SYSTEM can handle most of the day to day admin activities.  You really only need SYS when you need more God-like powers."

it automatically means we should use SYSTEM other than SYS to login to oracle when we really need to work as a superuser on Oracle ? the DBA in other country only gives SYS, but we need to change password for both SYS and SYSTEM, funny about that!

"The initial DBA from which all other DBA's and other users/schemas are created.  That is SYSTEM."

what is that mean ?  it means if we need to create a new DBA account for new a new aboard DBA, we only clone one using SYSTEM but not SYS ? I tried to clone one using SYSTEM and that cloned account can't even login !  what I have done to make this account can login is to grand the DBA option again for that account.
slightwv (䄆 Netminder) Commented:
>>why need SYSTEM ?

Because that is the way Oracle chose to create things?

In the Windows world you have Administrator and Power User roles.  Why?

Because you shouldn't always log in to the most powerful account for the day to day tasks.

From a security issue you should only have the minimum permissions necessary to do what you need.

There is a reason not everyone has the DBA role, Administrtor or root depending on the OS/database/???.

>>what is that mean ?  

You get one 'owner' and one 'DBA' account out of the box.

>>it means if we need to create a new DBA account for new a new aboard DBA, we only clone one using SYSTEM but not SYS ? I

You don't clone anything.  If you need a new DBA level account, create it and grant the DBA role to it.
Kent OlsenDBACommented:
Hi enyimba,

Keep in mind that the things that Oracle does as part of its normal operation are far different than the interface(s) that allow us to interact with it.  There is no mechanism for a user to read the third page in tablespace USERSPACE1 and see if the 3rd column in the second row of data is NULL.  But that is exactly what could occur with a simple SELECT query.

Internally, Oracle doesn't manage items by username.  They're managed by the user_id associated with the user name.  The interface that we use to interact with Oracle converts the user_id to the username that we use.

Run this query:

SELECT * FROM ALL_USERS WHERE USERNAME = 'SYS';

What you'll get is something that looks like this:

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 24-JUL-07

Open in new window


SYS has user_id 0.  (Just like root has user_id 0 on unix and linux systems).  From a practical standpoint, user_id 0 IS Oracle and has unlimited authority.  Every other user is an extension of the Oracle system and can do no more than it is explicitly authorized to do.  That's the reason that SYS can't be cloned.  None of the permissions (as you think of them) matter.  The cloned user will have its own ID, and it won't be 0.
marrowyungSenior Technical architecture (Data)Author Commented:
slightwv,

"You get one 'owner' and one 'DBA' account out of the box.
"
so owner: SYS
DBA : SYSTEM

you mean.

"You don't clone anything.  If you need a new DBA level account, create it and grant the DBA role to it. "

good !

is it what you mean when I am creating an user account using Toad for Oracle ?

when creating oracle DBA role

what is default there means if you use Toad before ?
SQL developer seems can't create a new user but alter it .

but in this way, it seems that there are no instance when we really need to login using SYSTEM, right?
marrowyungSenior Technical architecture (Data)Author Commented:
Kdo

"Every other user is an extension of the Oracle system and can do no more than it is explicitly authorized to do.  That's the reason that SYS can't be cloned."

understood , tks.

"Keep in mind that the things that Oracle does as part of its normal operation are far different than the interface(s) that allow us to interact with it.  There is no mechanism for a user to read the third page in tablespace USERSPACE1 and see if the 3rd column in the second row of data is NULL.  But that is exactly what could occur with a simple SELECT query.

 Internally, Oracle doesn't manage items by username.  They're managed by the user_id associated with the user name.  The interface that we use to interact with Oracle converts the user_id to the username that we use.

 Run this query:

 SELECT * FROM ALL_USERS WHERE USERNAME = 'SYS';

 What you'll get is something that looks like this:
 "

I am sorry that I think I miss that, what are you trying to coach me by that ?

UI can't do a lot of thing and show up a lot of information ?
slightwv (䄆 Netminder) Commented:
I'm not a GUI user so cannot comment on how to do specific tasks in specific tools but checking the DBA role should work for DBA accounts.

>>it seems that there are no instance when we really need to login using SYSTEM, right?

I use SYSTEM but I've not created a separate DBA account.  I'm the only DBA on my database so there is no need to create a different account.
marrowyungSenior Technical architecture (Data)Author Commented:
"I'm not a GUI user so cannot comment on how to do specific tasks in specific tools but checking the DBA role should work for DBA accounts.
"

yeah, you are right ! if I clone SYS I can't do anything but only after I check that role.

"I use SYSTEM but I've not created a separate DBA account.  I'm the only DBA on my database so there is no need to create a different account."

lovely for that, but now, I can't login using SYSTEM and I am checking with other Oracle DBA on the password.
slightwv (䄆 Netminder) Commented:
>>if I clone SYS I can't do anything but only after I check that role.

Again, you cannot clone SYS.  Also, SYS has SYSDBA not just DBA.
marrowyungSenior Technical architecture (Data)Author Commented:
"Also, SYS has SYSDBA not just DBA."

What is that mean ?

"Again, you cannot clone SYS."

yes I knew.
slightwv (䄆 Netminder) Commented:
>>"Also, SYS has SYSDBA not just DBA."

SYSDBA is a special privilege and has more power than DBA.

The online docs will have all the information you need on this.
marrowyungSenior Technical architecture (Data)Author Commented:
yeah,got it. that's why oracle has more control on security than MSSQL, in this way!

MS SQL, instead, grant nothing to a new account including DBA, it just simplier.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.