How to change the password of an Oracle user

How can I change the password of an Oracle user? This is a problem that many people have mentioned. Here we will teach you an Oracle user password method, hoping to help you.

As a Unix System Administrator, Oracle database administrator, and Oracle ERP application system administrator, you need to change the passwords of important users in different systems from time to ensure the security of the system and data.

However, due to the large number of managed minicomputers, databases, and application systems, it takes a lot of work to change the passwords of nearly users every month, regardless of the generation or change of passwords, especially for the generation of passwords, to enhance security, the password cannot be too simple. It is best to use a random combination of letters and numbers. However, if a large number of passwords are not automatically generated, the orchestration is for users in the Application System of Oracle ERP system to refer to users in various subsystems, not specific application users) it is also an Oracle database user, such as the receivables module. The user is AR, and AR is both a database user and an application user), and only a public user logging on to the system using applsyspub) and an application basic user ), apps (Specific Application Operation users) must be the same as the database password in the application system. The Oracle user password of other applications may be different from the password in the application system, you only need to change the patch to the same for the application system. Therefore, you can change the user password of the application system to be consistent with that of the database in the application system before patching. You do not need to change the password at ordinary times, which reduces the workload, second, the security of the system is enhanced.

You can change the password of a database user by executing the alter user identified by PASSWORD command in sqlplus. Therefore, you can efficiently change the password by generating a password script automatically. For Unix, the Administrator is not responsible for changing the password, so you can manually modify the password after it is automatically generated.
In order to generate a complicated password and improve work efficiency, I wrote a small script using the VBA that comes with Excel to run the program. Then, a script for changing the user password is automatically generated, record the user password on the worksheet for printing, saving it in the password book as a record. First, enter the username of each database user who wants to change the password in the first column of the worksheet. Then, enter the username in the third column and run the script gen_pass_oracle to automatically generate the corresponding password of the Oracle user; run the script gen_pass_unix to automatically generate the password of the unix operating system user. The generated Oracle user password is saved in change_pass. SQL. You can call this script in sqlplus to save the generated Unix User Password in change_pass.txt. You must manually change the password one by one.

You can modify the pass_length value in the program header to determine the length of the generated password. You can modify the start_asc method in the program header to determine the composition of the password 0-9, the A-Z or only A-Z is primarily implemented to determine whether to end password generation by checking whether a row of 1st or 3rd columns in the worksheet is empty. If it is not empty, there is a user name, then a loop is made to generate a password string of the specified length defined in the program header. The character in the password string is the new ascii value generated by the ascii value of the starting character plus the random number. Then, it is converted to the character type and added to the password string. When the length of the password string reaches the specified length, it is combined into an SQL statement and written into the file. At the same time, the password string is written to the 2nd or 4th columns of the row. Considering that strings starting with "=" are treated as formulas in Excel, ascii values equal to "=" are filtered when a password string is generated. In a similar way, the generated password string does not contain any other characters that you do not want to include.

By using this method, the workload for generating and changing passwords is greatly reduced, and the security is improved.
In fact, the password generated by this method is not limited to unix and Oracle and can be used for important users in various operating systems.

 
 

  
  
Sub gen_pass_app ()
  
  
Dim bit_count as integer 'cycle variable, password median counter
  
  
Dim row_num as integer 'the row number starting with the username information for which the password is to be generated
  
  
Dim rnd_base As Integer 'random number Seed
  
  
Ascii value of each character in the Dim char_value As Integer 'Password
  
  
Dim temp_str As String 'password String
  
  
Dim username (50) As String 'User Name
  
  
Dim pass_length as integer 'defines the length of the generated Password
  
  
Dim start_asc as integer 'defines which character is generated from
  
  
Pass_length = 8' set the password length to 8 characters
  
  
Rem start_asc = 48 'set the password to start from 0
  
  
Start_asc = 65 'set the password to start from
  
  
Rem because the Oracle Database User Password is case-insensitive, the random number is determined based on the selected Start letter
  
  
Rnd_base = 90-start_asc
  
  
?
  
  
Rem opens the file to output the generated Password Change script
  
  
Open "c: change_pass. SQL" For Output As #1
  
  
At the same time, rem Records the corresponding password on the worksheet to print out the password as a record. The title is written here.
  
  
Cells (1, 1) = "Username": Cells (1, 2) = "Password"
  
  
Cells (1, 3) = "Username": Cells (1, 4) = "Password"
  
  
Mr rem becomes the password for apps, but the script adds a comment, because the apps password must be changed with the application.
  
  
Rem first initializes the password string as blank
  
  
Temp_str = ""
  
  
For bit_count = 1 To pass_length
  
  
Char_value = start_asc + Int (Rnd (1) * rnd_base)
  
  
Rem here is a program error that is caused by the incorrect expression in excel.
  
  
If char_value = 61 Then
  
  
Char_value = 62
  
  
End If
  
  
Combination of rem and password
  
  
Temp_strtemp_str = temp_str + Chr $ (char_value)
  
  
Next bit_count
  
  
Rem outputs the generated apps password to the script file
  
  
Print #1, "REM alter user apps" + "identified by" + temp_str + ";"
  
  
Rem is also recorded on the worksheet
  
  
Cells (2, 3) = "APPS": Cells (2, 4) = temp_str
  
  
The user name for which the rem password is to be generated starts from the row row_num.
  
  
Row_num = 2
  
  
Rem if the first column is not empty, create a password. Otherwise, exit Do While Cells (row_num, 1) <> ""
  
  
Temp_str = ""
  
  
For bit_count = 1 To pass_length
  
  
Char_value = start_asc + Int (Rnd (1) * rnd_base)
  
  
If char_value = 61 Then
  
  
Char_value = 62
  
  
End If
  
  
Temp_strtemp_str = temp_str + Chr $ (char_value)
  
  
Next bit_count
  
  
Print #1, "alter user" + Cells (row_num, 1) + "identified by" + temp_str +
  
  
Cells (row_num, 2) = temp_str
  
  
Rem obtains the next row
  
  
Row_numrow_num = row_num + 1
  
  
Loop
  
  
The password of all rem users has been generated. close the file.
  
  
Close #1
  
  
End Sub
  
  
?
  
  
Sub gen_pass_unix ()
  
  
Dim bit_count as integer 'cycle variable, password median counter
  
  
Dim row_num as integer 'the row number starting with the username information for which the password is to be generated
  
  
Dim rnd_base As Integer 'random number Seed
  
  
Ascii value of each character in the Dim char_value As Integer 'Password
  
  
Dim temp_str As String 'password String
  
  
Dim username (50) As String 'User Name
  
  
Dim pass_length as integer 'defines the length of the generated Password
  
  
Dim start_asc as integer 'defines which character is generated from
  
  
Pass_length = 8
  
  
Start_asc = 48 '0
  
  
Rem start_asc = 65'a
  
  
Rem because unix passwords are case-sensitive, the range of random numbers is determined based on the selected Start letter to ensure
  
  
Rnd_base= 122-start_asc
  
  
?
  
  
Rem opens the file to output the generated Password Change script
  
  
Open "c: change_pass.txt" For Output As #1
  
  
At the same time, rem Records the corresponding password on the worksheet to print out the password as a record. The title is written here.
  
  
Cells (1, 3) = "Username": Cells (1, 4) = "Password"
  
  
Row_num = 2
  
  
Rem if the third column is not empty, create a password. Otherwise, exit Do While Cells (row_num, 3) <> ""
  
  
Temp_str = ""
  
  
For bit_count = 1 To pass_length
  
  
Char_value = start_asc + Int (Rnd (1) * rnd_base)
  
  
Rem 91-94 is [] ^ _'
  
  
Because rem does not want to include such characters in a unix password string, it reduces the number of counters that have been increased to ensure the length of the password. At the same time, it is not included in the password string to exclude them.
  
  
If (char_value> = 58 And char_value <= 64) Or (char_value> = 91 And char_value <= 96) Then
  
  
Bit_countbit_count = bit_count-1
  
  
Else
  
  
Temp_strtemp_str = temp_str + Chr $ (char_value)
  
  
End If
  
  
Next bit_count
  
  
Print #1, "user" + Cells (row_num, 1) + ":" + temp_str
  
  
Cells (row_num, 4) = temp_str
  
  
Rem obtains the next row
  
  
Row_numrow_num = row_num + 1
  
  
Loop
  
  
The password of all rem users has been generated. close the file.
  
  
Close #1
  
  
End Sub
 
 

Implementation of oracle multi-column subquery

Oracle memory structure-SGA

Oracle connect role permission changes

ORACLE Database Encoding

Oracle multi-Table query instance