1. Security Guide
  2. Managing User Authentication and Authorization
  3. Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7 Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

Identity and Access Management (IAM) users can be configured to connect to an Oracle Autonomous Database.

Parent topic: Managing User Authentication and Authorization

7.1 Introduction to Authenticating and Authorizing IAM Users for an Oracle Autonomous Database

Before you begin authenticating and authorizing IAM users for an Oracle Autonomous Database, you should understand the overall process.

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.1.1 About Authenticating and Authorizing IAM Users for an Oracle Autonomous Database

Users for the Oracle Autonomous Database can be centrally managed in Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM).

Note:

Oracle Database supports the Oracle Autonomous Database integration for Oracle Cloud Infrastructure (OCI) IAM with identity domains as well as the legacy IAM, which does not include identity domains. IAM with identity domains was introduced with new OCI tenancies that were created after November 8, 2021. Only default domain IAM users are supported with the new identity domains.

An Oracle Autonomous Database ADMIN user manages authentication and authorization of OCI IAM users who need to connect to the Oracle Autonomous Database instance. The type of Oracle Autonomous Database instance that IAM users can connect to is Autonomous Database on Shared Exadata Infrastructure, as well as Autonomous Database on Dedicated Exadata Infrastructure.

This type of connection enables the IAM user to access the Oracle Autonomous Database. These users typically log in with a user name and password (for example, using SQL*Plus). Alternatively, a user can log in with IAM Single-Sign On (SSO) credentials with a token when accessing the Autonomous Database. The choice to use IAM password authentication or the IAM SSO token authentication depends on the use case and user preference.

Legacy applications using existing supported database clients can migrate seamlessly to using an IAM user name and password. They can also use the gradual password rollover feature to set a second database password in IAM and update the application passwords without downtime.

Tools and applications that are updated to support IAM tokens can authenticate users directly with IAM and pass the database access token to the Autonomous Database instance. Existing database tools such as SQL*Plus can use the IAM password for authentication, but cannot directly request a database token for the user. Instead, users can use the Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI) to request the token, which enables SQL*Plus and other applications and tools to use the IAM database token to access the database. A database access token (db-token) is a scoped proof-of-possession (POP) token and comes with a public key. Before the db-token is sent to the database, the database client signs the db-token with the private key that is associated with token's public key. It provides "proof" that the sender of the token is the rightful holder of the token. The scope can optionally be included as part of the request for the db-token to reduce the scope of what the db-token can be used for. The default scope for the db-token is the entire tenancy but compartment and individual databases can also be defined as the scope. See the get description in OCI CLI Command Reference for more information.

IAM users and OCI applications can request a database token from IAM by using one of the following methods:

  • Using an existing, valid security (session) token
  • Using an IAM recognized API-key
  • Using a delegation token within an OCI cloud shell
  • Using an OCI instance principal for an application on OCI compute instance
  • Using an OCI resource principal for an application with a resource principal

The general process of enabling an IAM user to connect to an Oracle Autonomous Database instance is as follows:

  1. The IAM administrator creates and manages the IAM user accounts and groups, adding IAM users to appropriate IAM groups based on their tasks.
  2. On the Oracle Autonomous Database instance, the ADMIN user enables the connection between the Oracle Autonomous Database and the IAM endpoint if the database is Autonomous Database on Shared Exadata Infrastructure.

    If the database is Autonomous Database on Dedicated Exadata Infrastructure, then the connection for new PDBs is automatically enabled. Check the Oracle Autonomous Database documentation for details.

  3. On the Oracle Autonomous Database server, the ADMIN user enables the authorization of the IAM users by performing the following types of mappings:
    • Mapping an IAM group to a shared Oracle Database global user account
    • Mapping an IAM group to an Oracle Database global role
    • Exclusively mapping the IAM user to an Oracle Database global user

    The IAM user must be mapped to one schema, either exclusively or to a shared schema. They can optionally be members in an IAM group that is mapped to a global role.

    The highest Oracle Database privilege that an IAM user can have are the privileges from the ADMIN role.

  4. The following use cases are some common scenarios to connect to the Autonomous Database with centralized IAM authentication and authorization:
    • Connecting using SQL*Plus to the Autonomous Database using an IAM user name and IAM database password.
    • Using SQL*Plus to connect using an IAM SSO token.
    • Using SQLcl to connect to the Autonomous Database using the IAM password or IAM token.
    • Using SQL*Plus within the Oracle Cloud Infrastructure (OCI) Cloud Shell to connect to the Autonomous Database using the IAM password or IAM SSO token. Authenticating and authorization with IAM will take additional time as opposed to authenticating to a local database user account (non-global).

Related Topics

Parent topic: Introduction to Authenticating and Authorizing IAM Users for an Oracle Autonomous Database

7.1.2 Architecture of the IAM Integration with an Oracle Autonomous Database

The architecture for the IAM integration with an Oracle Autonomous Database depends on whether the IAM user is using an Oracle Cloud Infrastructure (OCI) IAM database password or an OCI IAM token to authenticate or connect to the Autonomous Database instance.

The following diagram illustrates how using an Oracle Cloud Infrastructure (OCI) IAM database password verifier to authenticate with the Oracle Autonomous Database works:

Figure 7-1 IAM User Authenticating to an Oracle Autonomous Database with an OCI IAM Database Password Verifier

Description of Figure 7-1 follows
Description of "Figure 7-1 IAM User Authenticating to an Oracle Autonomous Database with an OCI IAM Database Password Verifier"
  1. The IAM user logs in to a tool or application client that is associated with the Oracle Autonomous Database client. This user logs in with their IAM user name and IAM database password, which begins the authentication process. The user can log in with the existing supported database clients as long as the client is at least Oracle Database release 12c or patched to use the Oracle Database 12C password version.
  2. The IAM user connection request is sent through the database client.
  3. After the IAM user name is sent to the Oracle Autonomous Database instance, the database requests the user’s Oracle Cloud Infrastructure (OCI) IAM database password verifier from IAM. (The IAM user profile stores the IAM database password verifier.) This verifier is a hashed version of the password, not clear text. If the password verifier from IAM matches the password verifier generated by the database client, then the user is authenticated. The Oracle Autonomous Database instance uses a resource principal to communicate with IAM. The resource principal is the Autonomous Database identity that is recognized by IAM and used by the database to securely communicate with IAM. (See Using Oracle Autonomous Database on Shared Exadata Infrastructure for more information about resource principals.)
  4. When the authentication succeeds, the Oracle Autonomous Database instance retrieves the IAM user groups. If the IAM user is mapped to an Oracle Database schema and the user has not been locked out of their OCI account, then the IAM user successfully accesses the database.
  5. The Oracle Cloud Infrastructure (OCI) login counter tracks logins for both the OCI console and OCI database passwords. A successful database login using the IAM database password will reset this counter.
  6. Based on the outcome of the preceding steps, the IAM user database access attempt either succeeds or fails.

The following diagram illustrates the start of actions that take place when an IAM user or an Oracle Cloud Infrastructure (OCI) application accesses the Oracle Autonomous Database database using an OCI IAM token:

Figure 7-2 IAM User or OCI Application Authenticating to an Oracle Autonomous Database with an OCI IAM Token, Part 1

Description of Figure 7-2 follows
Description of "Figure 7-2 IAM User or OCI Application Authenticating to an Oracle Autonomous Database with an OCI IAM Token, Part 1"
  1. Access to the database requires one of the following:
    • 1a: From an IAM user, the user must have an API-key stored in their local system or have a security token from signing into OCI recently. An API-key, security token, delegation token, instance principal, can be used with the OCI CLI. If a current and valid security token is not available, then the user can be prompted to authenticate with OCI IAM. (See User Credentials for information about the available user credentials.) In an OCI cloud shell environment, a delegation token will be available.
    • 1b: For an OCI application, the application must have be configured to have an instance principal or a resource principal. All key types (API-key, security token, delegation token, instance principal, and resource principal) can be used with the OCI SDK.
  2. The application or OCI CLI makes a call to IAM requesting the db-token using one of the principal credentials. Only the db-token can be used to access the Autonomous Database. Requesting a db-token can be done by an application written with the Oracle Cloud Infrastructure (OCI) public SDK to connect with OCI IAM. (See Software Development Kits and Command Line Interface.) If an application cannot be changed to connect directly with OCI IAM using the OCI public SDK, then a helper tool such as the OCI command line interface (OCI CLI) can be used to retrieve the db-token for the user.
  3. An application or tool that has been updated to work with IAM can then pass the db-token directly to the database client through the client API as an attribute. If an application cannot be updated to get the db-token directly, then a helper tool such as OCI CLI can put the db-token into the default or specified location in the local directory. The TOKEN_AUTH=OCI_TOKEN setting in the connect string or the sqlnet.ora file enables the database client to retrieve the db-token from the default or specified file location. A user can request a token at the OCI CLI by running the oci iam db-token get command and specifying their profile, which stores their user account credentials. For example:
    oci iam db-token get --profile PeterFitch

    The directory location for the db-token and the corresponding private key should only have enough permission for the OCI CLI to write the files to the location and the database client to retrieve these files (for example, just read and write by the process user). Because the token and key allow access to the database, they should be protected within the file system.

The following diagram illustrates the continuation of the OCI IAM token authentication process:

Figure 7-3 IAM User or OCI Application Authenticating to an Oracle Autonomous Database with an OCI IAM Token, Part 2

Description of Figure 7-3 follows
Description of "Figure 7-3 IAM User or OCI Application Authenticating to an Oracle Autonomous Database with an OCI IAM Token, Part 2"
  1. The db-token is signed and sent to the Oracle Autonomous Database. TLS must be enabled on the database client-server link as well as DN matching. (When you use the Autonomous Database wallet files to connect to the Autonomous Database instance, TLS and DNS matching is already set for you.) DN matching is on by default with the JDBC driver, but will need to be configured for the OCI-C database client (and instant client).
  2. The Oracle Autonomous Database instance will request the IAM public key, if a valid copy is not already available locally. This key will be used to validate that the db-token was sent by IAM. The Oracle Autonomous Database instance uses a resource principal to communicate with IAM.
  3. After this authorization step completes successfully, the Oracle Autonomous Database instance will request the IAM user’s groups from IAM. This action will map the user to a global schema and also to map the user to any global roles that the user is a member of. After the IAM user has successfully completed these steps, the user has access to the Oracle Autonomous Database instance.

IAM SSO token-based authentication requires that you download the latest Oracle Database 19c and 21c JDBC and SQL*Plus Instant Client for Linux.

Related Topics

Parent topic: Introduction to Authenticating and Authorizing IAM Users for an Oracle Autonomous Database

7.1.3 Users and Groups to Map Between an Oracle Autonomous Database and IAM

IAM users must be mapped to a schema, either an exclusive mapping of a database schema to an IAM user or to a database shared schema that is mapped to an IAM group the user is a member of.

An IAM user must be mapped to a database schema to successfully complete the login and authorization steps. An IAM user can be directly mapped to a database schema if the IAM user needs to maintain their own schema objects (exclusive mapping). More commonly, an IAM user is a member of an IAM group that is mapped to a database schema (shared schema mapping). Shared schema mapping allows multiple IAM users to share the same schema so a new database schema is not required to be created every time a new user joins the organization. This operational efficiency allows database administrators to focus on database application maintenance, performance, and tuning tasks instead of configuring new users, updating privileges and roles, and removing accounts.

Database administrators for a group of databases can be members of an IAM group (for example, sales application developers for a sales application are in an IAM group called sales_app_dev_group). In this scenario, all the related databases can map the shared schema to the sales_app_dev_group group. Database global roles cannot be granted to a schema; they can only be mapped to an IAM group. Global roles can differentiate IAM user privileges when multiple IAM users are mapped to the same shared schema.

Remember that an IAM user must be mapped exclusively to a database schema or to a shared schema so that the IAM user can access the Autonomous Database instance.

Parent topic: Introduction to Authenticating and Authorizing IAM Users for an Oracle Autonomous Database

7.2 Configuring Oracle Autonomous Database for IAM

To configure Oracle Autonomous Database to work with IAM, an Oracle Autonomous Database ADMIN user must first enable the IAM integration (for Autonomous Database on Shared Exadata Infrastructure only) and then authorize IAM users and roles for Oracle Autonomous Database.

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.2.1 Enabling External Authentication for Oracle Autonomous Database

The method of enabling an IAM connection with Oracle Autonomous Database depends on the platform of Oracle Autonomous Database that you are using.

  • Oracle Autonomous Database on Shared Exadata Infrastructure: The IAM connection must be enabled to work with this platform. See Using Oracle Autonomous Database on Shared Exadata Infrastructure
  • Oracle Autonomous Database on Dedicated Exadata Infrastructure: The IAM connection is automatically configured to work with this platform.

Parent topic: Configuring Oracle Autonomous Database for IAM

7.2.2 Configuring Authorization for IAM Users and Oracle Cloud Infrastructure Applications

An Oracle Autonomous Database ADMIN user can map IAM users and Oracle Cloud Infrastructure (OCI) applications to the Oracle Autonomous Database global schemas and global roles.

Parent topic: Configuring Oracle Autonomous Database for IAM

7.2.2.1 About Configuring Authorization for IAM Users and Oracle Cloud Infrastructure Applications

You create the mappings for IAM users and Oracle Cloud Infrastructure (OCI) applications to Oracle Autonomous Database from the Oracle Autonomous Database instance.

There is a difference with authorization between IAM database password authentication and using IAM token based authentication. IAM database password authorization is only based on mappings of database schemas and global roles to IAM users and group. With IAM token based authentication, IAM policies are an additional authorization for IAM users to access their tenancy databases. An IAM user must be authorized through an IAM policy and be authorized through a mapping to a database global schema (exclusive or shared).

For both token and password verifier database access, you create the mappings for IAM users and OCI applications to the Oracle Autonomous Database instance. The IAM user accounts themselves are managed in IAM.

When the IAM user accesses the Oracle Autonomous Database instance with a token, the database will perform an authorization check against IAM policies to ensure the user is allowed to access the database. If the IAM user is allowed to access the database by IAM policy, then the database will query IAM for the user groups. When using password verifier authentication, the database will query IAM for user groups once the IAM user successfully completes authentication. The database queries the IAM endpoint to find the groups of which the user is a member. If your deployment is using shared schemas, then one of the IAM groups will map to a shared database schema and the IAM user will be assigned to that database schema. The IAM user will have the roles and privileges that are granted to the database schema. Because multiple IAM users can be assigned to the same shared database schema, only the minimal set of roles and privileges should be granted to the shared schema. In some cases, no privileges and roles should be granted to the shared schema. Users will be assigned the appropriate set of roles and schemas through database global roles. Global roles are mapped to IAM groups. This way, different users can have different roles and privileges even if they are mapped to the same database shared schema. A newly hired user will be assigned to an IAM group mapped to a shared schema and then to one or more additional groups mapped to global roles to gain the additional roles and privileges required to complete their tasks. The combination of shared schemas and global roles allows for centralized authorization management with minimal changes to the database operationally. The database must be initially provisioned with the set of shared schemas and global roles mapped to the appropriate IAM groups, but then user authorization management can happen within IAM.

Ensure that the IAM user is only mapped to one schema, either through exclusive mapping to a database schema or as a member of one IAM group that is mapped to a shared database schema. If more than one schema is mapped for an IAM user, then the database will take any exclusive mapping as precedence over any group mapping to a shared schema. If more than one group is mapped for a user, then the database will select the oldest mapping.

When using global roles to grant privileges and roles to the user, remember that the maximum number of enabled roles in a session is 150.

If you drop and recreate IAM users and groups using the same names, then the mappings from the database to IAM using the same names will continue to work. However, recreating an IAM user will require the IAM user to do one or more of the following: create the IAM database password, re-upload the API public key, update the OCI configuration file, and then re-examine the IAM policy for database authentication and authorization with IAM. If the IAM policy specifies a group that can use or manage the database-connections and autonomous-database-family resource types, then the user will need to be added to that group to allow IAM authentication and authorization.

Accessing the database with tokens requires the user to be authorized by IAM policy and by database mapping. Accessing the database with the IAM database password verifier requires authorization through database mapping. If no database schema mapping exists for the IAM user, the IAM user is prevented from accessing the database even if they have a valid token or password.

IAM users get their authorizations to perform various tasks based on the roles that they have been granted. The following scenarios are possible:

  • IAM group mapped to a shared Oracle Database global user: With the shared database global user account, an IAM user is assigned to a shared database schema (user) through the mapping of an IAM group to the shared schema. The IAM users that are members of the group can connect to the database through this shared schema. Use of shared schemas allows for centralized management of user authorization in IAM.
  • IAM group mapped to an Oracle Database global role: The privileges that have been granted to the shared Oracle Database global role become available to the users who have added to the IAM group.
  • Local IAM user exclusively mapped to an Oracle Database global user: With an exclusive global user mapping, a dedicated database user is exclusively mapped to a local IAM user. Not as common as the shared database schema, this user is created for when the user requires their own schema objects. Oracle recommends that you grant database privileges to these users through global roles, which facilitates authorization management. These users can also have direct privilege and role grants to their exclusive schema.
7.2.2.2 Mapping an IAM Group to a Shared Oracle Database Global User

Oracle Database global users that are mapped to IAM groups and IAM dynamic groups give IAM users and OCI applications an associated schema when they login along with the privileges and roles granted to that schema.

Global roles cannot be granted to a database schema (user), they can only be mapped to a group and be assigned to an IAM user when accessing the database.
  1. Log in to the Oracle Autonomous Database instance as a user who has the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the IAM group name (which can be a dynamic group).
    For example, to create a new database global user account named shared_sales_schema and map it to an existing IAM group named WidgetSalesGroup:
    CREATE USER shared_sales_schema IDENTIFIED GLOBALLY AS
'IAM_GROUP_NAME=WidgetSalesGroup';
7.2.2.3 Mapping an IAM Group to an Oracle Database Global Role

Oracle Database global roles that are mapped to IAM groups and dynamic groups give member users and applications additional privileges and roles above what they have been granted through their login schemas.

  1. Log in to the Oracle Autonomous Database instance as a user who has been granted the CREATE ROLE or ALTER ROLE system privilege
  2. Run the CREATE ROLE or ALTER ROLE statement with the IDENTIFIED GLOBALLY AS clause specifying the name of the IAM group (which can be a dynamic group).
    For example, to create a new database global role named widget_sales_role and map it to an existing IAM group named WidgetManagerGroup:
    CREATE ROLE widget_sales_role IDENTIFIED GLOBALLY AS 
'IAM_GROUP_NAME=WidgetManagerGroup';
    All members of the widget_sales_group will be authorized with the database global role widget_sales_role when they log in to the database.
7.2.2.4 Exclusively Mapping a Local IAM User to an Oracle Database Global User

You can map a local IAM user exclusively to an Oracle Database global user.

  1. Log in to the Oracle Autonomous Database instance as a user who has been granted the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the local IAM user name.
    For example, to create a new database global user named peter_fitch and map this user to an existing local IAM user named peterfitch:
    CREATE USER peter_fitch IDENTIFIED GLOBALLY AS 
'IAM_PRINCIPAL_NAME=peterfitch';
7.2.2.5 Altering or Migrating a User Mapping Definition

You can update an IAM user to a database global user mapping by using the ALTER USER statement.

You can update database schemas that were mapped to an IAM user, and whose accounts were created using any of the CREATE USER statement clauses: IDENTIFIED BY password, IDENTIFIED EXTERNALLY, or IDENTIFIED GLOBALLY. This is useful when migrating existing schemas to using IAM. If you delete and recreate an IAM user or an IAM group using the exact same name as the previous IAM user or group, then the existing mapping from the database that uses that IAM user or IAM group name will continue to work.
  1. Log in to the Autonomous Database instance as a user who has been granted the ALTER USER system privilege.
  2. Run the ALTER USER statement with the IDENTIFIED GLOBALLY AS clause.
    For example, suppose you want to change the existing schema shared_sales_schema to a different IAM group:
    ALTER USER shared_sales_schema IDENTIFIED GLOBALLY AS 
'IAM_GROUP_NAME=BiggerWidgetSalesGroup';
7.2.2.6 Mapping Instance and Resource Principals

Instance principals and resource principals can be used by applications to retrieve database tokens to establish a connection to an Oracle Autonomous Database instance.

Only dynamic groups can be mapped when you use instance and resource principals. You cannot exclusively map instance and resource principals; you only can map them through a shared mapping and putting the instance or resource instance in an IAM dynamic group.

7.2.2.7 Verifying the IAM User Logon Information

After you configure and authorize an IAM user for the Oracle Autonomous Database instance, you can verify the user logon information by executing a set of SQL queries on the Oracle database side.

  1. Log in to the database as an IAM user that you have just configured and authorized.
    For example, to log in to the database instance inst1 as the database global user peterfitch:
    sqlplus /nolog
CONNECT "peterfitch"@inst1
Enter password: password
  2. Verify the mapped global user.
    The mapped global user is the database user account that has the IAM user authorization. User PETER_FITCH_SCHEMA is considered a global user with exclusive mapping for the IAM user peterfitch, while user WIDGET_SALES is considered a global user with shared mapping for IAM group widget_sales_group of which peterfitch is a member. 
    SHOW USER;

    Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

    USER is "PETER_FITCH_SCHEMA"

    Or

    USER is "WIDGET_SALES"
  3. Find the roles that have been granted to the centrally managed user. 
    SELECT ROLE FROM SESSION_ROLES ORDER BY ROLE;

    Output similar to the following appears:

    ROLE
----------------------------------------------------------------------
WIDGET_SALES_ROLE
...
  4. Run the following queries to check the SYS_CONTEXT namespace values for the current schema being used in this database session, current user name, session user name, authentication method, authenticated identity, enterprise identity, identification type, and server type.
    • Verify the current schema that is being used in this database session. A database schema is an object container that identifies the objects it contains. The current schema is the default container for objects name resolution in this database session. 
      SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping: 

      SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
----------------------------------------------------------------------
PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
----------------------------------------------------------------------
WIDGET_SALES
    • Verify the current user. In this case, the current user is the same as the current schema. 
      SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','CURRENT_USER')
----------------------------------------------------------------------
PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','CURRENT_USER')
----------------------------------------------------------------------
WIDGET_SALES
    • Verify the session user.
      SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','SESSION_USER')
----------------------------------------------------------------------
PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','SESSION_USER')
----------------------------------------------------------------------
WIDGET_SALES
    • Verify the authentication method. 
      SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') FROM DUAL;

      Output similar to the following appears: 

      SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
----------------------------------------------------------------------
PASSWORD_GLOBAL

      If the user is authenticating with a token, then the output is TOKEN_GLOBAL.

    • Verify the authenticated identity for the enterprise user. The IAM authenticated user identity is captured and audited when this user logs on to the database.
      SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

      Output similar to the following appears: 

      SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
----------------------------------------------------------------------
peterfitch
    • Verify the centrally managed user's enterprise identity. 
      SELECT SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') FROM DUAL;

      Enterprise Identity will show the OCI Identity (OCID) of the IAM user or OCI application. Output similar to the following appears: 

      SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
----------------------------------------------------------------------
ocid1.user.region1..aaaaaaaaj7ot4g2sagkjtw3enbg4ied3x554zwyywurgrm2232j4crm5zha
    • Verify the identification type. 
      SELECT SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') FROM DUAL

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
----------------------------------------------------------------------
GLOBAL EXCLUSIVE

      Or

      SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
----------------------------------------------------------------------
GLOBAL SHARED
    • Verify the server type.
      SELECT SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE') FROM DUAL;

      Output similar to the following appears. In this case, the LDAP server type is IAM. 

      SYS_CONTEXT('USERENV','LDAP_SERVER_TYPE')
----------------------------------------------------------------------
OCI_IAM

7.2.3 Configuring Proxy Authentication

Proxy authentication allows an IAM user to proxy to a database schema for tasks such as application maintenance.

Parent topic: Configuring Oracle Autonomous Database for IAM

7.2.3.1 About Configuring Proxy Authentication

IAM users can connect to Oracle Autonomous Database by using proxy authentication.

Proxy authentication is typically used to authenticate the real user and then authorize them to use a database schema with the schema privileges and roles in order to manage an application. Alternatives such as sharing the application schema password are considered insecure and unable to audit which actual user performed an action.

A use case can be in an environment in which a named IAM user who is an application database administrator can authenticate by using their credentials and then proxy to a database schema user (for example, hrapp). This authentication enables the IAM administrator to use the hrapp privileges and roles as user hrapp in order to perform application maintenance, yet still use their IAM credentials for authentication. An application database administrator can sign in to the database and then proxy to an application schema to manage this schema.

You can configure proxy authentication for both the password authentication and token authentication methods.

7.2.3.2 Configuring Proxy Authentication for the IAM User

To configure proxy authentication for an IAM user, the IAM user must already have a mapping to a global schema (exclusive or shared mapping). A separate database schema for the IAM user to proxy to must also be available.

After you ensure that you have this type of user, alter the database user to allow the IAM user to proxy to it.
  1. Log in to the Autonomous Database instance as a user who has the ALTER USER system privileges.
  2. Grant permission for the IAM user to proxy to the local database user account.
    An IAM user cannot be referenced in the command so the proxy must be created between the database global user (mapped to the IAM user) and the target database user.
    In the following example, hrapp is the database schema to proxy to, and peterfitch_schema is the database global user exclusively mapped to user peterfitch. 
    ALTER USER hrapp GRANT CONNECT THROUGH peterfitch_schema;
At this stage, the IAM user can log in to the database instance using the proxy. For example, to connect using a password verifier:
CONNECT peterfitch[hrapp]@connect_string
Enter password: password

To connect using a token:

CONNECT [hrapp]/@connect_string
7.2.3.3 Validating the IAM User Proxy Authentication

You can validate the IAM user proxy configuration for both password and token authentication methods.

  1. Log in to the Autonomous Database instance as a user who has the CREATE USER and ALTER USER system privileges.
  2. Connect at the IAM user and execute the SHOW USER and SELECT SYS_CONTEXT commands.
    For example, suppose you want to check the proxy authentication of the IAM user peterfitch when they proxy to database user hrapp. You will need to connect to the database using the different types of authentication methods shown here, but the output of the commands that you execute will be the same for all types.
    • For password authentication: 
      CONNECT peterfitch[hrapp]/password\!@connect_string
SHOW USER;
--The output should be USER is "HRAPP"
SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') FROM DUAL;
--The output should be "PASSWORD_GLOBAL"
SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL;
--The output should be "PETERFITCH_SCHEMA"
SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;
--The output should be "HRAPP"
    • For token authentication: 
      CONNECT [hrapp]/@connect_string
SHOW USER;
--The output should be USER is "HRAPP "
SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') FROM DUAL;
--The output should be "TOKEN_GLOBAL"
SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL;
--The output should be "PETERFITCH_SCHEMA"
SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;
--The output should be "HRAPP"

7.3 Configuring IAM for Oracle Autonomous Database

To configure IAM to work with the Oracle Autonomous Database instance, an IAM administrator may need to create an IAM policy and have users create an IAM database password.

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.3.1 Creating an IAM Policy to Authorize Users Authenticating with Tokens

To configure IAM to work with the Oracle Autonomous Database instance, an IAM administrator must create an IAM policy (if using IAM tokens), create IAM groups and manage group membership.

The IAM administrator should work with the database administrator to create the appropriate IAM groups for databases. Individual IAM users will need to create an IAM database password in their profile if they are using password verifiers.
You do not need to create a policy for users who are authenticating with password verifiers.
  • Use the allow group command to create the policy. For example: 
    allow group DBUsers to use database-connections in tenancy
  • To create a policy that limits members of DBUsers group to access autonomous databases in compartment testing_compartment only
    allow group DBUsers to use autonomous-database-family in compartment testing_compartment
  • To create a policy that limits group access to a single database in a compartment:
    allow group DBUsers to use autonomous-database-family in compartment testing_compartment where target.database.id = 'ocid1.autonomousdatabase.oc1.iad.aaaabbbbcccc'
Note the following:
  • The database-connections resource type is included in the autonomous-database-family resource type. Either resource can be used, depending on your use case.
  • The minimum tag to enable access to the database is use. You can also use the manage tag to enable access to the database.

See Oracle Cloud Infrastructure Documentation for more information about the syntax of policy statements.

Parent topic: Configuring IAM for Oracle Autonomous Database

7.3.2 Creating an IAM Database Password

The IAM database password, different from the Oracle Cloud Infrastructure (OCI) console password, and set by the IAM user, is required for the Oracle Autonomous Database password verification process.

The set of allowed characters for the OCI IAM database password is similar to the set of allowed characters for the OCI console password except that the double quotation mark character is not allowed for the OCI IAM database password. See Managing User Credentials for information about creating an IAM database password.
  1. Log in to the OCI console to your user page.
  2. Access My profile or User settings (top right in the navigation toolbar) depending on the IAM version that you are using.
  3. In your profile or settings, in the left, under Resources, click on the Database Passwords link.
  4. Click the Create Database Password button.
  5. Add a description and the password, ensuring that you apply the listed complexity rules.
  6. Click Create Database Password to save the password.
    After the password is created, its description and creation date are listed under Database Passwords.

Parent topic: Configuring IAM for Oracle Autonomous Database

7.4 Accessing the Database Using an Instance Principal or a Resource Principal

An Oracle Cloud Infrastructure (OCI) application can connect to the database instance using its own instance or resource principal.

Instance and resource principals are identities for Oracle Cloud Infrastructure (OCI) compute instances and applications. Applications on an OCI compute instance can use the associated identity principal to get a db-token to connect to the database. Applications can also register with IAM to have its own resource principal to be used to get a db-token to connect with the database.

You cannot map instance principals and resource principals exclusively to a database global schema. The instance principal and resource principal must be in a dynamic group that is mapped to a database shared schema.

Related Topics

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.5 Configuring the Database Client Connection

Configuring the IAM client connection controls the authentication of IAM users to the Oracle Autonomous Database instance.

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.5.1 About Connecting to an Oracle Autonomous Database Instance Using IAM

IAM users can connect to the Oracle Autonomous Database instance by using either an IAM database password verifier or an IAM token.

Using the IAM database password verifier is similar to the Oracle Database password authentication process. However, instead of the password verifier (encrypted hash of the password) being stored in the Oracle Autonomous Database, the verifier is instead stored as part of the Oracle Cloud Infrastructure (OCI) IAM user profile.

The second connection method, the use of an IAM token for the database, is more modern. The use of token-based access is a better fit for Cloud resources such as Oracle Autonomous Database. The token is based on the strength that the IAM endpoint can enforce. This can be multi-factor authentication, which is stronger than the use of passwords alone. Another benefit of using tokens is that the password verifier (which is considered sensitive) is never stored or available in memory.

Parent topic: Configuring the Database Client Connection

7.5.2 Client Connections That Use an IAM Database Password Verifier

After you have configured the authorization needed for the IAM user, this user can log in using existing client application, such as SQL*Plus or SQLcl without additional configuration.

The IAM user enters the IAM user name and IAM database password (not the Oracle Cloud Infrastructure console password) using any currently supported database client. The only constraint is that the database client version be either Oracle Database release 12.1.0.2 or later (or patched) to allow Oracle Database 12c passwords. The database client must be able to use the 12C password verifier. Using the 11G verifier encryption is not supported with IAM. No special client or tool configuration is needed for the IAM user to connect to the Oracle Autonomous Database instance.

Parent topic: Configuring the Database Client Connection

7.5.3 Client Connections That Use a Token

For IAM token access to the Oracle Autonomous Database, the client application or tool requests a database token from IAM for the IAM user.

The client application will pass the database token directly to the database client through the database client API.

If the application or tool has not been updated to request an IAM token, then the IAM user can use Oracle Cloud Infrastructure (OCI) command line interface (CLI) to request and store the database token. You can request a database access token (db-token) using the following credentials:

  • Security tokens (with IAM authentication), delegation tokens (in the OCI cloud shell) and API-keys, which are credentials that represent the IAM user to enable the authentication
  • Instance principal tokens, which enable instances to be authorized actors (or principals) to perform actions on service resources after authenticating
  • Resource principal token, which is a credential that enables the application to authenticate itself to other Oracle Cloud Infrastructure services

When the IAM users logs into the client with a slash / login and the OCI_IAM parameter is configured (sqlnet.ora, tnsnames.ora, or as part of a connect string), then the database client retrieves the database token from a file. If the IAM user submits a user name and password, the connection will use the IAM database verifier access described for client connections that use IAM database password verifiers. The instructions in this guide show how to use the OCI CLI as a helper for the database token. If the application or tool has been updated to work with IAM, then follow the instructions for the application or tool. Some common use cases include the following: SQLPlus on-premises, SQLcl on-premises, SQL*Plus in Cloud Shell, or applications that use SEP wallets.

Related Topics

Parent topic: Configuring the Database Client Connection

7.5.4 Client Connections That Use Database Links

The use of database links in Oracle Autonomous Database-to-IAM connections is supported.

The method of configuring database links for Oracle Autonomous Database connections to IAM depends on the Oracle Autonomous Database platform.

  • Oracle Autonomous Database on Shared Exadata Infrastructure: You can use fixed user database links in which a database user is used for the fixed database link. The database user for creating the database link can only use password authentication with the database link. The IAM user can authenticate to the source database using either password or token access. You cannot configure IAM users as fixed database links, nor can you use connected or current user database links. See Using Oracle Autonomous Database on Shared Exadata Infrastructure
  • Oracle Autonomous Database on Dedicated Exadata Infrastructure: You can use connected user and fixed user database links, but not current user database links. For connected user database links, an IAM user must be provisioned to both the source and target link databases. You can use a database password verifier or an IAM database token to connect and use connected user database links. For a fixed user database link, a user can connect to the target database using a target database user with password authentication. In addition, fixed user database links support the connection to a target database as an IAM user (password verifier or IAM token) to use public fixed database user database links or public fixed IAM user database links. See Using Oracle Autonomous Database on Dedicated Exadata Infrastructure

Parent topic: Configuring the Database Client Connection

7.5.5 Common Database Client Configurations

IAM users can connect to the Oracle Autonomous Database instance using client tools such as SQLcl on a laptop.

Parent topic: Configuring the Database Client Connection

7.5.5.1 Configuring a Client Connection for SQL*Plus That Uses an IAM Database Password

You can configure SQL*Plus to use an IAM database password.

  • As the IAM user, log in to the Oracle Autonomous Database by using the following syntax:
    CONNECT user_name@db_connect_string
Enter password: password

    In this specification, user_name is the IAM user name. There is a limit of 128 bytes for the combined domain_name/user_name.

    The following example shows how IAM user peter_fitch can log in to an Oracle Autonomous Database instance. 

    sqlplus /nolog
connect peter_fitch@db_connect_string
Enter password: password

    Some special characters will require double quotation marks around user_name and password. For example: 

    "peter_fitch@example.com"@db_connect_string

"IAM database password"
7.5.5.2 Configuring a Client Connection for SQL*Plus That Uses an IAM Token

You can configure a client connection for SQL*Plus that uses an IAM token.

  1. Ensure you have an IAM user account.
  2. Check with an IAM administrator and Autonomous Database administrator to ensure you have a policy allowing you to access the database in the compartment or your tenancy and that you are mapped to a global schema in the database.
  3. If your application or tool does not support direct IAM integration, then download, install, and configure the OCI CLI. (See OCI Command Line Interface Quickstart.) Set up an API key as part of the OCI CLI configuration and select default values.
    1. Set up the API key access for the IAM user.
    2. Retrieve the db-token. For example:
      • Retrieving a db-token with an API-key using the Oracle Cloud Infrastructure (OCI) command-line interface:
        oci iam db-token get
      • Retrieving a db-token with a security (or session) token:
        oci iam db-token get --auth security_token

        If the security token has expired, a window will appear so the user can log in to OCI again. This generates the security token for the user. OCI CLI will use this refreshed token to get the db-token.

      • Retrieving a db-token with a delegation token: When you log in to the cloud shell, the delegation token is automatically generated and placed in the /etc directory. To get this token, execute the following command in the cloud shell:
        oci iam db-token get
      • Retrieving an instance token by using the OCI command-line interface:
        oci iam db-token get --auth instance_principal
    See Required Keys and OCIDs for more information.
  4. Ensure that you are using the latest release updates for the Oracle Database client releases 19c and 21c.
    This configuration only works with the Oracle Database client release 19c or 21c.
  5. Follow the existing process to download the wallet from the Oracle Autonomous Database and then follow the directions for configuring it for use with SQL*Plus.
    1. Confirm that DN matching is enabled by looking for SSL_SERVER_DN_MATCH=ON in sqlnet.ora.
    2. Configure the database client to use the IAM token by adding TOKEN_AUTH=OCI_TOKEN to the sqlnet.ora file. Because you will be using the default locations for the database token file, you do not need to include the token location.
    The TOKEN_AUTH and TOKEN_LOCATION values in the tnsnames.ora connect strings take precedence over the sqlnet.ora settings for that connection. For example, for the connect string, assuming that the token is in the default location (~/.oci/db-token for Linux):
    (description= 
  (retry_count=20)(retry_delay=3)
  (address=(protocol=tcps)(port=1522)
  (host=example.us-phoenix-1.oraclecloud.com))
  (connect_data=(service_name=aaabbbccc_exampledb_high.example.oraclecloud.com))
  (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
     OU=Oracle BMCS US, O=Example Corporation, 
     L=Redwood City, ST=California, C=US")
  (TOKEN_AUTH=OCI_TOKEN)))
After the connect string is updated with the TOKEN_AUTH parameter, the IAM user can log in to the Oracle Autonomous Database instance by running the following command to start SQL*Plus. You can include the connect descriptor itself or use the name of the descriptor from the tnsnames.ora file.
connect /@exampledb_high

Or:

connect /@(description= 
  (retry_count=20)(retry_delay=3)
  (address=(protocol=tcps)(port=1522)
  (host=example.us-phoenix-1.oraclecloud.com))
  (connect_data=(service_name=aaabbbccc_exampledb_high.example.oraclecloud.com))
  (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
     OU=Oracle BMCS US, O=Example Corporation, 
     L=Redwood City, ST=California, C=US")
  (TOKEN_AUTH=OCI_TOKEN)))

The database client is already configured to get a db-token because TOKEN_AUTH has already been set, either through the sqlnet.ora file or in a connect string. The database client gets the db-token and signs it using the private key and then sends the token to the Oracle Autonomous Database. If an IAM user name and IAM database password are specified instead of slash /, then the database client will connect using the password instead of using the db-token.

7.6 Troubleshooting IAM Logins

The ORA-01017: invalid username/password; logon denied error can be caused by several different issues throughout the Oracle Autonomous Database integration with Identity and Access Management (IAM).

Parent topic: Authenticating and Authorizing IAM Users for Oracle Autonomous Databases

7.6.1 Areas to Check on the Client-Side for ORA-01017 Errors

Client-side ORA-01017 errors can result from problems with IAM credentials, client configuration, or problems with the IAM profile.

Troubleshooting the IAM Token

  • Check the version of the Oracle Cloud Infrastructure (OCI) CLI used for the token. The OCI CLI must be at least OCI version 3.4, which includes the command to get the new db-token from IAM. To check the version of OCI, run the following command: 
    oci --version
  • Check the Oracle Database Client version. You can find the latest version by checking the Oracle Database documentation. Currently, only the following drivers are supported:
    • JDBC: Version 19.13.0.0.1 and later versions of 19c JDBC clients JDBC: Version 21.5 and later versions of 21c
    • Instant Client/SQL*Plus (Linux only): Version 19.13 (annotated with -2) and later versions of 19c
    • Instant Client/OCI/SQL*Plus (Linux only): Version 21.5 and later versions of 21c
    • SQLcl: version 21.4 and later
    • ODP.net: Version 19.13 and higher versions of 19c
    • ODP.net: Version 21.4 and higher versions of 21c

    The latest version of these drivers is needed when you use IAM tokens to access the database. All supported database clients will work when using IAM database passwords.

  • Check the token location that was specified in the tnsnames.ora file. The database clients and OCI CLI use the same default location for storing and retrieving database tokens and the private key (~/.oci/db-token). You can specify a different location, but both OCI CLI and the database client must be configured to use the same directory. Ensure that the correct TOKEN_LOCATION value is specified in the connect string, in the tnsnames.ora or sqlnet.ora file. The connect string takes precedence over tnsnames.ora, which takes precedence over the value of TOKEN_LOCATION in sqlnet.ora.
  • Check if the token has expired. The IAM database token is only valid for one hour. After the database token has expired, re-run the following OCI CLI command to request another token if you are using an API-key:
    oci iam db-token get
  • Check the TOKEN_AUTH parameter value in tnsnames.ora. Ensure that the parameter TOKEN_AUTH=OCI_TOKEN is set in either the connect string, tnsnames.ora, or sqlnet.ora. The connect string takes precedence over tnsnames.ora, which takes precedence over sqlnet.ora for the value of TOKEN_AUTH.
  • Check if there is a missing token or private key from the default user-specified token location. Ensure that both the token and the private key are in the directory that is specified by the TOKEN_LOCATION after you run the OCI CLI command oci cli db-token get. You can find the db-token and private key location by running the following command:
    [oracle@localhost ~]$ oci iam db-token get

    Output similar to the following appears:

    Private key written at /home/oracle/.oci/db-token/oci_db_key.pem
db-token written at: /home/oracle/.oci/db-token/token
db-token is valid until 2022-01-05 15:36:51

    If the location does not match the TOKEN_LOCATION setting, either update the OCI CLI command or update the TOKEN_LOCATION parameter.

  • Check your OCI IAM profile.
    • Ensure that the public API-key exists in the OCI user account. The OCI CLI will default to using the API-key on the client to request a db-token from IAM. If the public API-key is not in the OCI user account, then IAM will not return a database token.
    • Ensure that the IAM account is not locked. If it is, then ask the IAM administrator to unlock it.
    • If you are using the IAM database password, then ensure that you set the IAM database password in your IAM profile.
  • If you are not using the API-key, then explicitly state that you are using the security token. Use the following command: 
    oci iam db-token get --auth security_token

    If the security token does not exist or has expired, this command will try to open the browser for you to sign into IAM (or your federated IdP). This command will fail if you do not have a browser in your environment.

Troubleshooting Both the IAM Database Password and the IAM Token

  • Check client tracing on Oracle Instant Client only. Client tracing can provide some information when you use SQL*Plus with the Instant Client. You can generate Oracle Database client trace files using two different tracing levels.

Related Topics

Parent topic: Troubleshooting IAM Logins

7.6.2 Database Client Trace Files

You can generate two levels of trace files to troubleshoot IAM connections on client side.

The two levels of trace files that you can generate are as follows:

  • Low level tracing prints traces in case of failures:

    • If TCPS is not set up for the IAM connection, then it prints a message that the protocol has to be TCPS.

    • If SSL_SERVER_DN_MATCH is not set to TRUE, then it prints a message that the value is FALSE.

    • If an invalid TOKEN_LOCATION has been specified, then it prints a message that the token location does not exist.

    • If the db-token and private key are not present at the specified TOKEN_LOCATION or the default token location, then it prints a message.

    • If the application has passed in only db-token or private key, it prints a message for the missing attribute.

    • If the db-token has expired, then it prints a message.

  • High level tracing prints traces in case of failure as mentioned above. In addition, it prints traces in case of success, as follows:

    • It prints where SSL_SERVER_DN_MATCH is present, tnsnames.ora or sqlnet.ora. It also prints the value as TRUE if set to TRUE.

    • If both the db-token and private key are set by the application, then it prints a message.

    • If TOKEN_AUTH has the correct value OCI_TOKEN, then it prints the value.

    • If db-token is not expired, then it prints a message.

To control client tracing for IAM connections, you can use one of these methods:

  • Add the following settings to the client side sqlnet.ora file:

    • EVENT_25701=14 for low level tracing

    • EVENT_25701=15 for high level tracing

  • Set the environment variable EVENT_25701:

    • EVENT_25701=14 for low level tracing

    • EVENT_25701=15 for high level tracing

Client trace files are created in the following locations:

  • Linux: $ORACLE_HOME/log/diag/clients

  • Windows: %ORACLE_HOME%\log\diag\clients

You can use the ADR_BASE parameter in the client side sqlnet.ora to specify the directory in which tracing messages are stored. Ensure that the directory path is valid and has write permissions. Ensure that the diag_adr_enabled parameter is not set to false.

An example of setting ADR_BASE is as follows: 

ADR_BASE=/oracle/iam/trace

Parent topic: Troubleshooting IAM Logins

7.6.3 Areas to Check in the Oracle Autonomous Database for ORA-01017 Errors

ORA-01017 errors in the Oracle Autonomous Database instance can arise from the way that it was configured to work with IAM.

  • Check if the IAM configuration has been enabled. The Oracle Autonomous Database server must be configured for IAM integration and one or more database schemas (database users) must be mapped to IAM users or groups. This applies to both the IAM token and IAM database password use cases. To check if the configuration has been enabled, run the following command in SQL*Plus:
    SELECT NAME, VALUE 
FROM V$PARAMETER 
WHERE NAME='identity_provider_type';

    Alternatively, you can use this command:

    SHOW PARAMETER IDENTITY_PROVIDER_TYPE

    If the returned value does not equal OCI_IAM, then enable the external authentication.

  • Check the schemas that have been mapped to IAM. Note which which IAM users and IAM groups are used in the mapping. You can find this information by running the following query in SQL*Plus:
    SELECT USERNAME, EXTERNAL_NAME, CREATED
FROM DBA_USERS 
WHERE AUTHENTICATION_TYPE='GLOBAL';

    In the output, check that there is at least one EXTERNAL_NAME that starts with either IAM_USER or IAM_GROUP. Make a note of the IAM user or group name. If there are no global schemas, then you must create a new schema, or alter an existing schema, and then map it to an IAM user or IAM group that the user is a member of.

  • Check if Oracle Autonomous Database needs to be restarted. In some cases, an Oracle Autonomous Database instance that existed before the IAM configuration was introduced may need to be restarted. But before doing so, follow all other troubleshooting guidelines before trying to restart the database.

Related Topics

Parent topic: Troubleshooting IAM Logins

7.6.4 ORA-01017 Errors Caused by Improperly Configured IAM Users

Several ORA-01017 errors can arise from improperly configured IAM users.

  • Ensure that the IAM user can log in to the Oracle Autonomous Database instance. Ask the IAM user to try logging in an IAM user but not as a federated user. Ensure that this user is not locked out of the account. (The user should contact an IAM administrator if this happens.) If the user's IAM account is locked, then this user cannot log in to the Oracle Autonomous Database instance.

    You should also check the IAM user name and IAM groups that the user is a member of. One of these (user name or group names) should match the mapped IAM user and group name that you found from the Oracle Autonomous Database server. If there is no mapping, then the user will be denied access to the database. If this is the case, then an IAM administrator should add the user to an IAM group that is mapped to the Autonomous Database instance that the user needs to access.

  • Ensure that the API public key is registered in the IAM user profile. If the Oracle Autonomous Database instance configuration with IAM uses tokens, and if you use an API-key to retrieve the database token, then the API public key needs to be registered in the user's IAM user profile.
  • Ensure that the IAM database password has been set in the IAM user profile. If the Oracle Autonomous Database instance configuration with IAM uses database password authentication, then ensure that an IAM database password has been set in the user IAM user profile. In addition, ensure that Database Passwords is an allowed setting in the User Capability section of the IAM user profile.

Related Topics

Parent topic: Troubleshooting IAM Logins

7.6.5 Actions IAM Administrators Can Take to Address ORA-01017 Errors

Several actions to remedy ORA-01017 errors can only be performed by IAM administrators.

  • Check if the IAM user needs to recreate API-keys. If the IAM user was deleted and then recreated with the exact same user name, then Oracle Cloud Infrastructure (OCI) IAM will consider this as a different user with a different user OCID. In this case, the IAM user will need to recreate their user account and API-key. This action does not affect the IAM user and IAM group mappings in the database.
  • If necessary, unlock the IAM user account. If the user is inactive or otherwise locked, then an IAM administrator will need to unlock the user account before database access can be allowed.
  • Check the IAM policy. An IAM policy is required to allow the user to use IAM database tokens to access the database. The resource is called database-connections and it is also a member of the autonomous-database-family. You do not need to create IAM policies if the Oracle Autonomous Database instance uses IAM database passwords. When you configure the IAM policy, remember that the use or manage tag is required for the policy. For example:
    • Set allow all-users to use autonomous-database-family in the tenancy. This enables all IAM tenancy users to use IAM database tokens to access all Oracle Autonomous Database instances in the tenancy.
    • Set allow group DBUsers to use database-connections in the production_compartment compartment. This enables IAM users who are members of the DBUsers IAM group to use IAM tokens to access databases in the production_compartment compartment.
  • Check the mappings for IAM users and groups. The IAM user either has an exclusive mapping from a schema (that is, a database user) in the database or is a member of an IAM group that is mapped to a schema in the database. Run the following SQL*Plus query and review its output to find the mapped IAM users and groups. Ensure that the user has one mapping to a database schema.
    SELECT USERNAME, EXTERNAL_NAME, 
FROM DBA_USERS 
WHERE AUTHENTICATION_TYPE='GLOBAL';

Related Topics

Parent topic: Troubleshooting IAM Logins