[
https://issues.apache.org/jira/browse/AMBARI-26314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17931806#comment-17931806
]
Basapuram Kumar commented on AMBARI-26314:
------------------------------------------
PR - https://github.com/apache/ambari/pull/3948
> Ambari's ldap-sync issue for oralce-19.x+
> -----------------------------------------
>
> Key: AMBARI-26314
> URL: https://issues.apache.org/jira/browse/AMBARI-26314
> Project: Ambari
> Issue Type: Bug
> Components: ambari-server
> Environment: Tried on RHEl-8 & RHEL-9
> Oracle-19c,20.x
> MySQL-8.x
>
> Reporter: Basapuram Kumar
> Priority: Major
> Attachments: Screenshot 2025-03-02 at 17.10.01.png,
> image-2025-03-02-17-33-02-167.png, image-2025-03-02-17-33-26-676.png,
> image-2025-03-02-18-03-54-383.png
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> During the test with Oracle-19.x and higher version, observed that ldap
> syncing is failing for its groups with. the below error.
>
>
> {code:java}
> ambari-server sync-ldap --all {code}
>
>
> Response:
>
> {code:java}
> [root@ora19codp32333 conf]# ambari-server sync-ldap --all
> Using python /usr/bin/python3.11
> Syncing with LDAP...
> Enter Ambari Admin login: admin
> Enter Ambari Admin password:Fetching LDAP configuration from DB.
> Syncing all...ERROR: Exiting with exit code 1.
> REASON: Caught exception running LDAP sync. Exception [EclipseLink-4002]
> (Eclipse Persistence Services - 2.6.2.v20151217-774c696):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or
> view does not existError Code: 942
> Call: SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> Query: ReadAllQuery(referenceClass=GroupEntity sql="SELECT group_id,
> group_name, group_type, ldap_group, principal_id FROM "groups"") {code}
> !image-2025-03-02-18-03-54-383.png!
>
> For sample LDAP setup , used the knox's Demo LDAP.
> +*Repro steps.*+
> For Setting up LDAP, please add the knox into the Ambari,
>
> !image-2025-03-02-17-33-02-167.png!
>
> Followed by start the Demo LDAP.
> !image-2025-03-02-17-33-26-676.png!
> h2. Install Ldapsearch
> #
> \{{ dnf install -y openldap-clients}}
> h2. Search User
> #
> {code:java}
> ldapsearch -x -H ldap://`hostname -f`:33389 \
> -D "uid=admin,ou=people,dc=hadoop,dc=apache,dc=org" \
> -w admin-password \
> -b "dc=hadoop,dc=apache,dc=org" {code}
> Response
> {code:java}
> # extended LDIF
> #
> # LDAPv3
> # base <dc=hadoop,dc=apache,dc=org> with scope subtree
> # filter: (objectclass=*)
> # requesting: ALL
> ## sam, people, hadoop.apache.org
> dn: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> sn: sam
> cn: sam
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: c2FtLXBhc3N3b3Jk
> uid: sam# analyst, groups, hadoop.apache.org
> dn: cn=analyst,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: analyst
> objectclass: groupofnames
> objectclass: top
> member: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> member: uid=tom,ou=people,dc=hadoop,dc=apache,dc=org
> description: analyst group# scientist, groups, hadoop.apache.org
> dn: cn=scientist,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: scientist
> objectclass: groupofnames
> objectclass: top
> member: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> description: scientist group# admin, people, hadoop.apache.org
> dn: uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> sn: Admin
> cn: Admin
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: YWRtaW4tcGFzc3dvcmQ=
> uid: admin# keyadmin, people, hadoop.apache.org
> dn: uid=keyadmin,ou=people,dc=hadoop,dc=apache,dc=org
> sn: keyAdmin
> cn: keyAdmin
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: a2V5YWRtaW4tcGFzc3dvcmQ=
> uid: keyadmin# people, hadoop.apache.org
> dn: ou=people,dc=hadoop,dc=apache,dc=org
> ou: people
> objectclass: top
> objectclass: organizationalUnit# admin, groups, hadoop.apache.org
> dn: cn=admin,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: admin
> objectclass: groupofnames
> objectclass: top
> member: uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> description: admin group# tom, people, hadoop.apache.org
> dn: uid=tom,ou=people,dc=hadoop,dc=apache,dc=org
> sn: tom
> cn: tom
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: dG9tLXBhc3N3b3Jk
> uid: tom# hadoop.apache.org
> dn: dc=hadoop,dc=apache,dc=org
> dc: hadoop
> o: Hadoop
> objectclass: top
> objectclass: organization
> objectclass: dcObject# groups, hadoop.apache.org
> dn: ou=groups,dc=hadoop,dc=apache,dc=org
> ou: groups
> objectclass: top
> objectclass: organizationalUnit
> description: generic groups branch# guest, people, hadoop.apache.org
> dn: uid=guest,ou=people,dc=hadoop,dc=apache,dc=org
> sn: User
> cn: Guest
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: Z3Vlc3QtcGFzc3dvcmQ=
> uid: guest# search result
> search: 2
> result: 0 Success# numResponses: 12
> # numEntries: 11 {code}
> h2. Amabri Server Setup-ldap
>
> {code:java}
> ambari-server setup-ldap {code}
>
> Response and provide the prompted details
> {code:java}
> [root@ora19codp32333 conf]# ambari-server setup-ldap
> Using python /usr/bin/python3.11
> Enter Ambari Admin login: admin
> Enter Ambari Admin password: <admin>Fetching LDAP configuration from DB.
> Primary LDAP Host (ora19codp32333.acceldata.ce):
> Primary LDAP Port (33389):
> Secondary LDAP Host <Optional>:
> Secondary LDAP Port <Optional>:
> Use SSL [true/false] (false):
> User object class (inetOrgPerson): person
> User ID attribute (uid):
> Group object class (groupOfNames):
> Group name attribute (cn):
> Group member attribute (member):
> Distinguished name attribute (dn):
> Search Base (dc=hadoop,dc=apache,dc=org):
> Referral method [follow/ignore] (follow):
> Bind anonymously [true/false] (false):
> Bind DN (uid=admin,ou=people,dc=hadoop,dc=apache,dc=org):
> Enter Bind DN Password: <admin-password>
> Confirm Bind DN Password: <admin-password>
> Handling behavior for username collisions [convert/skip] for LDAP sync
> (skip): convert
> Force lower-case user names [true/false] (false):true
> Results from LDAP are paginated when requested [true/false] (true):
> ====================
> Review Settings
> ====================
> Primary LDAP Host (ora19codp32333.acceldata.ce): ora19codp32333.acceldata.ce
> Primary LDAP Port (33389): 33389
> Use SSL [true/false] (false): false
> User object class (inetOrgPerson): person
> User ID attribute (uid): uid
> Group object class (groupOfNames): groupOfNames
> Group name attribute (cn): cn
> Group member attribute (member): member
> Distinguished name attribute (dn): dn
> Search Base (dc=hadoop,dc=apache,dc=org): dc=hadoop,dc=apache,dc=org
> Referral method [follow/ignore] (follow): follow
> Bind anonymously [true/false] (false): false
> Handling behavior for username collisions [convert/skip] for LDAP sync
> (skip): convert
> Force lower-case user names [true/false] (false): true
> Results from LDAP are paginated when requested [true/false] (true): true
> ambari.ldap.connectivity.bind_dn:
> uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> ambari.ldap.connectivity.bind_password: *****
> Save settings [y/n] (y)?
> Saving LDAP properties...
> Saving LDAP properties finished
> Ambari Server 'setup-ldap' completed successfully. {code}
> h2. Ambari Server - sync-ldap
>
> {code:java}
> [root@ora19codp32333 conf]# ambari-server sync-ldap --all
> Using python /usr/bin/python3.11
> Syncing with LDAP...
> Enter Ambari Admin login: admin
> Enter Ambari Admin password:Fetching LDAP configuration from DB.
> Syncing all...ERROR: Exiting with exit code 1.
> REASON: Caught exception running LDAP sync. Exception [EclipseLink-4002]
> (Eclipse Persistence Services - 2.6.2.v20151217-774c696):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or
> view does not existError Code: 942
> Call: SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> Query: ReadAllQuery(referenceClass=GroupEntity sql="SELECT group_id,
> group_name, group_type, ldap_group, principal_id FROM "groups"") {code}
>
>
> Logging into Oracle-19c
>
> {code:java}
> sqlplus ambari77/bigdata@//localhost:1521/MORALPDB {code}
>
>
>
> {code:java}
> SQL> DESC GROUPS;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> GROUP_ID NOT NULL NUMBER(10)
> PRINCIPAL_ID NOT NULL NUMBER(19)
> GROUP_NAME NOT NULL VARCHAR2(255)
> LDAP_GROUP NUMBER(10)
> GROUP_TYPE NOT NULL VARCHAR2(255) {code}
>
>
> Select the table
>
> {code:java}
> select * from GROUPS;
> -- no rows selected{code}
> Verify the failed query, which failed during the sync-ldap
>
>
> [This is the exact query failed during syncing]
>
> {code:java}
> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups" {code}
> Response
>
>
> {code:java}
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> 2 ;
> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist {code}
>
>
>
> Lets try other variants of groups to make it work from oracle-19c+
> The below combination of queries works fine.
>
> {code:java}
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "GROUPS";
> no rows selected
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> groups;
> no rows selected
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> GROUPS;
> no rows selected {code}
>
>
> If we execute the same queries in {*}MySQL-8.x+{*}, here are the results.
>
> {code:java}
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "GROUPS";
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '"GROUPS"' at line 1
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> groups;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'groups' at line 1
>
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> GROUPS;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'GROUPS' at line 1{code}
>
>
> And one more thing is that if we use directtly table name as groups, it fails
> saying as its a *reserved key.*
>
> {code:java}
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id
> FROM groups;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'groups' at line 1 {code}
>
>
> +*Recommendation*+
> I would like to recommend a suggestion, which will be compatible to both
> MySQL >= 8.x and Oracle >= 19.x
>
> Soon I am going to open a PR, which will address this fix.
> With the changes, added in the PR, able to work with the both MySQL and
> Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
