Saturday, January 5, 2013

What’s the Difference between the SYS and SYSTEM Schemas?

After Oracle Database installation (see [1]), all database accounts except:
  • SYS
  • SYSTEM 
  • and a couple of others
are locked.

Curious people like me would normally ask, “What’s the difference between the SYS and SYSTEM schemas?”  In this article, answers are summarized from references [2] and [3].

SYS vs. SYSTEM


The SYS schema is the superuser of the database, owns all internal data-dictionary objects, and is used for tasks such as creating a database, starting or stopping the instance, backup and recovery, and adding or moving data files. These types of tasks typically require the SYSDBA or SYSOPER role. Security for these roles is often controlled through access to the OS account owner of the Oracle software. Additionally, security for these roles can be administered via a password file, which allows remote client/server access.

In contrast, the SYSTEM schema isn't very special. It’s just a schema that has been granted the DBA role.
Many shops lock the SYSTEM schema after database creation and never use it because it’s often the first
schema a hacker will try to access when attempting to break into a database.

Rather than risking an easily guessable entry point to the database, you can create a separate schema
(named something other than SYSTEM) that has the DBA role granted to it. This DBA schema is used for
administrative tasks such as creating users, changing passwords, granting database privileges, and so on.
Having a separate DBA schema(s) for administrators provides more options for security and auditing.




SYS
SYSTEM
When was it created?
Automatically created when Oracle database is installed Automatically created when Oracle database is installed

Granted role
Automatically granted the DBA role Automatically granted the DBA role

Default password CHANGE_ON_INSTALL (see Note 1) MANAGER (see Note 1)

What is this used for?
Owns the base tables and views for the database data dictionary used to create additional tables and views that display administrative information used to create internal tables and views used by various Oracle database options and tools

Connect as ...
The default schema when you connect as SYSDBA (select SYSDBA from the Role drop-down list in SQL Developer)

Select Default from the Role drop-down list in SQL Developer
Notes
Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account. Never use the SYSTEM schema to store tables of interest to non-administrative users.

Notes

  1. In Oracle Database 11g Release 2, this should be the Administrative Password you have specified during installation.[1]

References

  1. Installing Oracle Database 11g Release 2
  2. Pro Oracle Database 11g Administration
  3. What is the difference between SYS and SYSTEM database user accounts?
  4. Privileges, Roles and Profiles

No comments: