Description of problem: Attempting to delete the original Satellite user (first user created) via web UI fails & results in Internal Server Error (ISE) and email traceback that prevents the user from being deleted. Following stacktrace is seen in catalina.out: ... 2009-11-17 14:26:42,040 [TP-Processor2] WARN org.apache.struts.action.RequestProcessor - Unhandled Exception thrown: class com.redhat.rhn.common.db.WrappedSQLException 2009-11-17 14:26:42,042 [TP-Processor2] ERROR com.redhat.rhn.frontend.servlets.SessionFilter - Error during transaction. Rolling back javax.servlet.ServletException: ORA-20255: (cannot_delete_user) - The specified user may not be deleted. ORA-06512: at "RHNSAT.RHN_EXCEPTION", line 23 ORA-06512: at "RHNSAT.RHN_ORG", line 203 ORA-06512: at line 2 ... Caused by: com.redhat.rhn.common.db.WrappedSQLException: ORA-20255: (cannot_delete_user) - The specified user may not be deleted. ORA-06512: at "RHNSAT.RHN_EXCEPTION", line 23 ORA-06512: at "RHNSAT.RHN_ORG", line 203 ORA-06512: at line 2 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) ... at com.redhat.rhn.common.db.datasource.CallableMode.execute(CallableMode.java:34) at com.redhat.rhn.manager.user.UserManager.deleteUser(UserManager.java:469) at com.redhat.rhn.frontend.action.user.DeleteUserAction.execute(DeleteUserAction.java:89) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) ... ... 40 more Caused by: java.sql.SQLException: ORA-20255: (cannot_delete_user) - The specified user may not be deleted. ORA-06512: at "RHNSAT.RHN_EXCEPTION", line 23 ORA-06512: at "RHNSAT.RHN_ORG", line 203 ORA-06512: at line 2 ... 57 more 2009-11-17 14:26:42,130 [TP-Processor2] ERROR org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/rhn].[action] - Servlet.service() for servlet action threw exception java.sql.SQLException: ORA-20255: (cannot_delete_user) - The specified user may not be deleted. ORA-06512: at "RHNSAT.RHN_EXCEPTION", line 23 ORA-06512: at "RHNSAT.RHN_ORG", line 203 ORA-06512: at line 2 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) ... Version-Release number of selected component (if applicable): Red Hat Network (RHN) Satellite 5.3.0 How reproducible: Always. Steps to Reproduce: 1. Create multiple users on a 5.3 Satellite. 2. Make each user a sat admin and org admin. 3. Login as a second user, strip the original user of all admin rights. 4. Attempt to delete the original user (first user created at Satellite install time). Actual results: The WebUI displays an ISE and a traceback is sent to the email address of the Satellite, see catalina.out excerpt above. Expected results: Either user is deleted, or a proper error message displayed in UI as to why the user cannot be deleted. Additional info:
Looking at the delete_user procedure and running through it by hand, the error is thrown near the end at the catch all/other exceptions - ... update rhnRegToken set user_id = nvl(other_org_admin, other_user_id) where org_id = our_org_id and user_id = user_id_in; begin delete from web_contact where id = user_id_in; exception when others then rhn_exception.raise_exception('cannot_delete_user'); end; ... where "delete from web_contact" violates foreign key constraints. For example - see cases (a) and (b) below - if there're config files updated by the user (a), and definitely application install session since this is the user used during satellite installation (b). After setting these user_id to the next org_admin's id, deletion was successful: (a) SQL> delete from web_contact where id = 1; delete from web_contact where id = 1 * ERROR at line 1: ORA-02292: integrity constraint (RHNSAT.RHN_CONFREVISION_CID_FK) violated - child record found This is due to rhnConfigRevision having constraint rhn_confrevision_cid_fk on changed_by_id column: changed_by_id number default null constraint rhn_confrevision_cid_fk references web_contact(id) SQL> select id, revision, config_file_id, changed_by_id from rhnConfigRevision where changed_by_id = 1; ID REVISION CONFIG_FILE_ID CHANGED_BY_ID ---------- ---------- -------------- ------------- 62 1 62 1 21 1 21 1 41 1 41 1 61 1 61 1 SQL> update rhnConfigRevision set changed_by_id = 3 where changed_by_id = 1; 4 rows updated. (b) SQL> delete from web_contact where id = 1; delete from web_contact where id = 1 * ERROR at line 1: ORA-02292: integrity constraint (RHNSAT.RHN_APPINST_SESSION_UID_FK) violated - child record found rhnAppInstallSession user_id number constraint rhn_appinst_session_uid_nn not null constraint rhn_appinst_session_uid_fk references web_contact(id), SQL> select id, instance_id from rhnAppInstallSession where user_id = 1; ID INSTANCE_ID ---------- ----------- 1 1 2 1 SQL> update rhnAppInstallSession set user_id = 3 where user_id = 1; 2 rows updated. SQL> delete from web_contact where id = 1; 1 row deleted. c) there are probably others. So the proposed fix is: option 1: If the user being deleted is an org admin, make sure there's at least another org admin for the org. Display an error message in UI where it says you cannot delete last org admin for an org. (Similar to the last satellite admin - bug 508789); option 2: Do not require other org admin's exist at time of deletion, but add more checks to make sure fk constraints are not violated. Also, instead of throwing cannot_delete_user for all exceptions, display a nice message on why the user cannot be deleted. If that's not possible, at least rethrow the exception so we know which constraint is being violated without having to go throw manual SQL testing.
(In reply to comment #1) > c) there are probably others. So the proposed fix is: > > option 1: If the user being deleted is an org admin, make sure there's at least > another org admin for the org. Display an error message in UI where it says you > cannot delete last org admin for an org. (Similar to the last satellite admin > - bug 508789); > > option 2: Do not require other org admin's exist at time of deletion, but add > more checks to make sure fk constraints are not violated. Also, instead of > throwing cannot_delete_user for all exceptions, display a nice message on why > the user cannot be deleted. If that's not possible, at least rethrow the > exception so we know which constraint is being violated without having to go > throw manual SQL testing. Refined bug summary and proposed fix after discussing with Justin: This issue could occur with other org admins as well not just the original user (user_id = 1), and the fix should include - 1. a more graceful and detailed error, i.e., on the deletion web ui display "User can't be deleted because of ...". 2. allow deletion of any org admin (as long as it's not the only one in the org). This may be a bit tricky because of all the data that can be associated with a user that will need to be deleted or transferred to another admin, but it can be done. Will need thorough QA. (see #Comment 1)
Just as a side note: this bug does not occur depending on having a certain (admin) role but merely on being the last user having changed a certain config file. To change a config file you've got to be either org admin or config admin but even if those roles are later withdrawn from that user, the user still can't be deleted until another user changed all those files (or by manipulating the database in the same kind). It clearly doesn't matter whether that user was the first in the satellite/org. So if you have an user that has no roles but config admin and that user creates/changes a file, this user can't be deleted anymore. If that user is downgraded to a normal user with no roles, he still can't be deleted. But as soon as the file is deleted or a different user changes it, the former user can be deleted.
(In reply to comment #6) > So if you have an user that has no roles but config admin and that user > creates/changes a file, this user can't be deleted anymore. If that user is > downgraded to a normal user with no roles, he still can't be deleted. But as > soon as the file is deleted or a different user changes it, the former user > can be deleted. > This is correct - having someone else modify the same config files will change the changed_by_id in rhnConfigRevision table, so if that was the only thing blocking user deletion, this will resolve it as a workaround (thank you for that note). However, config files is only part of the problem, as seen in Bug Description: In addition to being the last modifier of config files, being the original Satellite user (first one created during Satellite installation) also has the installation session data associated with the user in rhnAppInstallSession table, which also prevents deletion unless we associated it with another user. This is an example where being the original satellite user (which is usually the Satellite admin thereafter) comes into play. So the general/true fix would be to check for at least these 2 tables for associations before deletion, as well as any/all other tables that could have similar associations to make sure it's taken care of before deleting the user. This includes picking the right user to "inherit" the associations - which in the case of installation sessions, probably the remaining site-wide Satellite administrator. Similarly for the config files.
(In reply to comment #7) > (In reply to comment #6) > However, config files is only part of the problem, as seen in Bug Description: > "Bug Description" should be "Comment 1".
It appears this bug also impacts deletion of organizations in addition to users, based on new report from customer: "- When trying to delete an organization it would fail with a traceback email. Exception: javax.servlet.ServletException: ORA-20255: (cannot_delete_user) - The specified user may not be deleted. ORA-06512: at "RHNSAT.RHN_EXCEPTION", line 23 ORA-06512: at "RHNSAT.RHN_ORG", line 210 2 ORA-06512: at "RHNSAT.RHN_ORG", line 43 ORA-06512: at line 1" Please make sure this is covered in bugfix and QA.
Fixed in Spacewalk upstream git by commit 7e3a7eb8297f3866dfc0bb86e99540e6d8b02a8a 538663 - unset references to to-be-deleted user in rhnConfigRevision
The 5.4.0 RHN Satellite and RHN Proxy release has occurred. This issue has been resolved with this release. RHEA-2010:0801 - RHN Satellite Server 5.4.0 Upgrade https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10332 RHEA-2010:0803 - RHN Tools enhancement update https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10333 RHEA-2010:0802 - RHN Proxy Server 5.4.0 bug fix update https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10334 RHEA-2010:0800 - RHN Satellite Server 5.4.0 https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10335 Docs are available: http://docs.redhat.com/docs/en-US/Red_Hat_Network_Satellite/index.html Regards, Clifford