Differences between Oraclesys and system users, sysdba and sysoper system permissions, and sysdba and dba roles

Differences between Oracle sys and system users, sysdba and sysoper system permissions, and sysdba and dba roles

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 * fromV _ $ 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 systemprivilege, also known as administrative privilege. The following table lists the specific permissions for sysdba and sysoper at the system management level, for example, enabling or disabling a database:

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 wheretable_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 wheretable_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!

Default password:

Sys change_on_install (meaning it is changed during installation. I changed it to zxsz4084)

System manager

Scott tiger

Within 10 Gb, the sys user must log on to the database as sysdba/sysoper. Solution: sqlplus sys/zxsz4084 as sysdba

The latter. Your system user password is wrong. You said that you changed the password during installation, it should be the same as your sys user password, it should be zxsz4084, instead of the default password manager, if you unlock the system user, the system will naturally remind you to change the password, so the password should not be the manager.

Concept:

SYS is the user with the highest permissions in Oracle, and SYSTEM is a user used for database management.After the database is installed, the passwords of SYS and SYSTEM should be modified immediately to ensure database security.

You can change the password in three ways:
Sqlplus/as sysdba;
1. SQL> alter user sys identified by 123456

2. SQL> grantconnect to sys identified by 123456

3. SQL> passwordsystem (note: this command is only applicable to SYSTEM)

You can change passwords between SYS and SYSTEM users.

However, after changing the password to 123456, you can log on as follows:
Sqlplus/as sysdba;
Sqlplus sys/abcde as sysdba;
Sqlplus sys/as sysdba;
Sqlplus sys as sysdba;
You can log on successfully, and then view the current user:
SQL> show user
Displays user is 'sys '.
Why? Why is it ineffective to change the password without a password or any other password.
The answer is: authentication method.

Oracle Password Authentication

SYS Password Authentication includes operating system authentication and Oracle Authentication.

1. Operating System Authentication,

For a Unix operating system,As long as you log on to the operating system as a user in the DBA group, you can log on to the database as SYSDBA without verifying the SYS password.
For windows operating systems,After the oracle database is installed, a user group named ORA_DBA is automatically installed in the operating system. As long as it is a user in this group, you can log on to the database as SYSDBA without verifying the SYS password. You can also create a user group named ORA_SID_DBA (whose SID is the Instance name). users belonging to this user group can also have the preceding privileges.

How to modify the authentication method

Next, let's talk about how to change the authentication method to operating system authentication or oracle Authentication. (Windows and unix platforms are similar)

To set the authentication method to operating system authentication:
1. Modify the REMOTE_LOGIN_PASSWORDFILE parameter to NONE.
2. Modify the SQLNET. ORA file. The directory of this file is... \ oracle \ product \ 10.1.0 \ db_1 \ network \ admin \ sqlnet. ora. Add this line in it: SQLNET. AUTENTICATION_SERVICES = (ETS)
3. Restart the database.

Set the authentication method to oracle Authentication (password file authentication ):
1. Modify the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE or SHARED. Exclusive indicates that only one instance can use a password file. The shared table password file can be used by multiple instances.
2. Modify the SQLNET. ORA file and add the # sign before SQLNET. AUTENTICATION_SERVICES = (ETS), that is, # SQLNET. AUTENTICATION_SERVICES = (ETS)
3. Restart the database.

What if the sys password is lost?

1. Use the system user to change the password

2. If a password file exists, delete it (the general path is under .. \ oracle \ product \ xx. x. x \ db_1 \ database) and create a password file -- enter it under cmd
Orapwd file = FILEPATH \ pwd <sid> password = PASSWORD entries = N
FILEPATH indicates the path of the password file. The format of the password file is pwd <sid>, and the sid is the name of the database instance.

It is orapwd <sid> in unix.