Authentication Methods

（一）Dictionary Authentication

用于普通用户的认证方式

1. Password encryption

尽量认证时的密码是加密的，但通过create|alter user创建或修改用户密码的SQL不加密，只能通过SSL连接保证加密

Oracle Database automatically and transparently encrypts passwords during network (client-to-server and server-to-server) connections, using Advanced Encryption Standard (AES) before sending them across the network.

However, a password that is specified within a SQL statement (such as CREATE USER user_name IDENTIFIED BY password;) is still transmitted across the network in clear text in the network trace files. For this reason, you should have native network encryption enabled or configure Secure Sockets Layer (SSL) encryption.

1. CASE SENSITIVE and PASSWORD VERSIONS

关于PASSWORD VERSION:

Oracle Database generates a one-way hash of the user's password and stores it for use when verifying the provided login password. In order to support older clients, Oracle Database can be configured to generate the one-way hash of the user's password using a variety of different hashing algorithms.

The resulting password hashes are known as password versions, which have the short names 10G, 11G, and 12C. The short names 10G, 11G, and 12C serve as abbreviations for the details of the one-way password hashing algorithms, which are described in more detail in the documentation for the PASSWORD_VERSIONS column of the DBA_USERS view.

By default, there are currently two versions of the one-way hashing algorithm in use in Oracle Database 12c release 2 (12.2): the salted SHA-1 hashing algorithm, and the salted PKBDF2 SHA-2 SHA-512 hashing algorithm：

The salted SHA-1 hashing algorithm generates the hash that is used for the 11G password version. The salted PKBDF2 SHA-2 SHA-512 hashing algorithm generates the hash that is used for the 12C password version. 

This hash generation takes place for the same password; that is, both algorithms run for the same password. Oracle Database records these password versions in the DBA_USERS data dictionary view. When you query this view, you will see two password versions. For example:

SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME  PASSWORD_VERSIONS

--------  -----------------

ADAMS     11G, 12C

SYS        11G, 12C

不同版本的一个主要特点在于大小写敏感：

10G：大小写不敏感

'10G 11G' 或'11G'：通过初始参数SEC_CASE_SENSITIVE_LOGON来设置是否密码大小写敏感

'11G 12C'或'12C'：SEC_CASE_SENSITIVE_LOGON已deprecated，密码必须为大小写敏感

通过SQLNET.ALLOWED_LOGON_VERSION_SERVER可以设置密码版本的兼容模式：

设置为12(默认)或12a，称为exclusive mode(排他模式，只能使用'11G 12C'或'12C版本密码，其它版本无法使用，大小写必须敏感)，设置8,9,10,11均为permissive mode(容忍模式，可以设置SEC_CASE_SENSITIVE_LOGON为false，则原'11G 12C'或'12C'版本用户无法登陆(设置为true无影响)，创建新用户使用'10G 11G 12C'版本密码)

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 generates all three password versions 10G, 11G, and 12C.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 generates both 11G and 12C password versions, and removes the 10G password version.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a generates only the 12C password version.

SQLNET.ALLOWED_LOGON_VERSION_SERVER设置为12或12a时, 则要确保SEC_CASE_SENSITIVE_LOGON参数设置为true，否则'11G 12C'或'12C'版本用户无法登陆

Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.

因为SQLNET.ALLOWED_LOGON_VERSION_SERVER默认为12，此时10g库升级到12c，原来用户将无法登录，只有通过alter user更改密码变成'11G 12C'版本才行

Note：设置12a要求连接client必须为12c，设置为12时可以用11g或12c client连接

db link的密码大小写在创建时指定的密码敏感，但实际要看目标数据库设置

1. 使用PROFILE管理PASSWORD（略）

（二）Database Administrators Authentication

Administrators Authentication指以特权用户登陆，即使用as sysdba| sysoper| sysbackup| sysdg| syskm| sysrac登陆(除了sysdba/sysoper其它为12c新加)

在12.2之前它们仍会同时使用Dictionary Authentication, 但从12.2开始sys用户只使用密码文件而不再使用数据字典。实测了下12.2在关闭OS认证及删掉password file时使用sys是无法as sysdba登陆的

Starting with Oracle Database 12c Release 2 (12.2), authentication for the SYS user happens using only the password file and not using the data dictionary.

特权用户不使用数据字同认证好处在于数据库关闭时仍能登陆进行管理，认证方法包括：

1. Operating System Authentication
2. Password File Authentication

认证顺序操作系统认证>password file

Operating system authentication takes precedence over password file authentication.

1. Operating System Authentication

在Oracle相关的OS USER GROUP中的用户连接数据库时不需要用户名密码, 默认是开启的

$ vi sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(all| none| nts)  

空: 启用OS认证，本地sysdba不用输入用户名与密码

none: 禁用OS认证，本地sysdba登陆必须输入用户名与密码

all: 本地sysdba不用输入用户名与密码，所有用户无法网络连接数据库，本地连接无影响

(none,all): 等同于none

nts: windows上的没测试

Operating System Groups

在数据库安装过程中，将创建操作系统组并为其指定实际OS GROUP

Operating system groups are created and assigned specific names as part of the database installation process. The default names of the operating system groups vary depending upon your operating system, and are listed in the following table:

Oracle Universal Installer uses these default names, but, on UNIX or Linux, you can override them. On UNIX or Linux, one reason to override them is if you have multiple instances running on the same host computer in different Oracle homes. If each instance has a different person as the principal DBA, then you can improve the security of each instance by creating different groups for each instance.

On Windows, default user group names cannot be changed. The HOMENAME placeholder enables you to have different user group names when you have multiple instances running on the same host Windows computer.

Membership in a group affects your connection to the database in the following ways:

1. If you are a member of the OSDBA group, and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA administrative privilege.
2. If you are a member of the OSOPER group, and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER administrative privilege.
3. If you are a member of the OSBACKUPDBA group, and you specify AS SYSBACKUP when you connect to the database, then you connect to the database with the SYSBACKUP administrative privilege.
4. If you are a member of the OSDGDBA group, and you specify AS SYSDG when you connect to the database, then you connect to the database with the SYSDG administrative privilege.
5. If you are a member of the OSKMDBA group, and you specify AS SYSKM when you connect to the database, then you connect to the database with the SYSKM administrative privilege.
6. If you are a member of the OSRACDBA group, and you specify AS SYSRAC when you connect to the database, then you connect to the database with the SYSRAC administrative privilege.
7. If you are not a member of one of these operating system groups, and you attempt to connect as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, or SYSRAC, then the CONNECT command fails.

Connecting Using Operating System Authentication

DBAs can authenticate to the database through the operating system rather than with a database user name and password.

To enable operating system authentication of an administrative user:

1. Create an operating system account for the user.
2. Add the account to the appropriate operating-system defined groups.

You can use operating system authentication by performing one of the following actions.

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

CONNECT / AS SYSBACKUP

CONNECT / AS SYSDG

CONNECT / AS SYSKM

1. Password File Authentication

remote_login_passwordfile：

none: 不能以用户名密码形式远程或本地以sysdba/sysoper登陆

不能授权sysdba/sysoper, 可以在线修改sys密码

Exclusive(默认): 使用专用密码文件(orapw<sid>)，只允许sys用户以sysdba/sysoper远程登陆

              可以授权sysdba/sysoper，但这些用户无法远程以sysdba/sysoper登陆

可以在线修改sys密码

shared: 共同密码文件(orapw)，只允许sys用户远程以sysdba/sysoper登陆

       不能授权sysdba/sysoper, 不能在线修改sys密码

注：设置为none会同时禁用本地及远程的密码文件登陆

If REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to none.

相关视图：select username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm, account_status from v$pwfile_users;

orapwd说明：

密码文件不会自动创建，使用orapwd创建后数据库相关授予操作会反应到此文件，另外生产环境更改sys密码会产大量递归调用，可能产生死锁，所以建议使用orapwd更改sys密码

1）创建密码文件：

$ orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2

$ orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2

$ orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' SYSBACKUP=password FORMAT=12.2

2)Migrates PASSWORD FILE

$ orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

3)更改PASSWORD FILE的SYS密码（其它特权用户密码无法更改）：

$ orapwd FILE='/u01/oracle/dbs/orapworcl' SYS=Y INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y  

4)查看PASSWORD FILE FORMAT

$ orapwd DESCRIBE FILE='orapworcl'

5) 使用format=12，忽略sys密码复杂度

orapwd的format选项默认为12.2，它会对sys密码进行复杂度检查：

orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle format=12

6) format=12.2还会使用profile中的以下设置限制sysdba/sysoper

FAILED_LOGIN_ATTEMPT

INACTIVE_ACCOUNT_TIME

PASSWORD_GRACE_TIME

PASSWORD_LIFE_TIME

PASSWORD_LOCK_TIME

7）创建密码文件后需同步数据字典中特权用户：

a. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

REVOKE SYSDBA FROM non-SYS-user;

GRANT SYSDBA TO non-SYS-user;

b. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

REVOKE SYSOPER FROM non-SYS-user;

GRANT SYSOPER TO non-SYS-user;

c. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSBACKUP ='TRUE';

REVOKE SYSBACKUP FROM non-SYS-user;

GRANT SYSBACKUP TO non-SYS-user;

d. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDG='TRUE';

REVOKE SYSDG FROM non-SYS-user;

GRANT SYSDG TO non-SYS-user;

e. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSKM='TRUE';

REVOKE SYSKM FROM non-SYS-user;

GRANT SYSKM TO non-SYS-user;

Note: SYSRAC不会使用password file