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
SYS is the owner of the database and the owner of the data dictionary.
Never ever create objects in the SYS schema.
SYSTEM
SYSTEM is a privileged administration user, and typically owns Oracle
provided tables other than the dictionary. Don't create your own objects
under SYSTEM.
PUBLIC
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 (
where ... name not in ('PUBLIC', '_NEXT_USER') .
Any privilege granted to public automatically becomes a privilege for
other users as well.
INTERNAL
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.
|