We are getting an "ORA-06550: Identifier <DEMO_SECURITY.AUTHENTICATE_USING_AD> must be declared" error in APEX.
We have a simple Oracle package that queries Active Directory using DBMS_LDAP to validate username and password. Basically, it creates a session and then does a simple bind which passes or fails to validate the credentials.
Our DEMO_SECURITY.AUTHENTICATE_USING_AD calls a function whose essence comes down these steps shown here:
l_session := dbms_ldap.init(l_host, l_port);
l_return := dbms_ldap.simple_bind_s(l_session, l_domain || '\' || p_username, p_password);
Q1. What user context does APEX run its authentication requests to the database?
Q2. What permissions are needed? Who gets them (reference Q1)?
BACKGROUND
Here you can see it working in our development environment. When proper credentials are provided, it returns “Authenticated” in the DBMS output and boolean (TRUE) to the calling code. Obviously, when it fails, it returns (FALSE) to the calling code.
We installed APEX on this database, and granted access to the schema that owns this DEMO_SECUIRTY package. We create a custom authentication scheme that calls this package in our demo APEX application.
When we log into the sample application, it validates and presents the main page…
This is what we were expecting.
Then, we copy this same package to another database in a different schema to implement it across multiple databases and applications, we find that the code works in our new database and schema, just like it did in the original schema.
We then try to incorporate it into our second application, as shown here:
Unfortunately, we are getting this error. We are pretty sure this is a permissions issue, don't know which permissions are needed and to which account they should be granted. When APEX makes the call (during runtime login) to the database, what is the user context that it accesses Oracle through? Is it the Workspace Administrator context? Is there a proxy user? Why did it work in the test environment, but not here?
