Oracle密码文件&认证

url:

https://blog.csdn.net/weixin_35804557/article/details/116351350

http://blog.itpub.net/20674423/viewspace-2676638/

1.1、密码文件生成语法

orapwd FILE=filename 
[FORCE={y|n}] 
[ASM={y|n}] 
[DBUNIQUENAME=dbname] 
[FORMAT={12.2|12}] 
[SYS={y|n|password|external('sys-external-name')|global('sys-directory-DN')}]
[SYSBACKUP={y|n|password|external('sysbackup-external-name')|global('sysbackup-directory-DN')}]
[SYSDG={y|n|password|external('sysdg-external-name')|global('sysdg-directory-DN')}] 
[SYSKM={y|n|password|external('syskm-external-name')|global('syskm-directory-DN')}] 
[DELETE={y|n}] 
[INPUT_FILE=input-fname]   

1.2、密码文件生成语法说明

Argument	Description
FILE	If the DESCRIBE argument is not included, then specify the name to assign to the new password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory. If the DESCRIBE argument is included, then specify the name of an existing password file.
FORCE	(Optional) If y, permits overwriting an existing password file. It also clears CRS resources, if they already have the password file registered.
ASM	(Optional) If y, create an Oracle ASM password file in an Oracle ASM disk group. If n, the default, create a password file in the operating system file system. When the DBUNIQUENAME argument is specified, the password file is a database password file. When the DBUNIQUENAME argument is not specified, the password file can be a database password file or an Oracle ASM password file.
DBUNIQUENAME	Unique database name used to identify database password files residing in an ASM disk group only. This argument is required when the database password file is stored on an Oracle ASM disk group. This argument is ignored when an Oracle ASM password file is created by setting the ASM argument to y.
FORMAT	(Optional) Specify one of the following values: 12.2, the default, creates the password file in 12.2. format. This format supports granting administrative privileges to external users and enables SSL and Kerberos authentication for administrative users. 12 creates the password file in Oracle Database 12c format. This format supports the SYSBACKUP, SYSDG, and SYSKM administrative privileges.
SYS	(Optional) This argument specifies if SYS user is password, externally, or globally authenticated. This argument can be set to y, n, password, external('sys-external-name'), or global(sys-directory-DN). If SYS=y and INPUT_FILE is specified to migrate password file entries, then you will be prompted to enter the new password for the SYS administrative user. If password, then you will be prompted to enter the password for the SYS administrative user. If external('sys-external-name'), then replace sys-external-name with the external name for SSL or Kerberos authentication for the SYS administrative user. If global(sys-directory-DN), then specify the directory service name for the global SYS user.
SYSBACKUP	(Optional) Creates SYSBACKUP entry. This argument specifies if SYSBACKUP user is password, externally, or globally authenticated. This argument can be set to y, n, password, external('sysbackup-external-name'), or global(sysbackup-directory-DN). If password, then you will be prompted to enter the password for the SYSBACKUP administrative user. If external('sysbackup-external-name'), then replace sysbackup-external-name with the external name for SSL or Kerberos authentication for the SYSBACKUP administrative user. If global(sysbackup-directory-DN), then specify the directory service name for the global SYSBACKUP user.
SYSDG	(Optional) Creates SYSDG entry. This argument specifies if SYSDG user is password, externally, or globally authenticated. This argument can be set to y, n, password, external('sysdg-external-name'), or global(sysdg-directory-DN). If password, then you will be prompted to enter the password for the SYSDG administrative user. If external('sysdg-external-name'), then replace sysdg-external-name with the external name for SSL or Kerberos authentication for the SYSDG administrative user. If global(sysdg-directory-DN), then specify the directory service name for the global SYSDG user.
SYSKM	(Optional) Creates SYSKM entry. This argument specifies if SYSKM user is password, externally, or globally authenticated. (Optional) This argument can be set to y, n, password, external('syskm-external-name'), or global(syskm-directory-DN). If password, then you will be prompted to enter the password for the SYSKM administrative user. If external('syskm-external-name'), then replace syskm-external-name with the external name for SSL or Kerberos authentication for the SYSKM administrative user. If y, creates a SYSKM entry in the password file. You are prompted for the password. The password is stored in the created password file. If n, no SYSKM entry is created in the password file. Note: The y and n values in the SYSKM argument are deprecated in Oracle Database 12c Release 2 (12.2) and may be desupported in a future release. If global(syskm-directory-DN), then specify the directory service name for the global SYSKM user.
DELETE	(Optional) If y, delete the specified password file. If n, the default, create the specified password file.
INPUT_FILE	(Optional) Name of the input password file. ORAPWD migrates the entries in the input file to a new password file. This argument can be used to convert a password file from one format to another, for example from 12 format to 12.2 format. This argument also can be used to reset the password for the SYS administrative user. ORAPWD cannot migrate an input password that is stored in an Oracle ASM disk group.
DESCRIBE	Describes the properties of the specified password file, including the FORMAT value (12.2 or 12).

1.3、密码文件命名及存储位置

Platform	Required Name	Required Location
UNIX and Linux	orapwORACLE_SID	ORACLE_BASE/dbs
Windows	PWDORACLE_SID.ora	ORACLE_BASE\database

unix下寻找口令文件的顺序是oracle_home\dbs\orapwSID,如果找不到，会在dbs目录想寻找orapw文件，如果找不到9i下会报错，数据库可以启动到mount状态，当然再次alter database open之后还是可以打开数据库。10g下open数据库时不在检查口令文件。win下如果在oracle_home/database/下找不到pwdsid.ora文件，不会寻找任何文件。这里所说的win下如果找不到PWDsid.ora文件之后不会寻找任何文件其实是相对unix下如果找不到orapwSID文件之后会寻找orapw文件而言的。其实win下寻找口令文件也是有顺序的。寻找顺序是这样的：首先寻找注册表中ora_sid_pwfile环境变量所指向的口令文件，其次寻找ora_pwfile执行的口令文件，{BANNED}{BANNED}最佳佳后才寻找$ORACLE_HOME/database/PWDsid.ora   （新版本需验证此内容）

1.4、密码新特性简介
  1）新的 password hash 算法

Oracle对Oracle Database 12c中的用户密码哈希进行了改进， 通过使用基于PBKDF2的SHA512哈希算法，而不是简单的SHA1哈希，密码哈希更安全, 从11g起user$.spare4列存储着密码的哈希值。在12.1.0.2版本时spare4列有3部分组成（S:H:T）.12.2时只剩下（S:T ）2部分。

S部分和11g时的算法一样，长度60 chars,是基于SHA1的哈希。

H部分是基于MD5的哈希，长度为32 chars, 也可能是因为MD5 hash更方便入侵者的暴力破解，在12.2 版本时从Spare4列去掉该部分。

T部分是从12.1.0.2版本增加，长度为160 chars, 使用的是基于PBKDF2-based SHA512的算法。该算法后部分32chars 的验证数据部分是随机生成。更加安全。

  2）新的password verify function

12c新引入了verify function 有ora12c_verify_function, ora12c_strong_verify_function、ora12c_stig_verify_function ，而且可能在版本之间的profile默认的verify function名都不一样。 utlpwdmg.sql:

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX  UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

INACTIVE_ACCOUNT_TIME UNLIMITED

PASSWORD_ROLLOVER_TIME 0

PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

  3）密码文件可以存储到ASM
      因文件位于ASM中，需要设置DBUNIQUENAME参数。
     无环境验证暂无此内容
 
  4）密码自动从primary同步到standby端在Datagrard环境中

    无环境验证暂无此内容

  5）新的密码认证协议

         升级12C注意事项： 连接失败 ORA-28040 ORA-1017

密码版之前写过关于认证的协议的。如果你的数据库是从12c以前的版本升上来的，在升级时会提示去掉SEC_CASE_SENSITIVE_LOGON=FALSE参数如下，但是如果你没有去掉SEC_CASE_SENSITIVE_LOGON=FALSE，那么升级后登录可以会遇到ora-1017错误，即使密码是正确的，甚至修改在升级后再修改密码(新的密码版本将为”11G 12C”)。解决方法是：change SEC_CASE_SENSITIVE_LOGON=TRUE, –the default.另外注意如果是Data Pump导入创建的用户，本会和导出时版本一致。

RECOMMENDED ACTIONS
===================
+ Consider removing the following DEPRECATED initialization parameters.
They are not OBSOLETE in version 12.2.0.1.0
but probably will be OBSOLETE in a future release.
Parameter
——————————
sec_case_sensitive_logon
Note:
Oracle notes that the term “version” in the allowed_logon_version_server parameter name refers to the version of the authentication protocol. It does NOT refer to the Oracle release version.
sqlnet.allowed_logon_version_server, depending on the version of the authentication protocol.
sqlnet.allowed_logon_version_server=12a: For Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later.
sqlnet.allowed_logon_version_server=12: For the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended).
sqlnet.allowed_logon_version_server=11: For Oracle Database 11g authentication protocols (default).
sqlnet.allowed_logon_version_server=10: For Oracle Database 10g authentication protocols.
sqlnet.allowed_logon_version_server=8: For Oracle8i authentication protocol

2、OS 认证&密码文件认证
2.1、认证过程


2.2、administrator权限列表

Administrative Privilege	Operations Authorized
SYSDBA	Perform STARTUP and SHUTDOWN operations ALTER DATABASE: open, mount, back up, or change character set CREATE DATABASE DROP DATABASE CREATE SPFILE ALTER DATABASE ARCHIVELOG ALTER DATABASE RECOVER Includes the RESTRICTED SESSION privilege This administrative privilege allows most operations, including the ability to view user data. It is the most powerful administrative privilege.
SYSOPER	Perform STARTUP and SHUTDOWN operations CREATE SPFILE ALTER DATABASE: open, mount, or back up ALTER DATABASE ARCHIVELOG ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.) Includes the RESTRICTED SESSION privilege This privilege allows a user to perform basic operational tasks, but without the ability to view user data.
SYSBACKUP	This privilege allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus. See Oracle Database Security Guide for the full list of operations allowed by this administrative privilege.
SYSDG	This privilege allows a user to perform Data Guard operations. You can use this privilege with either Data Guard Broker or the DGMGRL command-line interface. See Oracle Database Security Guide for the full list of operations allowed by this administrative privilege.
SYSKM	This privilege allows a user to perform Transparent Data Encryption keystore operations. See Oracle Database Security Guide for the full list of operations allowed by this administrative privilege.
SYSRAC	This privilege allows the Oracle agent of Oracle Clusterware to perform Oracle Real Application Clusters (Oracle RAC) operations. See Oracle Database Security Guide for the full list of operations allowed by this administrative privilege.

2.3、OS认证群组&dba权限对应清单

Operating System Group	UNIX or Linux User Group	Windows User Group
OSDBA	dba	ORA_DBA (for all Oracle homes) ORA_HOMENAME_DBA (for each specific Oracle home)
OSOPER	oper	ORA_OPER (for all Oracle homes) ORA_HOMENAME_OPER (for each specific Oracle home)
OSBACKUPDBA	backupdba	ORA_HOMENAME_SYSBACKUP
OSDGDBA	dgdba	ORA_HOMENAME_SYSDG
OSKMDBA	kmdba	ORA_HOMENAME_SYSKM
OSRACDBA	racdba	ORA_HOMENAME_SYSRAC

2.4、Remote_login_passwordfile 参数设置
       Remote_login_passwordfile 参数有三个值：
          1）None：不使用口令文件验证即禁止远程超级权限登陆。

          2）Exclusive： 表示实例独占使用口令文件，也就是各自实例使用单独的口令文件
          3）Shared: 表示多个实例共享一个口令文件，一用于RAC. 静态参数禁止更改口令文件。

3、V$PWFILE_USERS
3.1、v$pwfile_users的定义

lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges.

Column	Datatype	Description
USERNAME	VARCHAR2(128)	Name of the user that is contained in the password file
SYSDBA	VARCHAR2(5)	Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER	VARCHAR2(5)	Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM	VARCHAR2(5)	Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)
SYSBACKUP	VARCHAR2(5)	Indicates whether the user can connect with SYSBACKUP privileges (TRUE) or not (FALSE)
SYSDG	VARCHAR2(5)	Indicates whether the user can connect with SYSDG privileges (TRUE) or not (FALSE)
SYSKM	VARCHAR2(5)	Indicates whether the user can connect with SYSKM privileges (TRUE) or not (FALSE)
ACCOUNT_STATUS	VARCHAR2(30)	Account status: OPEN EXPIRED EXPIRED (GRACE) LOCKED (TIMED) LOCKED EXPIRED & LOCKED (TIMED) EXPIRED & LOCKED EXPIRED (GRACE) & LOCKED
PASSWORD_PROFILE	VARCHAR2(128)	Password profile name
LAST_LOGIN	TIMESTAMP(9) WITH TIME ZONE	The time of the last user login
LOCK_DATE	DATE	Date the account was locked if account status was LOCKED
EXPIRY_DATE	DATE	Date of expiration of the account
EXTERNAL_NAME	VARCHAR2(1024)	Shows Certificate DN or Principal Name of externally authenticated users
AUTHENTICATION_TYPE	VARCHAR2(8)	Indicates the authentication mechanism for the user: EXTERNAL - CREATE USER user1 IDENTIFIED EXTERNALLY; GLOBAL - CREATE USER user2 IDENTIFIED GLOBALLY; PASSWORD - CREATE USER user3 IDENTIFIED BY password;
COMMON	VARCHAR2(3)	This column has a value of YES if an administrative privilege (for example, SYSDBA) was granted with CONTAINER=ALL. Otherwise, the column has a value of NO.
PASSWORD_VERSIONS	VARCHAR2(12)	Shows the list of versions of the password hashes (also known as "verifiers") existing for the account. The values for this column can include: 10G: If an old case-insensitive ORCL hash exists 11G: If a SHA-1 hash exists 12C: If a de-optimized PBKDF2-based hash exists For more information about the 12C verifier, see . Note that any combination of these verifiers can exist for any given account.
CON_ID	NUMBER	The ID of the container to which the data pertains. Possible values include: 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. 1: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

3.2 、v$pwfile_users&口令文件的关系

如果密码文件不存在或者名称错误，查询v$pwfile_users将得到空记录
添加sysdba等权限用户，会记录到密码文件和v$pwfile_users中
到回收sysdba等权限用户，密码文件记录依然存在，但是v$pwfile_users中无对应记录