Oracle login Method

Oracle login Method

1. There are two ways to manage Oracle login authentication:
1. Operating System Authentication
2. Password Authentication
Ii. Overview and implementation of operating system authentication
1. Warm up a small experiment
SQL> -- first, shut down the database.
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> -- use sys to check if you can log on.
SQL> conn/as sysdba
Already connected to the idle routine.
SQL> show user
USER is "SYS"
SQL> -- in fact, we are currently logged on in the operating system logon mode. We will discuss in detail how to implement the operating system logon mode.
SQL> -- let's try again. We can use a user that does not exist in oracle to log on.
SQL> conn xiaoming/as sysdba
Already connected to the idle routine.
SQL> show user
USER is "SYS"
SQL> -- in the above example, we can see that in the operating system authentication logon mode, any user can log on to Oracle AS sysdba, but the actual user is still sys;

2. How to Use the OS logon mode in Windows
First, when installing the Oracle database in Windows, log on to the operating system as administrator;
We need to find the sqlnet. ora file in the \ NETWORK \ ADMIN directory of $ oracle_home (under the oracle Installation home directory). For example, the local path is:
E: \ app \ neusoft \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN \ sqlnet. ora
 
The contents of this file are summarized as follows:
# Sqlnet. oraNetwork Configuration File: E: \ app \ neusoft \ product \ 11.2.0 \ dbhome_1 \ network \ admin \ sqlnet. ora
# Generated by Oracle configuration tools.
 
# Thisfile is actually generated by netca. But if MERs choose
# Install "Software Only", this file wont exist and without the native
# Authentication, they will not be able to connect to the database on NT.
 
AUTHENTICATION_SERVICES = (ETS)

 
DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

In the content, # indicates the meaning of the comment. The translation of the comment is
The Sqlnet. ora configuration path is: E: \ app \ neusoft \ product \ 11.2.0 \ dbhome_1 \ network \ admin \ sqlnet. ora
This file is automatically generated by the Configuration tool.
This file is actually generated by netca (NetConfiguration Assistant. However, if you select "Install database software only" during installation, this file will not generate
If you want to use the operating system authentication method to log on to oracle, you need to make the following settings: SQLNET. AUTHENTICATION_SERVICES = (ETS)
Translating REMOTE_LOGIN_PASSWORDFILE = NONE and remote_login_passwordfile is a remote logon password file. The default value of this parameter is exclusive, exclusive;
Setting statement: SQL> alter system set remote_login_passwordfile = none scope = spfile;
The scope must be spfile. After the statement is submitted, the database instance must be restarted to take effect.
3. How to Use the OS logon mode in unix
First, set sqlnet. AUTHENTICATION_SERVICES = (beq) in the sqlnet. ora file. If the file does not exist, you can create it manually. Second, like windows,
Use SQL> alter system set remote_login_passwordfile = none scope = spfile; set remote_login_passwordfile to none
Iii. Password Authentication overview and implementation methods
1. First, modify sqlnet. AUTHENTICATION_SERVICES = (none) in the sqlnet. ora file)
2. Execute showparameter remote_login_passwordfile in sqlplus to check whether the value is EXCLUSIVE or SHARED;
3. If the value of the remote_login_passwordfile parameter is null or NONE, you need to use the statement SQL> alter system set remote_login_passwordfile = EXCLUSIVE scope = spfile; restart the instance to take effect;
4. Use the owdpwd command to generate a password file. The file must be the database folder under the installation database Home Directory ($ oracle_home, the file name must start with orapw + database instance name in a unix operating system,
If the operating system is windows, it should be PWD + database instance name. ora, password is the logon password specified for the sys user, entries is optional, refers to the maximum number of users with sysdba/sysoper permissions;
The format is as follows:
Orapwd file = E: \ app \ neusoft \ product \ 11.2.0 \ dbhome_1 \ database \ orapworclpassword = neusoft entries = 5;
5. Check the v $ pwfile_users view to view the list of users with SYSDBA/SYSOPER permissions in the database;
6. You can use the authorization statement to grant sysdba and sysoper permissions to any specified user:
GRANT sysdba, sysoper TO system;
SELECT * from v $ PWFILE_USERS;