Differences between Oracle sys and system users, SYSDBA and Sysoper systems permissions, SYSDBA, and DBA roles ____oracle

SYS and SYSTEM user differences

1 The most important difference, the importance of storing data is different

SYS the base tables and views of all Oracle's data dictionaries are stored in the SYS user, which is critical to the operation of Oracle and is maintained by the database itself and cannot be manually changed by any user. The SYS user has roles or permissions such as Dba,sysdba,sysoper and is the highest user of Oracle permissions.

The system user is used to store the secondary level of internal data, such as some Oracle features or management information for tools. The system user has the normal DBA role permissions.

2) The difference between the second and the different permissions

The system user can only log on em as normal, unless you grant SYSDBA system privileges or syspoer system privileges.
SYS user has "SYSDBA" or "sysoper" system privileges, login em can only use these two identities, can not use normal.

Login to Oracle with the SYS user, execute SELECT * from V_$pwfile_users, and query to users with SYSDBA permissions, such as:

Sql> select * from V_$pwfile_users;
USERNAME SYSDBA Sysoper
SYS true True

SYSDBA and Sysoper two system privilege differences

What's the difference between normal, SYSDBA, Sysoper?
Normal is a normal user
The other two, you look at the rights they have, you know.
SYSDBA has the highest system privileges and is sys after logging in
Sysoper is mainly used to start and close the database, sysoper the user is public after landing

Sysdba and Sysoper belong to system privilege, also known as administrative privilege, with some level of permission Sysdba and sysoper specific permissions, such as database turn-off, to see the following table:

System permissions	Sysdba	Sysoper
Difference	Startup (Start database)	Startup
Shutdown (Close database)	Shutdown
ALTER DATABASE Open/mount/backup	ALTER DATABASE Open/mount/backup
Change Character Set	None
Create database (creating databases)	None Cannot create database
Drop db (Delete database)	None
Create SPFile	Create SPFile
ALTER DATABASE Archivelog (archive log)	ALTER DATABASE Archivelog
ALTER DATABASE Recover (recovery databases)	Can only be fully recovered, cannot perform incomplete recovery
has restricted session limit permissions	have restricted session permissions
Allows users to connect as SYS users	You can do some basic things, but you can't view user data
User is sys after login	User is public after logging in

System if logged in, it is a normal DBA user, but if logged in as SYSDBA, the result is actually logged in as a SYS user, which is similar to the sudo feeling in Linux, as we can see from the login information. So when you connect to the database as SYSDBA, the objects you create are actually generated in the Sys. The same is true of other users, if the as SYSDBA login, but also as the SYS user login, see the following experiment:

Sql> create user strong identified by strong;

User has created.

Sql> Conn Strong/strong@magick as SYSDBA;

is connected.

Sql> Show user;

USER is "SYS"

Sql> CREATE TABLE Test (a int);

Table has been created.

Sql> Select owner from dba_tables where table_name= ' test ';

Row not selected because Oracle automatically turns uppercase when the table is created, it does not exist when you look it up in lowercase;

Sql> Select owner from dba_tables where Table_name= ' TEST ';

OWNER

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

SYS

the difference between DBA and SYSDBA

What is the difference between a DBA, a SYSDBA, and two system roles?

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

Create Instance → Startup instance → Create DATABASE (System tablespace is required)

startup process

instance start → mount database → Open database

Sysdba, which manages an Oracle instance, does not rely on the entire database to be fully booted, and as long as the instance is started, he already exists to log in as SYSDBA, mount the database, and open the database. Only the database is open, or when the entire database is fully booted, the DBA role has a basis for existence.