Resolved! Creating Credentials for AI Vector Search on Oracle Database 23ai ADB

In my previous blog post on Get Started with Oracle Database 23ai AI Vector Search, I mentioned that it was not yet possible to use third-party REST APIs to perform tasks like generating embeddings and text.

This is only the tip of the iceberg. Next up is text generation but there is currently an issue that prohibits the adding of credentials required to work with third-party REST APIs using the DBMS_VECTOR and DBMS_VECTOR_CHAIN packages on the ADB. Hopefully, this will be fixed very soon.

I am happy to post that with the latest 23.5.0.24.06 database release update, this is no longer an issue!

To use the DBMS_VECTOR and DBMS_VECTOR_CHAIN PL/SQL subprograms with third-party REST APIs, there are two key tasks that an ADB administrator needs to do after creating an application schema:

1. Grant the CREATE CREDENTIAL privilege; and

   grant create credential to &app_schema.;
2. Allow the schema to make network connections using the DBMS_NETWORK_ACL_ADMIN procedure.

   begin
       dbms_network_acl_admin.append_host_ace(
           host => '*'
           , ace => xs$ace_type(
               privilege_list => xs$name_list('connect')
               , principal_name => '&app_schema.'
               , principal_type => xs_acl.ptype_db
           )
       );
   end;
   /

There were no issues performing the second task, however, for the first, the ADMIN user itself does not have that privilege. Hence, when you execute the procedure DBMS_VECTOR.CREATE_CREDENTIAL, you would have received this:

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_VECTOR", line 1259
ORA-06512: at "SYS.DBMS_ISCHED", line 2592
ORA-06512: at "SYS.DBMS_ISCHED", line 2586
ORA-06512: at "SYS.DBMS_CREDENTIAL", line 40
ORA-06512: at "SYS.DBMS_VECTOR", line 1251
ORA-06512: at line 2
27486. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a scheduler operation without the
           required privileges.
*Action:   Ask a sufficiently privileged user to perform the requested
           operation, or grant the required privileges to the proper user(s).

It looks like the internals of the procedure has changed, and you no longer need that database privilege. Instead, grant the following privilege:

grant execute on c##cloud$service.dbms_cloud_ai to &app_schema.;

After which, you should be able to run the DBMS_VECTOR.CREATE_CREDENTIAL (or DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL) procedure successfully.

begin
    dbms_vector_chain.create_credential(
        credential_name => '&credential_name.'
        , params => json(trim(q'[
{
    "user_ocid": "&user_ocid.",
    "tenancy_ocid": "&tenancy_ocid.",
    "compartment_ocid": "&compartment_ocid.",
    "private_key": "&api_private_key.",
    "fingerprint": "&api_public_key_fingerprint."
}
        ]'))
    );
end;
/

Once created successfully, you should be able to view the credentials using the USER_CREDENTIALS view:

select * from user_credentials;

Here it is in action.

:P2_TEXT :=
  dbms_vector.utl_to_generate_text(
    data => :P2_PROMPT
    , params => json(trim(q'[
{
  "provider": "ocigenai",
  "credential_name": "MY_CREDENTIALS",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/generateText",
  "model": "cohere.command",
  "inferenceRequest": {
    "maxTokens": 200,
    "temperature": 0.1
  }
}
    ]'))
  )
;

Once the credentials are created, the administrator may revoke the execute privileges on DBMS_CLOUD_AI, however, the network ACL for the parsing schema is still required.

Finally, I received this release upgrade with the ADB patch ADBS-24.6.4.2 that was applied on my Always Free instance on Friday, June 28, 2024. Check your maintenance history if this has already been done for your instance. Good luck!

Schedule a Discovery Call

Credits

• Photo by Ashkan Forouzani on Unsplash