SQL tutorial 43: How to create an EXTERNAL USER in oracle database By Manish Sharma RebellionRider

Summary

In this tutorial, Manish from RebellionRider.com demonstrates how to create an external user in an Oracle database. Unlike local users, external users are authenticated by the operating system instead of Oracle’s data dictionaries. The tutorial covers the essential steps, including finding the hostname, user account name, and the OS_AUTHENT_PREFIX parameter to properly name the external user. Manish also shows how to create the user with a simple SQL command and log in without needing a password. This step-by-step guide is applicable to Windows, Linux, and Mac environments, making it a comprehensive resource for Oracle users.

The video is abnormal, and we are working hard to fix it.
Please replace the link and try again.

Q & A

What is the main topic of the tutorial?

The main topic of the tutorial is how to create an external user in an Oracle database.

How does authentication differ between local and external users in Oracle?

For local users, authentication is handled by Oracle's data dictionaries using a password, whereas for external users, authentication is managed by external services like the operating system.

Can external users access the database without a password?

Yes, external users can access the database without a password since their authentication is managed by the operating system.

What systems can you use to create an external user?

The steps to create an external user can be used on Windows, Linux, and Mac environments, as the procedure is the same across these platforms.

What are the three factors that make up the external user's name?

The three factors are: 1) Hostname (your computer's name), 2) User account name of your operating system, and 3) OS_AUTHENT_PREFIX, a special Oracle parameter.

Why is the hostname required only on Windows machines?

The hostname is only required when creating an external user on a Windows machine because it is part of the external user’s name. On UNIX or Linux machines, the hostname is not needed.

How do you find the hostname of your machine?

You can find the hostname of your machine by typing 'hostname' in the command prompt and hitting enter.

What is the purpose of the OS_AUTHENT_PREFIX parameter in Oracle?

The OS_AUTHENT_PREFIX parameter tells Oracle that the user being created is an external user, and the name of all external users must start with the value of this parameter.

How do you find the value of the OS_AUTHENT_PREFIX parameter?

To find the value of the OS_AUTHENT_PREFIX parameter, log on to the database as a sys user with sysdba privileges and run the query 'Show Parameter os'.

How is the external user's name formatted in Oracle?

The external user's name is formatted as 'OS_AUTHENT_PREFIX' followed by the hostname (for Windows machines) and then the user account name, with a backward slash between the hostname and user account name. On UNIX/Linux systems, the hostname is omitted.

What is the syntax for creating an external user in Oracle?

The syntax is: 'CREATE USER "OPS$USER-HOSTNAME\USERNAME" IDENTIFIED EXTERNALLY;' where 'OPS$USER-HOSTNAME\USERNAME' is the external user's name.

What system privilege is mandatory for an external user to log in to Oracle?

The 'CREATE SESSION' system privilege is mandatory for an external user to log in to the Oracle database.

How can you log into the database as an external user?

You can log into the database as an external user by typing 'SQLPLUS /' in the command prompt, without the need for a username or password.