Oracle OS Authentication

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 @ dmt as sysdba
Connected.
SQL> connect sys/bbb as sysdba
Connected.
SQL> connect aaa/bbb as sysdba
Connected.
SQL> show user
USER is "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 password of the database and want to log on to the database, you can use this method on the premise that it is on the database server. However, convenience also brings some security risks, so many people want to block OS authentication. In win, they only need to remove 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, 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>
You can also delete oracle installer users from the ora_dba group. Of course, you can also delete the ora_dba group directly to disable the OS function. In unix/linux, the file sqlnet. add SQLNET. AUTHENTICATION_SERVICES = (none) and delete dba (groupdel dba) groups or delete oracle users from dba groups 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. how to completely shield the OS function from the ora file? Let it "Never turn over? I have no experience in this area. You can add it!
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 v $ pwfile_users information is derived from the password file (This view lists users who have been granted SYSDBA and SYSOPER privileges as derived 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.
Here is a simple test:
SQL> declare
2 v_string varchar2 (100 );
3 begin
4 for I in 1 .. 100 loop
5 v_string: = 'create user test' | I | 'identified by test' | I;
6 execute immediate v_string;
7 end loop;
8 end;
9/
The PL/SQL process is successfully completed.
SQL> select count (*) from dba_users where username like '% TEST % ';
COUNT (*)
----------
101
The reason for this is 101 is that the test user has been created before.
SQL> declare
2 v_string varchar2 (100 );
3 begin
4 for I in 1 .. 100 loop
5 v_string: = 'Grant sysdba to test' | I;
6 execute immediate v_string;
7 end loop;
8 end;
9/
Declare
*
Row 3 has an error:
ORA-01996: GRANT failed: Password File ''is full
ORA-06512: In line 6
SQL> select * from v $ pwfile_users;
USERNAME SYSDB SYSOP
----------------------------------------
SYS TRUE
TEST1 TRUE FALSE
TEST2 TRUE FALSE
TEST3 TRUE FALSE
TEST4 TRUE FALSE
TEST5 TRUE FALSE
TEST6 TRUE FALSE
TEST7 TRUE FALSE
TEST8 TRUE FALSE
Nine rows have been selected.
SQL>
We can clearly see that v $ pwfile_users does not specify entries = 5 when creating the password file, but 9 records, which means that the passwords of nine users occupy an OS block, in fact, there may also be multiple OS blocks. In short, these blocks are full, but the password file still occupies 2 k, which is not clearly explained. The OS command shows that the number of bytes in each cluster is 4096. Here, the OS block size is actually 4 k, but the password file occupies 2 k and does not use a full OS block, I don't know why (
This parameter specifies the number of entries that you require the password file to accept. this number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. the actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. for example, if your operating system block size is 512 bytes, it holds four password entries. the number of password entries allocated is always a multiple of four.
)
View the OS block size in win
C: \ WINDOWS \ system32> fsutil fsinfo ntfsinfo e:
NTFS Volume serial number: 0x12afb454f6e54b31
Version: 3.1
Number of zones: 0x00000000040270d0
Total number of clusters: 0x00000000000000804e1a
Available Cluster: 0x00000000000b8d41
Total number of reserved items: 0x0000000000000000
Number of bytes per sector: 512
Number of bytes per cluster: 4096
Number of bytes per FileRecord segment: 1024
Number of clusters in each FileRecord segment: 0