Differences between system and sys in Oracle databases

SYS: has the role or permission of DBA, sysdba, and sysoper (System Operator). It is the user with the highest Oracle permissions and can only Log On As sysdba or sysoper, but cannot log on as normal.
System: Has dBA and sysdba permissions or roles. you can log on as a common user.
• [Differences between sysdba, sysoper, and DBA]
Sysdba User: You can change the character set, create and delete databases, and log on to sys (shutdown, startup)
Sysoper: the user cannot change the character set, cannot create or delete databases, and is public (shutdown or startup) after login)
DBA User: You can perform various management work only after starting the database.
Sysdba> sysoper> common DBA


1) The most important difference is that the importance of stored data is different.

[Sys] The base tables and views of all ORACLE data dictionaries are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database itself, no user can change it manually.
Sys users have the role or permissions such as dBA, sysdba, and sysoper, and are the users with the highest Oracle permissions.



[System] a user is used to store level-1 Internal data, such as the Management Information of some features or tools of oracle. System users have common DBA role permissions.



2) The second difference is that permissions are different.

[System] a user can only log on to Em as normal, unless you grant sysdba or syspoer system permissions to the user.

[Sys] a user has system permissions of "sysdba" or "sysoper". The user can only log on to Em using these two identities and cannot use normal.

Log on to Oracle as a sys user and run select * from V _ $ pwfile_users. Users with sysdba permissions can be queried, for example:

SQL> select * from V _ $ pwfile_users;

Username sysdba sysoper

Sys true

Differences between sysdba and sysoper system Permissions



What are the differences between normal, sysdba, and sysoper?

Normal is a common user

For the other two, you can check their permissions.

Sysdba has the highest system permissions.

Sysoper is mainly used to start and close databases. After logging on to sysoper, the user is public.



Sysdba and sysoper belong to system privilege, also known as administrative privilege,
The following table lists the specific system management permissions such as sysdba and sysoper:







If the system is logged on normally, it is actually a common DBA user.
Sysdba logs on. The result is actually logged on as a sys user, which is similar to sudo in Linux. We can see from the logon information. Therefore
After sysdba connects to the database, all created objects are actually generated in SYS. The same applies to other users.
Sysdba logon is also performed as a sys user. See the following experiment:

SQL> create user strong identified by strong;

The user has been created.

SQL> conn [email = strong/strong @ magick] strong/strong @ magick [/Email] As sysdba;

Connected.

SQL> show user;

User is "sys"

SQL> Create Table Test (A INT );

The table has been created.

SQL> select owner from dba_tables where table_name = 'test ';

Unselected rows // because oracle is automatically converted to uppercase when creating a table, it does not exist when it is queried in lower case;

SQL> select owner from dba_tables where table_name = 'test ';

Owner

------------------------------

Sys

& Oslash; differences between dBA and sysdba

What are the differences between dBA and sysdba system roles?

Before explaining this, I need to talk about the Oracle service creation process.

· Create an instance → start an instance → create a database (system tablespace is required)

Startup Process

· Instance start → load database → Open Database

Sysdba manages Oracle instances. Its existence does not depend on the full startup of the entire database. As long as the instance is started, it already exists,
Log in as sysdba, load the database, and open the database. The DBA role has a foundation only when the database is opened or the entire database is fully started!