|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Oracle's special users: SYS, SYSTEM, INTERNAL and PUBLIC
Both, SYS and SYSTEM are default users, created with the creation of the database. Although they have much power - as they are granted the DBA role - they're still ordinary users. Because SYS owns the data dictionary, (s)he is considered a bit more special than SYSTEM.
But SYS has the SYSDBA privilege which SYSTEM doesn't. This makes it possible for SYS to become a very very powerful user. This is the case when (s)he connects as sys/password as SYSDBA or / as sysdba. The as sysdba phrase is a request to aqcuire the privileges associated wht the single SYSDBA system privileges (see here).
The difference becomes clear if you try to shutdown the database as ordinary SYS: you get insufficient privileges as result. However, if connected as SYSDBA, it's possible.
Note, SYSDBA is not a role, it is a privilege. You'll find it in system_privilege_map, not in dba_roles.
Anytime, someone connects as SYSDBA, it turns out it's being SYS. That is, if SYSDBA is granted to JOHN and John connects as SYSDBA and select user from dual, it reveals he's actually SYS.
SYS is also special in that it is not possible to create a trigger in the sys schema. Also, a logon trigger is not executed when sys connects to the database.
SYS is the owner of the database and the owner of the data dictionary.
Never ever create objects in the SYS schema.
SYSTEM is a privileged administration user, and typically owns Oracle provided tables other than the dictionary. Don't create your own objects under SYSTEM.
The sql.bsq script, which is run when a database is created, creates the public role:
create role public /
However, this role is not visible in dba_roles because it is hidden in this view (
Any privilege granted to public automatically becomes a privilege for other users as well.
INTERNAL is an obsolete (as of 8i) special user which is allowed access to the database even when the database is in NOMOUNT or MOUNT state. This user is typically used for physical database maintenance. The user internal is not maintained in the datadictionary but in the Oracle password file. The internal mechanism has been replaced by the SYSDBA and SYSOPER privilege in Oracle 8 and beyond.