How To Create External User Account In Oracle Database by Manish Sharma

Create an External user using the Create User statement.

External users and their authentication are managed by external services such as operating system or a network service. The user who is authenticated by the operating system can easily access the database without being requested for a password. This means that whenever an external authentication option is chosen for the user – an external service performs the password administration and user authentication whereas the Oracle Database maintains the User account.

External users are classic regular database users (non-database administrators) who are assigned standard database roles (such as CONNECT and RESOURCE), but no SYSDBA (database administrator) or SYSOPER (database operator) privilege.

Let’s see what the steps are for creating an External user.
The name of an external user is a string which is a combination of three factors.
These three factors are:

Hostname

This is your computer’s name. You can very easily find the hostname of your machine, by simply writing hostname in your command prompt and hitting ‘enter’. This will show you the hostname of your machine. Note here – A Hostname is only required when you are creating an external user on windows machine but if you are using a UNIX or Linux machine then there is no need of a hostname. So you can Skip this step if you are on a Linux Machine.

User account name of your operating system

Second factor is the User account name of your operating system. This is the name of user account for which you want to create the external user. Mind here that the authentication information of this account such as password will serve as the authentication information of your external user.
To find the user name of your OS simply write 

echo %username%

In case you are using Linux then write 

echo “$USER”

OS_AUTHENT_PREFIX, a special Oracle parameter.

The third factor here is OS_AUTHENT_PREFIX which is a special oracle parameter. This parameter is responsible for telling the oracle that the user which we are going to create is an external user and not a local one or for that matter any other type of user. The name of all the external users must start with the value of this parameter.
To get the value of this parameter first you need to log on to your database using sys user with sysdba privileges. And then write 

SQL>Show Parameter os;

In the fourth line of the output returned by above command, you can see the value of OS_AUTHENT_PREFIX parameter which is by default set as OPS$

Note:
The text of the OS_AUTHENT_PREFIX initialization parameter is case-sensitive on some operating systems. Refer to your operating system specific Oracle documentation for more information about this initialization parameter.

Now let’s create the name of our external user. For that let’s suppose the value of our hostname is “loclalhost” and the name of user account for which we want to create the external user is “Manish” and the value of OS_AUTHENT_PREFIX parameter is set on default “OPS$”

While creating the name of the external user you have to keep certain things in mind such as:

  1. Name of the external user must always start with the value of OS_AUTHENT_PREFIX parameter followed by the hostname and then a backward slash (\) after which comes your user account name.

In case you are using UNIX or Linux Machine then we will skip the host name from the name of our external user. 
So the name of external user in Windows environment will be 

OPS$loclalhost\Manish

And the name of external user in UNIX or Linux environment will be

OPS$Manish

Since now we know the name of our external user hence creating it is a piece of cake. 
To create an external user we will again use the Create User statement. Let’s see how:

SQL>CERATE USER “OPS$LOCALHOST\MANISH” IDENTIFIED externally;

Must Read: How To create a new user

Above query is fairly simple CREATE and USER both are the keywords followed by the name of our user which we just determined and then IDENTIFIED clause which is followed by externally keyword.

Always remember to enclose the name string in double quotes as we have $ sign in the string and this name string should be in all caps.

Our external user has been created! Now we have to grant this user a special as well as mandatory system privilege which is “Create Session” so that we can log on to database using this user. 

SQL> GRANT create session TO “OPS$LOCALHOST\MANISH”;

Now as it’s an external user this means that all its authentications are handled by operating system. Thus to log on to our database using this user we have to simply write

C:\> sqlplus /

As our user is an external user thus there is no need of writing username or password, just write forward slash (/) and hit enter.

This is the process of how to create an external user. You can watch my YouTube tutorial to see the execution of each step. Also please do not forget to share this with your friends and spread the word. Thanks & Have a great day!

Check out the quick video on how to create an external user.