CODEWITHSUNDEEP
oracle-databaseauthentication
JustinC
JustinC

Reputation: 21

Oracle OS authentication authentication

I have problem logging in to one of the accounts in our database. The situation is like this.

The user has already logged in to his account environment on Linux, for example from his personal account

su - projectA

The user tries to run SQL*Plus using

[projectA@myDB2]$ sqlplus /

We are getting the message:

ORA-01017: invalid username/password: logon denied

I ran this command below and it has a missing prefix.

SQL> SHOW PARAMETER os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      
SQL>

How do I set a prefix on os_authent_prefix?

Upvotes: 0

Views: 1169

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

You're allowed to have no value set for OS_AUTHENT_PREFIX, but if you do then the database account needs to be the same as the operating system account - i.e. projectA instead of OPS$projectA.

If you do want the account to have the OPS$ prefix then the initialisation parameter has to match. You can set it as @kfinity said in a comment:

alter system set OS_AUTHENT_PREFIX='OPS$' scope=spfile;

followed by a DB bounce. But as that is the default value (still, I think!) it's likely someone has intentionally cleared it, so proceed with caution.

The main thing is to be consistent. If you have other user accounts with OPS$ then you probably should have it set (and you can check the old DB you mentioned to see if the users and settings are the same). If you don't have any others then you need to verify whether any of the un-prefixed account names are identified externally via the dba_users.authentication_type column. If there are any then changing the initialisation parameter would break those.

Read more in the documentation:

Upvotes: 1

Related Questions