What's the difference between the Oracle SYS and SYSTEM accounts?

oracle administration database-administration
Hooloovoo picture Hooloovoo · May 27, 2009 · Viewed 25k times · Source

What are the differences between the Oracle SYS and SYSTEM built in accounts?

Edit: Apart from 3 letters!

Answer

Thomas Jones-Low picture Thomas Jones-Low · May 27, 2009

SYS owns the oracle data dictionary. Every object in the database (tables, views, packages, procedures, etc. ) all have a single owner. For the database dictionary, and a whole lot of special tables (performance views and the like) are all owned by the SYS user.

The SYSTEM user is supposed to be the master DBA user, with access to all of these object. This reflects an early, and long time, Oracle security design philosophy. You build the application using one user, then create a second with access (select, update, delete) but not drop privileges. This gives you a "super-user" access to your schema without being able to destroy it accidentally. Over the years, thing have been added to the SYSTEM account that may have needed to be in the SYS account. But very few people want to give out access to their SYS account if they don't have to.

Related questions

How SID is different from Service name in Oracle tnsnames.ora

Why do I need two of them? When I have to use one or another?

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More