While working on my shell scripts, I hit a snag when I want sqlplus to authenticate using my operating system account without entering the login credentials. When I tested “sqlplus /” with my user account “mike”, I received the error below:
ERROR: ORA-01017: invalid username/password; logon denied
First I checked to see if my operating system account has permission in the database. I was able to use the query below:
SELECT username FROM dba_users WHERE password = 'EXTERNAL' ORDER BY username;
When I saw that my OS account did not have permissions in the database, I added the account. Please note that I put the prefix “ops$” in front of my OS account name. A more common OS account used is ops$oracle. Then I had to grant connect & resource permissions. Some more advanced users might need the dba granted to accounts like ops$oracle. Below is the SQL that I used to add my OS account:
create user ops$mike identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT account unlock; grant connect,resource to ops$mike;
Then I was able to test again, without having to logout and log back in. I was now ready to run sqlplus in my shell scripts without being prompted for a password.