6.7 Connecting to the Database

There are several methods for connecting to Oracle Database.

Table 6-1 Database Connection Methods and Syntax

Type of Connection Connection Syntax Description

From the command line

The general form of connecting an application to a database server from the command line is: 

tool username@connect_identifier

You are prompted to enter your password which is encrypted.

For example:

SQLPLUS system@sales
Enter password: password

Most Oracle tools can use the operating system command line to connect, and some provide alternatives.

From a login screen

 
username@connect_identifier

Some tools provide a login screen as an alternative form to log in. A user can log in to a database server by identifying both the user name and connect identifier in the user name field of the tool login screen, and entering the password in the password field.

From a 3GL application

 
exec sql connect :username identified by :password

In the preceding connection request, :username and :password are 3GL variables that can be set within the program either statically or by prompting the user. When connecting to a database server, the value of the :username variable is in the form: 

username@net_service_name

The :password variable contains the password for the database account to which you are connecting.

Applications written in 3GL, such as OCI and pre-compilers, are used by middle-tier and database application developers for direct database access from a client program.

From within SQL*Plus

 
SQLPLUS /nolog
SQL> CONNECT username@net_service_name

For example: 

SQLPLUS /nolog
SQL> CONNECT scott@serverx
Enter password: password

In the preceding commands, username and password are the database user and password, and net_service_name is the network service name.

Some Oracle tools have commands for database connections to allow an alternative user name to be specified without leaving the tool.

Other Oracle tools use slightly different methods specific to their function or interface. For example, Oracle CDE tools use login buttons with fields for the user name, password, and remote database ID.

Using KERBEROS5_CC_NAME parameter 

(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=(SQLNET.KERBEROS5_CC_NAME=/usr/tmp/krbcache))
)

Use this parameter to specify the complete path to the Kerberos credentials cache file for the Kerberos principal (user), when multiple Kerberos principals need to log in through the Database client.

If you are using Kerberos authentication to connect to a database, then specifying the complete path to the credential cache using KERBEROS5_CC_NAME is mandatory. For a single Kerberos principal, you can specify the credential cache path in the sqlnet.ora file. When a client needs to use more than one Kerberos Principal for making multiple database connections, specify KERBEROS5_CC_NAME parameter in either the CONNECT string for individual connections or in the tnsnames.ora file.

KERBEROS5_CC_NAME supports multiple principals and the storage of credentials that are returned by the Key Distribution Center (KDC) in an encrypted form.

See Oracle Database Security Guide.

Using KERBEROS5_PRINCIPAL parameter 

(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=
          (KERBEROS5_CC_NAME=/tmp/krbuser1/krb.cc)
          (KERBEROS5_PRINCIPAL=krbprinc1@example.com))
)

This parameter is used to specify Kerberos principals for a database client.

This is an optional parameter. However, if you use this parameter, then ensure that the KERBEROS5_PRINCIPAL matches the principal retrieved from the Kerberos credential cache (specified using KERBEROS5_CC_NAME). The authentication fails if the principal name does not match.

Using IAM database password verifier authentication

 
SQLPLUS /nolog
SQL> CONNECT username@net_service_name
Enter password: password

In this connection request, username is the IAM user name, net_service_name is the network service name, and password is the IAM database password.

An IAM user can connect through a database proxy user account using password-based proxy authentication:

SQLPLUS [user1]/password123\!@cdb1_pdb2
SQL> SHOW USER;
select sys_context('USERENV','AUTHENTICATION_METHOD') from dual;
select sys_context('USERENV','PROXY_USER') from dual;
select sys_context('USERENV','CURRENT_USER') from dual;

If you have configured Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) for user authentication and authorization, then IAM users can connect to OCI Database as a Service (DBaaS) using the IAM user name and IAM database password. With this connection method, an IAM database password verifier (an encrypted hash of password) is retrieved from IAM to authenticate users.

This IAM database password is different from the OCI console password. An IAM user can set this password from the OCI console (watch the Create an OCI IAM Password video).

After you are granted the required authorization, you can log in from any supported database client using on-premise client applications, such as SQL*Plus.

Use of IAM user name and IAM database password with the IAM database verifier is the default configuration, and you do not need to set any additional parameters for the client. However, if PASSWORD_AUTH is set to OCI_TOKEN in the client-side sqlnet.ora file, then the database client tries to connect with OCI IAM to retrieve a database token using the IAM user name and IAM database password. In this case, you can override this setting for a particular connection by setting PASSWORD_AUTH=PASSWORD_VERIFIER.

You can configure client connections (typically middle-tier environments) to use proxy authentication. In this case, you can alter an existing IAM user with necessary permissions to connect through a proxy database user account by using password-based proxy authentication. The proxy user session has all the privileges granted to the IAM user.

See Oracle Database Security Guide.

Using IAM token-based authentication (bearer token)

 
SQLPLUS /nolog
SQL> CONNECT /@connect_identifier

The PASSWORD_AUTH setting enforces IAM token-based authentication using a bearer token: 

(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
    (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (PASSWORD_AUTH=OCI_TOKEN)
        (OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
        (OCI_TENANCY=ocid1.tenancy..12345))
    (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)

You can use the optional OCI_COMPARTMENT and OCI_DATABASE parameters to limit the scope of your token request. 

(DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
     (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (PASSWORD_AUTH=OCI_TOKEN)
        (OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
        (OCI_TENANCY=ocid1.tenancy..12345)
        (OCI_COMPARTMENT=ocid1.compartment..12345)
        (OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345))
     (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))  
  )

If you have configured IAM for user authentication and authorization, then IAM users can use an IAM database token (db-token) to connect to an Oracle DBaaS instance. This type of token is a bearer token and does not come with a private key.

You can configure the database client to request the db-token using your IAM user name and IAM database password. An application cannot pass this type of token to the client. In this case, set the PASSWORD_AUTH parameter to OCI_TOKEN.

An IAM user can set this IAM database password from the OCI console (watch the Create an OCI IAM Password video).

The database client retrieves the token directly from the OCI IAM endpoint. You must set additional parameters so that the database client can find the IAM endpoint along with additional metadata. The additional parameters are OCI_IAM_URL and OCI_TENANCY with the optional OCI_COMPARTMENT and OCI_DATABASE to limit the scope.

When an IAM user logs in with the IAM user name and IAM database password using /@connect_identifier, the PASSWORD_AUTH=OCI_TOKEN setting along with /@connect_identifier instructs the database client to get the token directly from an OCI IAM endpoint using a REST API request.

You can specify these parameters in the tnsnames.ora file, sqlnet.ora file, or directly as part of the connect string.

See Oracle Database Security Guide.

Using IAM token-based authentication (PoP token)

 
SQLPLUS /nolog
SQL> CONNECT /@connect_identifier

The TOKEN_AUTH setting enforces IAM token-based authentication using a PoP token: 

(DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcps)(HOST=sales_db)(PORT=1522))
   (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (TOKEN_AUTH=OCI_TOKEN))
   (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)

You can use the optional TOKEN_LOCATION parameter to override the default directory where the database token and private key are stored: 

(DESCRIPTION= 
    (ADDRESS=(PROTOCOL=tcps)(HOST=sales_db)(PORT=1522)) 
    (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (TOKEN_AUTH=OCI_TOKEN)
        (TOKEN_LOCATION="/home/oracle/.oci/db-token")) 
    (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)

An IAM user can connect through a database proxy user account using token-based proxy authentication:

SQLPLUS [user1]/@pop_token_connstr
SQL> SHOW USER;
select sys_context('USERENV','AUTHENTICATION_METHOD') from dual;
select sys_context('USERENV','PROXY_USER') from dual;
select sys_context('USERENV','CURRENT_USER') from dual;

If you have configured IAM for user authentication and authorization, then IAM users can use an IAM database token (db-token) to connect to an Oracle DBaaS instance. This type of token is a proof-of-possession (PoP) token with an expiration time and scope. You can request the token from IAM using Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI) or programmatically from the OCI Software Development Kit (SDK).

You can use one of the IAM user credentials, such as API-key, security token, resource principal, service principal, instance principal, or delegation token (delegation token is available only in the Cloud Shell) to retrieve the db-token and private key from IAM.

A client application can send a connection request in one of the following ways:

  • When an IAM user logs in using /@connect_identifier (and TOKEN_AUTH is set to OCI_TOKEN), the database client gets the db-token and private key from either the default directory or the location specified by TOKEN_LOCATION (using IAM token-based authentication).

  • If your client application is updated to retrieve tokens from IAM, then IAM directly passes the db-token and private key as attributes to the database client using the database client API. In this case, you do not need to specify the TOKEN_AUTH and TOKEN_LOCATION parameters.

You can specify these parameters in the tnsnames.ora file, sqlnet.ora file, or directly as part of the connect string.

You can configure client connections to use proxy authentication. In this case, you can alter an IAM user with necessary permissions to connect through a proxy database user account by using token-based proxy authentication. The proxy user session has all the privileges granted to the IAM user.

See Oracle Database Security Guide.

Using Azure token-based authentication

 
SQLPLUS /nolog
SQL> CONNECT /@connect_identifier

The TOKEN_AUTH setting enforces Azure token-based authentication. You must also use the TOKEN_LOCATION parameter to specify the directory path where the access token is stored.

If the token file is named token, then the client automatically looks for the file in the specified directory path (for example, /home/dbuser1/access-token): 

(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
    (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (TOKEN_AUTH=OAUTH)
        (TOKEN_LOCATION="/home/dbuser1/access-token"))
    (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)

If the token file name is different from token, then you must specify the file name (for example, mytoken) along with the directory path: 

(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
    (SECURITY=
        (SSL_SERVER_DN_MATCH=TRUE)
        (SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
        (TOKEN_AUTH=OAUTH)
        (TOKEN_LOCATION="/home/dbuser1/access-token/mytoken"))
    (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)

If you have configured Microsoft Entra ID (previously called Microsoft Azure Active Directory) for user authentication and authorization, then Azure users can use the Entra ID OAuth2 access token to connect to an Oracle Database. This access token is a bearer token with an expiration time and scope, and follows the OAuth2.0 standard with Entra ID extensions.

The TOKEN_AUTH setting (TOKEN_AUTH=OAUTH) instructs the database client to get the access token from the directory specified by TOKEN_LOCATION if the token file is named token. Otherwise, you must use your token file name along with the directory location while specifying the TOKEN_LOCATION parameter.

The TOKEN_LOCATION parameter is mandatory for Azure token-based authentication. The database client gets the access token from this location and sends it to the database server.

You specify the TOKEN_AUTH and TOKEN_LOCATION parameters in the tnsnames.ora, sqlnet.ora file or directly as part of the connect string.

You can request the tokens from tools and scripts run on Linux, Microsoft PowerShell, or other environments. You can also request these tokens programmatically using the Microsoft SDKs.

A client application can send a connection request in one of the following ways:

  • An Azure user can request the access token from Entra ID using one of the supported authentication flows (resource owner password credentials, authorization code, on-behalf-of (OBO) flow, or client credentials) and store it in a local file directory.

  • If your client application is updated to retrieve tokens from Entra ID, then the application can also request the access token directly from Entra ID and pass it as an attribute to the database instance using a database client API. In this case, you do not need to specify the TOKEN_AUTH and TOKEN_LOCATION parameters.

See Oracle Database Security Guide.

Note:

If you have configured IAM token-based authentication, then you can enable the database client to directly retrieve the db-token with IAM Single-Sign On (SSO) credentials using OCI authentication flows, such as OCI_INTERACTIVE, OCI_API_KEY, OCI_INSTANCE_PRINCIPAL, OCI_DELEGATION_TOKEN, and OCI_RESOURCE_PRINCIPAL.

Similarly, if you have configured Azure token-based authentication, then you can enable the database client to directly retrieve the access token with Azure SSO credentials using authentication flows, such as AZURE_INTERACTIVE, AZURE_SERVICE_PRINCIPAL, AZURE_MANAGED_IDENTITY, and AZURE_DEVICE_CODE.

This feature is available in environments that use JDBC-thin clients, ODP.NET Core classes, or ODP.NET Managed Driver classes. To configure this feature for JDBC-thin clients, see Oracle Database JDBC Developer's Guide and for ODP.NET, see Oracle Data Provider for .NET Developer's Guide.

Related Topics

Parent topic: Quick Start to Oracle Net Services