Ability to delete unused roles

Maybe they need reference counting, since you shouldn't automatically create something you can't automatically delete?

I'd be happy with an admin page that lists all the roles, greys out the ones that are attached to users, and lets me delete the others.

With reference counting, I see it counting up as you add groups to servers, and then losing track of reality when servers are just shut down without first deleting the projects. Not saying it shouldn't be done, but I don't think it's going to be enough.

Can this be solved by adding a 'Delete' button on the new /security/role.action page?

Attaching utility jar for removing unused resources, roles and related data directly from the database.

Thanks, Deng! Can we get this into Redback svn so it's easier to work with?

How it works:

1. The resources from Archiva (repositories) or Continuum (project groups) or both will be retrieved.
2. The resources from SECURITY_RESOURCES table of users database will be retrieved and checked against the retrieved resources from Archiva and Continuum.
3. Constraints for cascade deletion of resources and roles will be set in the Redback users db tables.
4. The resource will be deleted if it doesn't exist in Archiva or/and Continuum resources list.
5. The added constraints from step 3 will be removed.

To use the jar file, execute the following command with the specified parameters below:

java \-jar redback\-roles\-cleanup\-${version}.jar [PARAMETERS]

Parameters:

1. Application name - specify archiva, continuum or all. If the application name set is "archiva", the utility class will read the specified Archiva config file to get the active repositories used. If the application name set is "continuum", the utility class will connect to the Continuum db using the db connection parameters and retrieve the project groups. Otherwise, if the application name is set to "all", the utility class will do both.
2. JDBC driver class name
3. Redback users database connection URL
4. Redback users database username
5. Redback users database password
6. Path to Archiva configuration file (if application is continuum, just set this value to "")
7. Continuum builds database connection URL
8. Continuum builds database username
9. Continuum builds database password
10. JDBC driver class name for Continuum (set this parameter only if Continuum database is using a different DBMS)

Notes:

• Make sure that you add the JDBC driver in the classpath either by setting -cp [JDBC driver jar] when executing the redback roles cleanup utility jar or if that doesn't work, put the JDBC driver jar in _$ {JAVA_HOME}

  /jre/lib/ext_

• When Archiva and Continuum are configured to share their own database, make sure to specify all for the application name parameter or else you might accidentally delete used resource roles from the other application.

Sample Usages:
1. Users database is used by Continuum only:

  
  java \-jar redback\-roles\-cleanup\-${version}.jar continuum com.mysql.jdbc.Driver 
          jdbc:mysql://localhost:3306/users usersdbUsername usersdbPassword "" 
          jdbc:mysql://localhost:3306/continuum continuumdbUsername continuumDbPassword

2. Users database is used by Archiva only:

 java \-jar redback\-roles\-cleanup\-${version}.jar archiva com.mysql.jdbc.Driver 
          jdbc:mysql://localhost:3306/users usersdbUsername usersdbPassword /path/to/archiva.xml

3. Archiva and Continuum are sharing one users database:

 java \-jar redback\-roles\-cleanup\-${version}.jar all com.mysql.jdbc.Driver 
          jdbc:mysql://localhost:3306/users usersdbUsername usersdbPassword /path/to/archiva.xml 
          jdbc:mysql://localhost:3306/continuum continuumdbUsername continuumDbPassword

Okay, I'll add this to the sandbox (http://svn.codehaus.org/redback/redback/trunk/sandbox/) for now.

Instead of setting ON DELETE CASCADE fk constraints, I changed it to deleting associated data step by step as it only worked with MySQL. Derby complains when there is already an existing fk constraint on the field. (-r853)

This is how it works now:

1. The resources from Archiva (repositories) or Continuum (project groups) or both will be retrieved.
2. The resources from SECURITY_RESOURCES table of users database will be retrieved and checked against the retrieved resources from Archiva and Continuum.
3. The resource/role will be deleted in the tables (in order) SECURITY_USERASSIGNMENT_ROLENAMES, SECURITY_ROLE_CHILDROLE_MAP, SECURITY_ROLE_PERMISSION_MAP, SECURITY_PERMISSIONS, SECURITY_RESOURCES and SECURITY_ROLES if it doesn't exist in Archiva or/and Continuum resources list.

Updated tool to cope with multiple Continuum databases in -r857 and also added documentation to the site in -r858.

Additional fix for redback-roles-cleanup committed to trunk -r882, with the following changes:

• also delete parent roles associated with the deleted resource
• added more output logs when deleting rows from the database