Script - Security change at low level

This is an administrator script that should be used only when to propagate security changes under a folder which have a significant number of nodes is needed. These type of changes made from UI can take a lot of time to be propagated across the repository and this script does it much faster.  

Caution

Our recommendation is to use it when changes affects more than 100.000 nodes otherwise we recommend do it from UI.

Note

The security change starts with some folder UUID node and propagates across all children. The logic is to remove actual grants for each node and replace by newer based only on roles. Note that you can build other type of logic. Take the example as a basis.

• The variable UUID is the folder Unique Identifier.
• The variable MAX_DEPTH sets how much deep should advance the script in subfolders children ( note than 0 indicates the UUID folder ).
• The variables roleName and roleGrant arrays are the pairs of role name and roles grants to be added.
• The variable LOG_FILE_NAME is the name of the log file saved under $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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.*;

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 = "8ab2d963-0ced-4673-9991-54dd3d8b36b5";
String[] roleName = {"ROLE_X","ROLE_Y"};
int[] roleGrant  = {Permission.READ, Permission.READ | Permission.WRITE};

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

    Writer cLogger = null;

    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 message) throws IOException {
        cLogger.write(getLogEntry(LEVEL_INFO, message));
        cLogger.flush();
    }

    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();
    }
}


FileLogger.info(LOG_FILE_NAME, "**** Security change started ****");
Class.forName("com.mysql.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 ****");

Caution

After the script execution we must reindex Lucene indexes because the security changes has been done at the database level and it is necessary for maintaining integrity with search engine.

Images

Initial security status

UUID of the folder

Execution

Change script parameters by your owns.

 

Log

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

Rebuild Lucene indexes

Caused by changes of the security at a database level, it is necessary for maintaining integrity with search engine, reindex Lucene indexes.

Security changed applied