Differences between SYS and system users, systems permissions, and roles in Oracle _oracle

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

The base tables and views of all Oracle data dictionaries in "sys" 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 holds the secondary level of internal data, such as some Oracle features or management information for the tool. The system user has the normal DBA role permissions.

2 The second difference, the different permissions.

"System" users 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:

Copy Code code as follows:

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:

Oracle <wbr>sys and System user differences

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

startup process

instance start → mount database → Open database

SYSDBA, is to manage the Oracle instance, its existence does not depend on the entire database to start completely, as long as the instance starts, he already exists, log in as SYSDBA, mount the database, open the database. Only the database is open, or when the entire database is fully booted, the DBA role has a basis for existence!

1.DBA permissions are primarily involved in database administration-related permissions

2. You need to give your user admin permission to build the table, such as
Grant CREATE table to admin; --Create TABLE Grant create view to admin; --Create a view

3. Can also directly to connect and resource role, which includes most of the required grant connect,resource to admin;
The permissions included in both roles (for example, Oracle 10g) are:

Connect role:--is the typical right to give end users, the most basic
Create session--establishing sessions
Resource role:--is granted to developers
Create CLUSTER--Create a cluster
Create PROCEDURE--build process
Create SEQUENCE--Create a sequence
CREATE table--building tables
Create TRIGGER--creating triggers
Create type--build types
Create OPERATOR--Creating an operator
Create Indextype--Creating an index type
CREATE table--Creating tables