Home » RDBMS Server » Security » Authentication and passwordfile (Oracle database 12)
Authentication and passwordfile [message #670769] Wed, 25 July 2018 02:48 Go to next message
madem
Messages: 2
Registered: July 2018
Junior Member
Hello

I need your help to understand what is passwordfile and how does it works ?
I know it's possible to connecter "as sysdba" or "as sysoper". Tell me if i'm wrong, but each user that have sysdba or sysoper system privileges can connect by specify "as sysdba" or "as sysoper" whitout enter a password (thank's to passwordfile), is it correct ?

So, if a set remote_password_file to exclusive, each user (i'm talking about os user) can connect to the database by using "sys as sysdba" ?

I read into the CIS that remote_password_file must be set to None, i just need to understand why.
I'm not rellay sure to uinderstand well about passwordfile authentication. Tell me if i'm wrong, but it's a file where password of each user who have privilege "sysdba /sysoper" are stocked ? And we use it to authenticate to the database whitout have to enter any password ?

So if you can help me to understand about passwordfile, it will be very nice Smile

Thank's Smile
Re: Authentication and passwordfile [message #670772 is a reply to message #670769] Wed, 25 July 2018 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I know it's possible to connecter "as sysdba" or "as sysoper". Tell me if i'm wrong, but each user that have sysdba or sysoper system privileges can connect by specify "as sysdba" or "as sysoper" whitout enter a password (thank's to passwordfile), is it correct ?
Only if you configure the DB/instance to allow this.

Quote:
So, if a set remote_password_file to exclusive, each user (i'm talking about os user) can connect to the database by using "sys as sysdba" ?
Only if they know the password.

Quote:
Tell me if i'm wrong, but it's a file where password of each user who have privilege "sysdba /sysoper" are stocked ?
Correct, see McPwfile: check your Oracle password files

Database Administrator's Guide, Chapter 1 Getting Started with Database Administration, Section Database Administrator Authentication

Re: Authentication and passwordfile [message #670774 is a reply to message #670769] Wed, 25 July 2018 03:02 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
The CIS recommendation for remote_login_passwordfile is "this value should be set according to the needs of the
organization". Virtually all organizations will need to set it to "exclusive".

btw, it isn't just SYSDBA and SYSOPER (which you shouldn't be using on a regular basis any more) it is also SYSBACKUP, SYSDG, and SYSKM.
Re: Authentication and passwordfile [message #670776 is a reply to message #670772] Wed, 25 July 2018 03:13 Go to previous messageGo to next message
madem
Messages: 2
Registered: July 2018
Junior Member
Quote:

I know it's possible to connecter "as sysdba" or "as sysoper". Tell me if i'm wrong, but each user that have sysdba or sysoper system privileges can connect by specify "as sysdba" or "as sysoper" whitout enter a password (thank's to passwordfile), is it correct ?

Only if you configure the DB/instance to allow this
Ok, and to allow this, i need to specify a good value (like exclusive) to the parameter remote_login_passwordfile ?

Quote:
o, if a set remote_password_file to exclusive, each user (i'm talking about os user) can connect to the database by using "sys as sysdba" ?

Only if they know the password.
I dont understand, i thought the goal of passwordfile was to not enter the password when connecting


Quote:
The CIS recommendation for remote_login_passwordfile is "this value should be set according to the needs of the
organization". Virtually all organizations will need to set it to "exclusive".

btw, it isn't just SYSDBA and SYSOPER (which you shouldn't be using on a regular basis any more) it is also SYSBACKUP, SYSDG, and SYSKM.
Ok, thx for this precision Smile
Re: Authentication and passwordfile [message #670777 is a reply to message #670776] Wed, 25 July 2018 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I dont understand, i thought the goal of passwordfile was to not enter the password when connecting
Not at all; you have to read the section of the documentation I pointed you to, I won't copy and paste its content here.

Re: Authentication and passwordfile [message #670781 is a reply to message #670776] Wed, 25 July 2018 07:25 Go to previous message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Quote:
I dont understand, i thought the goal of passwordfile was to not enter the password when connecting
Not at all. It is to provide password authentication when connecting as sysdba or sysoper when OS authentication is not in play.

Consider -
You are connected (os connection) to the server on which the db resides. IF your os account is a member of the os group 'dba', AND you request a local (not TNS) connection, then you are OS authenticated and the passwordfile does not even come into the picture. In this case, any of the following will work:

sqlplus / as sysdba
sqlplus sys/syspswd as sysdba
sqlplus thisisallfubar/doesnot exist as sysdba

On all of the above, neither the provided username/password nor the password file is ever looked at. The "as sysdba" says 'if the os user is a member of the dba group, connect to the local database that is specified by the enviornment variable ORACLE_SID'.

On the other hand, if you request a TNS connection:

sqlplus myuser/mypassword@mydatabase

THEN authentication will be to consult the password file for 'mydatabase', to see if there is an entry for 'myuser' and that his password is 'mypassword'.

And with TNS connections, it doesn't matter if the client is on the same server as the database, or not. All communications still go through the full network stack.

Previous Topic: Securing Marketing Automation
Next Topic: JDBC connections on TLS/2484 and TCP/1521
Goto Forum:
  


Current Time: Fri Dec 06 14:52:04 CST 2019