“SQLPLUS /” – ORA-01017: invalid username/password; logon denied

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.