Friday, September 2, 2016

How to Create an External user using Create user statement
The external user and his authentication are managed by external services like as network service or operating system services. The user who is authenticate by the OS (operating system) can access the Database without any password.


The external users are classic default and regular database users (non-dba) who are assigned standard database roles (such as CONNECT and RESOURCE), but no SYSOPER (database operator) or SYSDBA (database administrator) privilege.
Before creating an external user you must check the Oracle OS_AUTHENT_PREFIX initialization parameter, using following command.
SQL> SHOW PARAMETER os_authent_prefix

NAME                         TYPE        VALUE
-----------------------    ----------- ------------------------------
os_authent_prefix           string      ops$
You can see the value of os_authent_prefix parameter default value is “ops$”, if it’s not same like that you can change it using ALTER COMMAND.

Now create an external user using CREATE command, using following.
SQL>Create user “ops$oracle” Identified by externally;
Your external user has been created successfully, Now we have need to grant mandatory system privilege which is “Create Session” so that it can be log on to database.
SQL> GRANT create session TO “ops$oracle”;
Now external user can be login in database without using username or password, it’s just use the forward slash (/).
$sqlplus /
SQL>show user

You can watch my YouTube video to see the execution of each step.

No comments:

Post a Comment