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

Source: Internet
Author: User
Tags dba true true create database

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.