0

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?

Improve this question
10
  • 1
    How was the database account created? Commented Jun 12, 2018 at 14:05
  • 1
    It's fine for the prefix to be null. You probably want either CREATE or ALTER USER projectA IDENTIFIED EXTERNALLY. docs.oracle.com/cd/B19306_01/network.102/b14266/… Commented Jun 12, 2018 at 14:12
  • 1
    If you really want to set the prefix though, I'd do alter system set OS_AUTHENT_PREFIX='your_prefix_here' scope=spfile; and then bounce the database. Commented Jun 12, 2018 at 14:15
  • 1
    @JustinC - I was referring to the user's account in the database, not the database itself. Commented Jun 12, 2018 at 14:19
  • 1
    If the DB account was created as ops$projectA then the OS_AUTHENT_PREFIX needs to be OP$S. With no prefix set the account name should not have that prefix either. Either is valid but you need to decide which way to go; which might be based on the old DB or other accounts that do currently work. Read more in the docs. Commented Jun 12, 2018 at 17:33

1 Answer 1

Reset to default
1

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for all our help. The senior DB confirmed that it is the correct statement to run on sqlplus like you all commented here. Actually I wanted to run that statement too but the Java developer worried that it might messed up the links to the external tables (text files). To tell the truth I am coming from a Java/Python background but taken up this job as junior DB. So there is a lot to learn here. Thank you again.
I'm not sure how changing that could affect external tables, which use DB directory objects and rely on OS permissions - authentication of DB users doesn't seem relevant.
I agree with you but the Java Developers who was working on that box that wasn't sure at the time. It was just the OS authentication with prefix on or no prefix string. At least we learned something new especially me which is a good thing for everybody. Again, thank you Alex for your time. I really do appreciate for your help as well as kfinity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.