{"id":798,"date":"2014-10-18T16:25:35","date_gmt":"2014-10-18T16:25:35","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=798"},"modified":"2014-11-19T11:57:46","modified_gmt":"2014-11-19T11:57:46","slug":"sqlplus-ora-01017-invalid-usernamepassword-logon-denied","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=798","title":{"rendered":"“SQLPLUS \/” – ORA-01017: invalid username\/password; logon denied"},"content":{"rendered":"

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.\u00a0 When I tested “sqlplus \/” with my user account “mike”, I received the error below:<\/p>\n

ERROR: ORA-01017: invalid username\/password; logon denied\r\n<\/pre>\n
\"ORA-01017_01\"<\/a><\/p>\n
First I checked to see if my operating system account has permission in the database.\u00a0 I was able to use the query below:<\/p>\n
SELECT username FROM dba_users WHERE password = 'EXTERNAL' ORDER BY username;\r\n<\/pre>\n
\"ORA-01017_02\"<\/a><\/p>\n
When I saw that my OS account did not have permissions in the database, I added the account.\u00a0 Please note that I put the prefix “ops$” in front of my OS account name.\u00a0 A more common OS account used is ops$oracle.\u00a0 Then I had to grant connect & resource permissions.\u00a0 Some more advanced users might need the dba granted to accounts like ops$oracle.\u00a0 Below is the SQL that I used to add my OS account:<\/p>\n
create user ops$mike\r\nidentified externally\r\ndefault tablespace USERS \r\ntemporary tablespace TEMP\r\nprofile DEFAULT \r\naccount unlock;\r\n\r\ngrant connect,resource to ops$mike;<\/pre>\n
\"ORA-01017_03\"<\/a><\/p>\n
Then I was able to test again, without having to logout and log back in.\u00a0 I was now ready to run sqlplus in my shell scripts without being prompted for a password.<\/p>\n
\"ORA-01017_04\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 When I tested “sqlplus … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":801,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[36,30],"class_list":["post-798","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-logon-denied","tag-ora-01017"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/798"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=798"}],"version-history":[{"count":6,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/798\/revisions"}],"predecessor-version":[{"id":809,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/798\/revisions\/809"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/801"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=798"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=798"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=798"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}