Hi,
We are trying to display the users in our oracle db in MWS. So we have created a datasource and then in the Directory services administration created the database direcory service by specifying the queries for users and authentication and also selected the created datasource, but the users are not populated in manage users. Can anyone let me know what could be the reason for this? We are using 7.1.2 server
Regards,
Teja
It appears that the SQL queries that you entered for the DB Directory Service Configuration aren’t returning results. You might have a look at the logs and double check the error messages. You should probably also attempt to double check the configured queries with a SQL query tool.
Regards,
–mark
Hi,
I am also experiencing the same problem. The database I am trying to retrieve users from is Oracle and MWS version is 8.0.2.0.50.
When I search for users using the directory service I created for Oracle, I am not seeing any users. The SQL queries that I entered are returning results when I run them using SQL Developer.
I updated the SQL query to select user as follows to make sure the problem is not with the SQLs I entered and the way I am specifying parameters (e.g. {uid})
Select ‘userid’ USERID, ‘lastName’ LASTNAME, ‘firstName’ FIRSTNAME, ‘fullname’ FULLNAME, ‘userDN’ USERDN, ‘lastname@xyz.com’ EMAIL from dual
The above generic SQL should always return one user (with user id = ‘userid’)… Even with this query, I am not seeing any users in the user management screen.
Anything else I should be looking at? May be a property or something? Also, I updated the logging level to Trace but I don’t see any SQL statements being issued in any of the logs and also there are no error messages. Any idea where the SQLs would be logged?
Thanks in advance for looking into this…
Can you use any sql analyzer tools with your DB? Something like a DB Spy that will enable you to see what queries are going to the server?
Currently, MWS doesn’t allow you to trace the SQL calls because we’ve seen that cripple the server when it is improperly configured. (eg: Store the logs in a db and log every sql call)
Regards,
–mark
Hi Mark,
Thanks for the response. Yeah, I ended up using sys.v_$sql view in Oracle to monitor the SQL stmts that MWS was issuing when we search for users. When I search by USERID in the advanced search screen on MWS, it just doesn’t give me any results even though the SQL below shows me that the Oracle has returned one row… However, the generic search using the keyword parameter on the user screen is working fine now after I changed the SQL queries to generate the distinguished name (DN) attribute as follows - “cn=,userid=”. I am now able to see all my users in the database if I search by entering user name or userid in the keyword textbox. Initially, I was setting the DN to a simple user id and that didn’t work at all in any of the search screens. So I guess the key is to set the distinguished name correctly (name/value pairs separated by commas)…
By the way, here’s the SQL I used to monitor the SQL stmts being issued by MWS…
select LAST_LOAD_TIME, SQL_TEXT, FIRST_LOAD_TIME, LAST_ACTIVE_TIME, ROWS_PROCESSED, MODULE, PROGRAM_ID from sys.v_$sql where SQL_TEXT like ‘%USERTABLE%’ and MODULE like ‘%JDBC Connect Client%’ order by LAST_LOAD_TIME desc;
Sounds good. Thanks for the follow-up.
Regards,
–mark