Password Authentication mode for Oracle Top account SYS

Source: Internet
Author: User
Tags dba

CONNECT Username/[email protected] as SYSDBA
Connect means connecting to
Username refers to the user name
Password means a password.
ServerName refers to the name of the service
As SYSDBA is logged in as a database administrator
I don't know if I can help you.

Oracle-provided authentication mode
1. Operating system authentication (anonymous login does not verify user name and password)
Conn/as SYSDBA is both operating system certified
Can only be used locally because the database software is installed on the operating system, the user must be a DBA group;
With direct access to the operating system Oracle does not need to be limited.

Reasons for delegating permissions on Unix-like systems
For ordinary users
Add to the Oinstall DBA Group
If you add only the DBA group, you cannot
Because the Oracle installation directory is basically 750 permissions, even if you do not have access to the Directory permission library function, you need to change
For the root user
Because the first step in UNIX permission delegation is to verify that the UID equals 0
So even if you add to the Oinstall DBA group, you can't log in

Remember, don't use a user other than Oracle to operate Oracle
It's like the boss can't go to the front desk. Do not violate these planned rules.
2. password file authentication
Location of password file storage $ORACLE _home/dbs/orapw$oracle_sid

There are two special types of identity sysdba and Sysoper in Oracle, and when DBAs need to perform maintenance management operations on the database,
Must have one of these two kinds of special identities.
When the database is not open, using the database built-in account is unable to log into the database,
However, users with SYSDBA or Sysoper permissions can log in.
There are two ways to authenticate a user with two types of special permissions: OS authentication and password file authentication.


Whether an Oracle database is managed using OS authentication or password file authentication depends on the following three factors:
1.SQLNET. Parameter sqlnet.authentication_services settings in the ora parameter file
Parameter remote_login_passwordfile settings in the 2.PFILE (SPFILE) parameter file
3. password file Orapw$sid (Linux) | Pwd$sid.ora (Windows) is present

The basic order of Oracle Permissions authentication is this:
The Sqlnet.authentication_services setting value is used to determine whether to use OS authentication or password file authentication.
If you use password file authentication, you should look at the following two conditions:
If the Remote_login_passwordfile parameter is set to not none,
and the password file exists, you can normally use password file authentication, otherwise it will fail.


Sqlnet. Authentication_services parameters
In Sqlnet.ora (located in the $oracle_home/network/admin directory) file, you need to change the text editor to open the changes directly,
The values for different operating system sqlnet.authentication_services will vary, usually we will use some of the following settings:
Under Linux none all nts not set or beq four cases
Start monitoring Lsnrctl start for network connection go password file
Use the external command to set the SYS password to a
Orapwd file= $ORACLE _home/dbs/orapw$oracle_sid password=a force=y
Linux Platform Testing
The first default is not set or set to BEQ
[Email protected] admin]$ grep-v ' # ' Sqlnet.ora

NAMES. Directory_path= (TNSNames, Ezconnect)

[Email protected] admin]$
$ sqlplus sys/[email protected] as Sysdba
Can log in
$ sql/as SYSDBA
Can log in
Conclusion: Password authentication and OS authentication are enabled when no setting or value is beq

The second scenario is set to None
[Email protected] admin]$ grep-v ' # ' Sqlnet.ora

NAMES. Directory_path= (TNSNames, Ezconnect)
Sqlnet. Authentication_services= (None)

[Email protected] admin]$

[Email protected] admin]$ Sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Sat Apr 30 04:35:02 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Sql> Conn/as SYSDBA
ERROR:
ORA-01031: Insufficient Permissions


Sql> Conn sys/a as Sysdba
Connected.
Sql>
Sql> Conn sys/[email protected] as Sysdba
Connected.
Sql>
Conclusion: (none) OS authentication is blocked must go password file authentication

The third case is set to all
Sql> Conn/as SYSDBA
Connected.

Sql> Conn sys/[email protected] as Sysdba
ERROR:
Ora-12641:authentication Service failed to initialize


Warning:you is no longer connected to ORACLE.
Sql>

Conclusion: All password file authentication is blocked only by OS authentication

Fourth case setting NTS
[Email protected] admin]$ grep-v ' # ' Sqlnet.ora

NAMES. Directory_path= (TNSNames, Ezconnect)
Sqlnet. Authentication_services= (NTS)

[Email protected] admin]$

Sql> Conn/as SYSDBA
ERROR:
ORA-01031: Insufficient Permissions


Sql> Conn sys/a as Sysdba
Connected.
Sql> Conn sys/[email protected] as Sysdba
Connected.
Sql>

Conclusion: NTS only password file authentication

Windows Platform Testing

The first set of default settings nts

Sql> Conn/as SYSDBA
is connected.
sql> alter user SYS identified by A;

The user has changed.

Sql> Conn/as SYSDBA
is connected.
Sql> Conn sys/a as Sysdba
is connected.
Sql> Conn sys/[email protected] as Sysdba
is connected.
Sql>
Conclusion: Setting NTS (and Linux inconsistencies) password and OS authentication are enabled

The second one is set to none

Sql> Conn/as SYSDBA
ERROR:
Ora-01031:insufficient Privileges


Sql> Conn sys/a as Sysdba
is connected.
Sql> Conn sys/[email protected] as Sysdba
is connected.
Sql>
Conclusion: Set to None (consistent with Linux) only password-certified OS authentication is blocked

The third type is set to all

Sql> Conn/as SYSDBA
ERROR:
ORA-12641: Validation Service failed to initialize


Sql> Conn sys/a as Sysdba
ERROR:
ORA-12641: Validation Service failed to initialize


Sql> Conn sys/[email protected] as Sysdba
ERROR:
ORA-12641: Validation Service failed to initialize

Conclusion: This parameter is blocked for both OS and password authentication under Windows

The fourth type does not set

Sql> Conn/as SYSDBA
ERROR:
Ora-01031:insufficient Privileges


Sql> Conn sys/a as Sysdba
is connected.
Sql> Conn sys/[email protected] as Sysdba
is connected.
Sql>
Conclusion: only password-certified OS authentication is allowed to be blocked


The fifth type is set into BEQ
Sql> Conn/as SYSDBA
ERROR:
Ora-01031:insufficient Privileges


Warning: You are no longer connected to ORACLE.
Sql> Conn sys/a as Sysdba
is connected.
Sql> Conn sys/[email protected] as Sysdba
is connected.
Sql>
Conclusion: only password-certified OS authentication is blocked

Remote_login_passwordfile parameters
The setup of this system parameter sets up a method for the database to use the password file, which can be set to a value of three:

Remote_login_passwordfile = NONE
Do not use password files

Remote_login_passwordfile = EXCLUSIVE
Use a password file, but only one DB instance is available for use

Remote_login_passwordfile = SHARED
Multiple DB instances share a password file, which cannot be added to the password file by other database users as privileged users.

alter system Set Remote_login_passwordfile = NONE scope = SPFile;

Grant SYSDBA to Scott;

SELECT * from V$pwfile_users;

Password file
Storing passwords for SYSDBA sysoper authentication can, of course, elevate ordinary users to this identity
SYSDBA Super Admin Conn/as sysdba
Sysoper operator Conn/as Sysoper
The difference between two identities
SYSDBA not controlled by permissions
Sysoper and Sysdba are missing permissions than SYSDBA:
1. Cannot build Library
2. Unable to start database to restricted session mode
3. Cannot perform hot backup
4. Incomplete recovery of the database is not possible
5. Cannot query Dba_ data dictionary
In fact, we usually use SYSDBA instead of sysoper.
[email protected]> Conn/as SYSDBA
Connected.
[email protected]> Show User
USER is "SYS"
[email protected]>
Just log in with as SYSDBA Show user is always sys

[email protected]> Conn/as sysoper
Connected.
[email protected]> Show User
USER is ' public '
[email protected]>
Just use as Sysoper sign in Show user is always public

To modify the password of a super user
1. Use Administrator (SYS system) User login to modify inside the database
[email protected]> Show user
USER is "SYS"
[email protected]> ALTER USER SYS identified by SYS;

User altered.

 [email protected] > This changes the SYS password to SYS
   2. Directly modify the password file but the password file is binary only with the ORAPWD command to recreate
 [email protected] >!orapwd
 usage:orapwd file=< fname> password=<password> entries=<users> force=<y/n>
 [email protected] >! Orapwd file= $ORACLE _home/dbs/orapw$oracle_sid password=oracle entries=1 force=y

[email protected]> This sets the sys password to Oracle
Authorized
[email protected]> Grant sysdba to U1;

Entries=<users> The default value is 4-how many users can be accommodated
Set the Rules
0-4 can accommodate 5
5-8 can accommodate 9
9-12 can accommodate 13
etc...

[Email protected] dbs]$ orapwd file= $ORACLE _home/dbs/orapw$oracle_sid password=oracle Entries=1 force=y

The role of the o7_dictionary_accessibility parameter

SYS is the superuser default connection within the database library must use as SYSDBA identity
This parameter can be used without using the as SYSDBA identity and directly using the SYS connection is of little significance.
Sql> Conn/as SYSDBA
Connected.
Sql>
Sql> Show Parameter O7

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_dictionary_accessibility Boolean FALSE
Sql>
sql> alter user SYS identified by A;

User altered.

Sql> Conn sys/a
ERROR:
Ora-28009:connection as SYS should be as SYSDBA or sysoper


Warning:you is no longer connected to ORACLE.
Sql> Conn/as SYSDBA
Connected.
Sql> Show Parameter O7

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_dictionary_accessibility Boolean FALSE

Sql> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE Scope=spfile;

System altered.

sql> Startup force
ORACLE instance started.

Total System Global area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 75499088 bytes
Database buffers 205520896 bytes
Redo buffers 2973696 bytes
Database mounted.
Database opened.
Sql> Show Parameter O7

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_dictionary_accessibility Boolean TRUE
Sql> Conn sys/a
Connected.
Sql> Select COUNT (*) from Session_roles;

COUNT (*)
----------
32

Sql> Select COUNT (*) from Session_privs;

COUNT (*)
----------
161

Sql> Conn/as SYSDBA
Connected.
Sql> Select COUNT (*) from Session_roles;

COUNT (*)
----------
0

Sql> Select COUNT (*) from Session_privs;

COUNT (*)
----------
166

Sql>


Permission assignment of Sys between versions

oracle805
Svrmgrl
Server Manager line abbreviation
Managing startup stops for the underlying database
Connect Internal

SYS database owner
System Manager

Sqlplus can only do query

Oracle8i

Svrmgrl
Server Manager line abbreviation
Managing startup stops for the underlying database
Connect Internal

As SYSDBA new but has not yet completely replaced SVRMGRL connect internal
Sqlplus upgrade to can start library stop library


Oracle9i

As SYSDBA completely replaces the SVRMGRL and connect internnal
exists outside the database so no role (role)

Administrators inside the SYS/A database
Have a role

***************************************************************************

Users in the Oinstall and DBA groups, log on to the server natively, directly through the OS authentication, log in directly to the database
Oracle user is used to install Oracle database software, which user installs, which user action

Sqlplus user/[email protected]:p ort/service_names;---go password file
Sqlplus sys/[email protected]:p ort/service_names as Sysdba
SYS user log in as SYSDBA, directly find the password in the password file, determine whether the password is consistent with the input
As Sysoper directly find the password in the password file, determine whether the password is consistent with the input
Normal user login, take the password to the data dictionary to get the password, only the database opened in the case can be executed

As Sysdba|sysoper (descriptor) database is not started, if you want to start the database, you need to verify the identity
If the password for the as SYSDBA is also in the data dictionary, the database is closed and cannot be accessed
Data dictionary, causing no one to start the database


As SYSDBA: Switch to Super Administrator status, switch to internal SYS
As Sysoper: operator, switch to internal public

As SYSDBA connected to the database, the display of users are Sys,sys users are not directly logged in, must be logged in as Sysdba
As SYSDBA the connector to switch, the user shown is public

As Sysoper connected to the database, the displayed users are public


Password file
$ORACLE _home/dba/orapw$oracle_sid

[email protected]> desc dba_users;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
USERNAME not NULL VARCHAR2 (30)
USER_ID not NULL number
PASSWORD VARCHAR2 (30)
Account_status not NULL VARCHAR2 (32)
Lock_date DATE
Expiry_date DATE
Default_tablespace not NULL VARCHAR2 (30)
Temporary_tablespace not NULL VARCHAR2 (30)
CREATED not NULL DATE
Profile is not NULL VARCHAR2 (30)
Initial_rsrc_consumer_group VARCHAR2 (30)
External_name VARCHAR2 (4000)

[email protected]> select Username,password from dba_users where username= ' SCOTT ';

USERNAME PASSWORD
------------------------------ ------------------------------
SCOTT 4dd35d67f0372586

Conn Sys/pass off by default, do not let the connection

Sqlplus/as sysdba; disable
Vim $ORACLE _home/network/admin/sqlnet.ora
Sqlnet. Authentication_services= (beq); A value can be not added (), generally uppercase

beq None All nts
The effect is different because of the different platform

BEQ: Default
Sqlplus/as SYSDBA: Allow login
Sqlplus sys/[email protected]:p ort/service_name as SYSDBA: Allow login

None
Sqlplus/as sysdba; refuse.
Sqlplus sys/[email protected]:p ort/service_name as SYSDBA; Allow Login

All
Sqlplus/as sysdba; Allow Login
Sqlplus sys/[email protected]:p ort/service_name as SYSDBA; reject

Nts
Sqlplus/as sysdba; Allow Login
Sqlplus sys/[email protected]:p ort/service_name as SYSDBA; reject


[email protected]> show parameter Remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Remote_login_passwordfile string EXCLUSIVE


$ORACLE the _home/dba/orapw$oracle_sid password file does not exist:
Orapwd file= $ORACLE _home/dbs/orapw$oracle_sid password=abc force=y
Changes in the password file do not affect the password in the data dictionary, alter modifies the user's password, and the password in the password file also changes

Experiment One:
The first step:
Sqlplus/nolog
Conn/as SYSDBA
Seleect Username,password from Dba_users where username= ' SYS ';

Step Two:
Orapwd file= $ORACLE _home/dbs/orapw$oracle_sid password=abc force=y;--Change password in password file

Step Three:
Sqlplus/nolog
Conn/as SYSDBA
Seleect Username,password from Dba_users where username= ' SYS ';

Result: The same as the result set in the first and third steps, change the password in the password file, the password in the data dictionary does not change


Experiment Two:
The first step:
Strings $ORACLE _home/dbs/orapw$oracle_sid

Step Two:
Sqlplus/nolog
Conn/as SYSDBA
Seleect Username,password from Dba_users where username= ' SYS ';
ALTER user SYS identified by sys123;--to modify the password in the data dictionary
Seleect Username,password from Dba_users where username= ' SYS ';

Step Three:
Strings $ORACLE _home/dbs/orapw$oracle_sid

Result: The result set of the first and third steps is different, change the password in the data dictionary, and the password in the password file changes.

Excerpt from: http://blog.csdn.net/lsz_qh/article/details/7349110

Password Authentication mode for Oracle Top account SYS

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.