Script - Security change at low level

This is an administrator script that should be used only when it is necessary to propagate security changes under a folder that contains a significant number of nodes. These types of changes made from the UI can take a long time to propagate across the repository, and this script does it much faster.

Caution

Our recommendation is to use it when changes affect more than 100.000 nodes; otherwise, we recommend doing it from the UI.

Note

The security change starts with a folder UUID node and propagates across all its children. The logic is to remove existing grants for each node and replace them with new ones based only on roles. Note that you can build other types of logic. Use the example as a basis.

• The variable UUID is the folder Unique Identifier.
• The variable MAX_DEPTH sets how deep the script should advance into subfolder children (note that 0 indicates the UUID folder).
• The arrays roleName and roleGrant are pairs of role names and role grants to be added.
• The variable LOG_FILE_NAME is the name of the log file saved in the $TOMCAT_HOME folder.

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.openkm.bean.Permission;
import com.openkm.core.Config;

// PARAMETERS
String LOG_FILE_NAME = "Security";
int MAX_DEPTH = Integer.MAX_VALUE;
String folder_UUID = "162735a1-661c-4555-bf53-4e9115f38c62";
String[] roleName = {"ROLE_X", "ROLE_Y"};
int[] roleGrant = {Permission.READ, Permission.READ | Permission.WRITE};

public static class FileLogger {

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss,SSS";
    public static final String LEVEL_INFO = "INFO ";
    public static final String LEVEL_WARN = "WARN ";
    public static final String LEVEL_ERROR = "ERROR";

    public static void info(String baseName, String message) throws IOException {
        logWrite(baseName, LEVEL_INFO, message);
    }

    public static void logWrite(String baseName, String level, String message) throws IOException {
        Writer sLogger = new FileWriter(getLogFile(baseName), true);
        sLogger.write(getLogEntry(level, message));
        sLogger.flush();
        sLogger.close();
    }

    public static String getLogFile(String baseName) {
        String fileDate = new SimpleDateFormat("yyyyMMdd").format(new Date());
        return Config.HOME_DIR + File.separator + baseName + "_" + fileDate + ".log";
    }

    public static void info(String baseName, String message, String param1, String param2) throws IOException {
        logWrite(baseName, LEVEL_INFO, message, param1, param2);
    }

    public static void warm(String baseName, String message, String param1, String param2) throws IOException {
        logWrite(baseName, LEVEL_WARN, message, param1, param2);
    }

    public static void error(String baseName, String message, String param1, String param2) throws IOException {
        logWrite(baseName, LEVEL_ERROR, message, param1, param2);
    }

    public static void logWrite(String baseName, String level, String message, String param1, String param2)
            throws IOException {
        Writer sLogger = new FileWriter(getLogFile(baseName), true);
        sLogger.write(getLogEntry(level, message, param1, param2));
        sLogger.flush();
        sLogger.close();
    }

    public static String getLogEntry(String level, String message) {
        StringBuilder sb = new StringBuilder();
        sb.append(new SimpleDateFormat(DATE_FORMAT).format(new Date()));
        sb.append(" ");
        sb.append(level);
        sb.append(" ");
        sb.append(message);
        sb.append("\n");
        return sb.toString();
    }

    public static String getLogEntry(String level, String message, String param1, String param2) {
        Object[] params = {param1, param2};
        StringBuilder sb = new StringBuilder();
        sb.append(new SimpleDateFormat(DATE_FORMAT).format(new Date()));
        sb.append(" ");
        sb.append(level);
        sb.append(" ");
        sb.append(MessageFormat.format(message, params));
        sb.append("\n");
        return sb.toString();
    }
}

public void updateSecurity(Connection con, String UUID, int level) throws SQLException, IOException {
    FileLogger.info(LOG_FILE_NAME, "updateSecurity level:''{0}'' with UUID:''{1}''", String.valueOf(level), UUID);
    PreparedStatement ps = null;
    ResultSet rs = null;

    if (level == 0) {
        setParentNodeSecurity(con, UUID);
        removeAllChildrenNodeSecurity(con, UUID);
        setAllChildrenNodeSecurity(con, UUID);
    }

    if (level < MAX_DEPTH) {
        String sqlfindFolderChildren = "SELECT NBS_UUID FROM OKM_NODE_BASE WHERE NBS_PARENT=? and NBS_UUID in (SELECT NBS_UUID FROM OKM_NODE_FOLDER)";

        try {
            ps = con.prepareStatement(sqlfindFolderChildren);
            ps.setString(1, UUID);
            rs = ps.executeQuery();

            while (rs.next()) {
                String childUUID = rs.getString("NBS_UUID");
                removeAllChildrenNodeSecurity(con, childUUID);
                setAllChildrenNodeSecurity(con, childUUID);
                updateSecurity(con, childUUID, level + 1);
            }
        } catch (SQLException e) {
            FileLogger.error(LOG_FILE_NAME, "updateSecurity with UUID:''{0}'' con error ''{1}''", UUID, e.getMessage());
        } finally {
            ps.close();
            rs.close();
        }
    }
}

public void removeAllChildrenNodeSecurity(Connection con, String UUID) throws SQLException, IOException {
    PreparedStatement ps = null;
    String deleteChildrenNodeRoles = "DELETE FROM OKM_NODE_ROLE_PERMISSION where NRP_NODE in (SELECT NBS_UUID FROM OKM_NODE_BASE WHERE NBS_PARENT=?)";
    String deleteChildrenNodeUsers = "DELETE FROM OKM_NODE_USER_PERMISSION where NUP_NODE in (SELECT NBS_UUID FROM OKM_NODE_BASE WHERE NBS_PARENT=?)";

    try {
        ps = con.prepareStatement(deleteChildrenNodeRoles);
        ps.setString(1, UUID);
        ps.executeUpdate();
        ps = con.prepareStatement(deleteChildrenNodeUsers);
        ps.setString(1, UUID);
        ps.executeUpdate();
        con.commit();
    } catch (SQLException e) {
        FileLogger.error(LOG_FILE_NAME, "removeAllChildrenNodeSecurity with UUID:''{0}'' con error ''{1}''", UUID,
                e.getMessage());
    } finally {
        ps.close();
    }
}

public void setAllChildrenNodeSecurity(Connection con, String UUID) throws SQLException, IOException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sqlfindAllChildren = "SELECT NBS_UUID FROM OKM_NODE_BASE WHERE NBS_PARENT=?";
    String insertRoleSecurityChildren = "INSERT INTO OKM_NODE_ROLE_PERMISSION (NRP_NODE,NRP_PERMISSION, NRP_ROLE) VALUES (?,?,?)";

    try {
        ps = con.prepareStatement(sqlfindAllChildren);
        ps.setString(1, UUID);
        rs = ps.executeQuery();
        ps = con.prepareStatement(insertRoleSecurityChildren);

        while (rs.next()) {
            String childUUID = rs.getString("NBS_UUID");

            for (int i = 0; i < roleName.length; i++) {
                ps.setString(1, childUUID);
                ps.setInt(2, roleGrant[i]);
                ps.setString(3, roleName[i]);
                ps.addBatch();
            }
        }

        ps.executeBatch();
        con.commit();
    } catch (SQLException e) {
        FileLogger.error(LOG_FILE_NAME, "removeAllChildrenNodeSecurity with UUID:''{0}'' con error ''{1}''", UUID,
                e.getMessage());
    } finally {
        ps.close();
        rs.close();
    }
}

public void setParentNodeSecurity(Connection con, String UUID) throws SQLException, IOException {
    PreparedStatement ps = null;
    String deleteChildrenNodeRoles = "DELETE FROM OKM_NODE_ROLE_PERMISSION where NRP_NODE =?";
    String deleteChildrenNodeUsers = "DELETE FROM OKM_NODE_USER_PERMISSION where NUP_NODE =?";
    String insertRoleSecurityChildren = "INSERT INTO OKM_NODE_ROLE_PERMISSION (NRP_NODE,NRP_PERMISSION, NRP_ROLE) VALUES (?,?,?)";

    try {
        ps = con.prepareStatement(deleteChildrenNodeRoles);
        ps.setString(1, UUID);
        ps.executeUpdate();
        ps = con.prepareStatement(deleteChildrenNodeUsers);
        ps.setString(1, UUID);
        ps.executeUpdate();
        ps = con.prepareStatement(insertRoleSecurityChildren);

        for (int i = 0; i < roleName.length; i++) {
            ps.setString(1, UUID);
            ps.setInt(2, roleGrant[i]);
            ps.setString(3, roleName[i]);
            ps.addBatch();
        }

        ps.executeBatch();
        con.commit();
    } catch (SQLException e) {
        FileLogger.error(LOG_FILE_NAME, "setParentNodeSecurity with UUID:''{0}'' con error ''{1}''", UUID, e.getMessage());
    } finally {
        ps.close();
    }
}


try {
    FileLogger.info(LOG_FILE_NAME, "**** Security change started ****");
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con = null;
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/okmdb", "openkm", "*secret*");
    con.setAutoCommit(false);
    updateSecurity(con, folder_UUID, 0);
    con.close();
    FileLogger.info(LOG_FILE_NAME, "**** Security change ended ****");
} catch (Exception e) {
    print(e.getMessage());
}

Caution

After the script execution, we must reindex Lucene indexes because the security changes were made at the database level, and this is necessary to maintain integrity with the search engine.

Images

Initial security status

UUID of the folder

Execution

Change the script parameters to your own.

Log

Consider taking a look at the log file to see the progress of the process and to check for errors.

Rebuild Lucene indexes

Because the security changes were made at the database level, it is necessary to reindex the Lucene indexes to maintain integrity with the search engine.

Security changes applied