ORACLE: sysdba, DBA, and sysoper)

Differences between sys and system users

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

Base tables and views of all ORACLE data dictionaries in SYS are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database, 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.

The system user is used to store the second-level 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 users can only log on to Em as normal, unless you have granted sysdba system permissions or syspoer system permissions to them.
The sys user has system permissions of "sysdba" or "sysoper". You can only use these two identities to log on to Em, but 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 specific permissions of sysdba and sysoper at the system management level such as enabling or disabling a database can be viewed in the following table:

 

System Permissions	Sysdba	Sysoper
Differences	Startup (start database)	Startup
Shutdown (shut down the database)	Shutdown
Alter database open/Mount/backup	Alter database open/Mount/backup
Change Character Set	None
Create Database)	None
DROP DATABASE)	None
Create spfile	Create spfile
Alter database archivelog (archive logs)	Alter database archivelog
Alter database recover (recover database)	Only full recovery is allowed, and incomplete recovery cannot be performed.
Have restricted SESSION (session Restriction) Permissions	Restricted session permission
Allows users to connect as sys users	Some basic operations can be performed, but user data cannot be viewed.
After logon, the user is sys.	After logon, the user is public.

If the system is logged on normally, it is actually a common DBA user, but if it is logged on as sysdba, it actually logs on as a sys user, this is similar to sudo in Linux. We can see it from the logon information. Therefore, after the as sysdba connects to the database, the created objects are actually generated in SYS. The same applies to other users. If you log on as sysdba as a sys user, see the following experiment:

SQL> create user strong identified by strong;

The user has been created.

SQL> conn strong/strong @ magick 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

 

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 instance → start instance → create database (system tablespace is required)

Startup Process

Instance start → load database → Open Database

Sysdba manages Oracle instances. It does not rely on the full startup of the entire database. Once the instance is started, it already exists. It logs in as sysdba, loads the database, and opens the database. The DBA role has a foundation only when the database is opened or the entire database is fully started!

 

Category: Oracle

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

Base tables and views of all ORACLE data dictionaries in SYS are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database, 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.

The system user is used to store the second-level 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 users can only log on to Em as normal, unless you have granted sysdba system permissions or syspoer system permissions to them.
The sys user has system permissions of "sysdba" or "sysoper". You can only use these two identities to log on to Em, but 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 specific permissions of sysdba and sysoper at the system management level such as enabling or disabling a database can be viewed in the following table:

 

System Permissions	Sysdba	Sysoper
Differences	Startup (start database)	Startup
Shutdown (shut down the database)	Shutdown
Alter database open/Mount/backup	Alter database open/Mount/backup
Change Character Set	None
Create Database)	None
DROP DATABASE)	None
Create spfile	Create spfile
Alter database archivelog (archive logs)	Alter database archivelog
Alter database recover (recover database)	Only full recovery is allowed, and incomplete recovery cannot be performed.
Have restricted SESSION (session Restriction) Permissions	Restricted session permission
Allows users to connect as sys users	Some basic operations can be performed, but user data cannot be viewed.
After logon, the user is sys.	After logon, the user is public.

If the system is logged on normally, it is actually a common DBA user, but if it is logged on as sysdba, it actually logs on as a sys user, this is similar to sudo in Linux. We can see it from the logon information. Therefore, after the as sysdba connects to the database, the created objects are actually generated in SYS. The same applies to other users. If you log on as sysdba as a sys user, see the following experiment:

SQL> create user strong identified by strong;

The user has been created.

SQL> conn strong/strong @ magick 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

 

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 instance → start instance → create database (system tablespace is required)

Startup Process

Instance start → load database → Open Database

Sysdba manages Oracle instances. It does not rely on the full startup of the entire database. Once the instance is started, it already exists. It logs in as sysdba, loads the database, and opens the database. The DBA role has a foundation only when the database is opened or the entire database is fully started!