OS Authentication in Windows
As a best practice, I don’t like logging in as SYS or SYSTEM unless I have to. I also don’t like remote OS authentication. If I connect remote, I want to use a password. If I am on the box however, I like to just connect using OS authentication (not providing a username and / or password). Now I could also give this internal user access to connect over the network, but well, I am not going to go there. The purpose of this post is to show you what I did to set this up on Windows. On Linux or Unix, it is quite easy, on Windows there is a gotcha.
First, to use OS Authentication for Oracle logins, you need to understand a parameter.
SQL> show parameter authen
NAME TYPE VALUE
----------------------- ----------- -------------
os_authent_prefix string OPS$
remote_os_authent boolean FALSE
The os_authent_prefix could really be set to anything you want, but I like the default. The other parameter is set to FALSE because I don’t want to trust other operating systems authentication.
Next, you need to create the user. How do you find out who that user is? Simple, run this query after you login locally.
SQL> select UPPER(sys_context('userenv','os_user')) from dual;
UPPER(SYS_CONTEXT('USERENV','OS_USER'))
-------------------------------------------------------------------
MACHINENAME\TOM
Now that I have this information, I can easily create the user and grant him the privilege he needs.
SQL> create user "OPS$MACHINENAME\TOM" identified externally;
User created.
SQL> grant dba to "OPS$MACHINENAME\TOM";
Grant succeeded.
SQL> connect /
Connected.
SQL> show user;
USER is "OPS$MACHINENAME\TOM"
SQL>
As you can see, Oracle prefixed my login with OPS$ that I defined in os_authent_prefix. I also had to use the “\” because Oracle sees my login as “MACHINENAME\TOM” and not just Tom. The gotcha is that you cannot create a user with the “\” without using double quotes. If you were a Windows Domain user account then you would use “DOMAINNAME\USERNAME”.
I hope that someone finds this little tip useful in case they want to setup OS Authentication on Windows : ).