Oracle OS authentication and password files

1. OS Authentication
After oracle is installed, OS authentication is enabled by default. The OS authentication mentioned here refers to the server-side OS authentication. OS authentication means that the user and password for database login are verified at the operating system level. If you log on to the OS when installing Oracle, no authentication is required when you log on to the Oracle database, for example:
SQL> connect/As sysdba
Connected.
SQL> connect sys/AAA @ test as sysdba
Connected.
SQL> connect sys/BBB as sysdba
Connected.
SQL> connect AAA/BBB as sysdba
Connected.
SQL> show user
Sys
SQL>
No matter which user you enter (even if this user, such as AAA, does not exist in the Database), you can connect to the database as long as you connect to the database with the sysdba permission and the user is sys, which is very convenient, sometimes, if you forget the Database Password and want to log on to the database, you can use this method on the database server.

But convenience also brings some security risks, so many people want to shield OS authentication,

In win, you only need to set ORACLE_HOME/Network/admin/sqlnet. sqlnet. authentication_services = (CNT) the system should be set to none or comment out this sentence (add # above) to block the OS function. to connect a sys user to a database, you must enter the correct sys password, you can also delete the Oracle installer from the ora_dba group. Of course, you can also delete the ora_dba group directly to disable the OS function. for example:
SQL> connect/As sysdba
Error:
ORA-01031: insufficient Permissions
SQL> connect sys/AAA as sysdba
Error:
ORA-01017: invalid username/password; login denied
SQL> connect AAA/BBB as sysdba
Error:
ORA-01031: insufficient Permissions
SQL> connect sys/system as sysdba
Connected.
SQL>

 

In Unix/Linux, you can also add sqlnet. authentication_services = (none) to the file sqlnet. ora)AndDeleting a DBA (groupdel dBA) group or deleting an oracle user from a DBA group can shield OS authentication. The use of these two methods to block OS functions seems a little unreassuring, or unconvincing, because after all, the system administrator can still create ora_dba or DBA groups and modify sqlnet. ora file ......

2. Password File
The Oracle password file is used to store the passwords of all users who connect to the database with the sysdba or sysoper permission. to remotely connect to the database with the sysdba permission, you must use the password file. Otherwise, you cannot connect to the database, because the Sys user must use the sysdba or sysoper method to connect to the database, that is, the Sys user must use a password file to connect to the database, therefore, I think it makes no sense to store the password of the Sys user in the database! The advantage of using the password file is that even if the database is not in the open state, you can still connect to the database through password file verification. After installing Oracle, the common user is not granted the sysdba permission. The password file only stores the Sys password. If the sysdba permission is then granted to the common user, at this time, the password of a common user will be read from the database and saved in the password file. Of course, the database must be in the open state. For example:
SQL> grant sysdba to test;
Authorization successful.
SQL> connect test/AAA @ orcl as sysdba
Error:
ORA-01017: invalid username/password; login denied
Warning: you are no longer connected to Oracle.
SQL> connect test/test @ orcl as sysdba
Connected.
SQL> alter database close;
The database has been changed.
SQL> grant sysdba, sysoper to test;
Grant sysdba, sysoper to test
*
Row 3 has an error:
ORA-01109: the database is not open

Several users have been granted sysdba or sysoper permissions, which can be obtained by querying the following v $ pwfile_users. The information of V $ pwfile_users is obtained from the password file.

SQL> select * from V $ pwfile_users;
Username sysdb sysop
----------------------------------------
Sys true
Test True False

Several users can be granted sysdba or sysoper permissions, which is determined by the number of entries specified when the password file is created. To be precise, it is also related to the size of the OS block. If entries specifies 5, an OS block can store the passwords of eight users, then eight users can be granted sysdba or sysoper.

 

Another problem is that the password length is increased, and the space occupied by the password is too large. The fact is that no matter how long the password is, the encrypted length is almost the same, that is to say, the size of the password file is almost irrelevant to the length specified by the password!

 

C:> orapwd file = databasepwd. ora Password = system entries = 5
OPW-00005: files with the same name exist-please delete or rename
C:> orapwd file = databasepwd. ora Password = system entries = 5 force = y

 

Note that there are no spaces before and after creating the password file! It is also worth mentioning that a new force default value n is added for 10 Gb, which is similar to the reuse function when creating a tablespace and whether to overwrite a file with the same name when it exists.

 

Whether to use the password file is controlled by using the remote_login_passwordfile parameter provided by Oracle. remote_login_passwordfile has three values: None, shared, and exclusive,
None indicates that the password file is not used. Password File authentication is disabled. The Oracle database does not allow remote sysdba/sysoper logon.
Exclusive indicates that the instance exclusively uses the password file, that is, each instance uses a separate password file,
Shared indicates that multiple instances share one password file,By default, the password file in win is in the format of pwdsid. ora, and orapwsid in Unix (case sensitive ),When the Oracle database is started, it first looks for the password file of orapw <Sid>. If the file does not exist, it starts to look for the password file of orapw. If the password file is named orapw, multiple databases can be shared.

 

SQL> alter system set remote_login_passwordfile = exclusive scope = spfile;

 

3. Change User Password

// View the user
SQL> select username, password from dba_users;

SQL> alter user system identified by manager;

 

 

4. How to Deal with sys/system password loss:

 

1) query the view v $ pwfile_users, select * from V $ pwfile_users;
Record user information with sysoper/sysdba system Permissions
2) shut down the database shutdown immediate
3). Delete the password file. The file path is ORACLE_HOME \ database and the file name is PWD <Sid>. ora.
4). Create a password file
Orapwd file = <FILENAME> Password = <password>
5) Add a user to the password file
Connect sys/internal_user_passsword as sysdba;
Start the database instance and open the database; create a corresponding user account and authorize it
Grant permissions: grant sysdba to user_name (if the previous database only has sysdba permissions for sys, skip this step)
6) modify the password file status. The default password file status is shared.
Remote_login_passwordfile is set to exclusive

SQL> alter system set remote_login_passwordfile = exclusive scope = spfile;