Difference between revisions of "Low level security change"

From OpenKM Documentation
Jump to: navigation, search
 
(9 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
That is an administrator script that should be used only when is needed propagate security changes under a folder wich have a significative amount of nodes. These kind of changes made from UI will need a lot of time to be propagated across repository and that utility does it much faster.
 
That is an administrator script that should be used only when is needed propagate security changes under a folder wich have a significative amount of nodes. These kind of changes made from UI will need a lot of time to be propagated across repository and that utility does it much faster.
  
−
{Note|Our recomendation is use when changes affects more than 100.000 nodes otherside we recommend do it from UI}
+
{{Note|Our recomendation is use when changes affects more than '''100.000 nodes''' otherside we recommend do it from UI}}
  
 
In these example the security change start with some folder UUID node and propagates across all childs. The logic is remove actual grants for each node and replace by newer based only on roles. Note that you can build other kind of logic take that example as the basis.
 
In these example the security change start with some folder UUID node and propagates across all childs. The logic is remove actual grants for each node and replace by newer based only on roles. Note that you can build other kind of logic take that example as the basis.
  
 
Description:
 
Description:
−
* '''UUID''' is the folder Unique Identifier
+
* '''UUID''' is the folder Unique Identifier.
−
* MAX_DEPTH indicates how much deep should advance the script in subfolders childs ( note than 0 indicates the UUID folder )
+
* '''MAX_DEPTH''' indicates how much deep should advance the script in subfolders childs ( note than 0 indicates the UUID folder ).
−
* roleName array and roleGrant array indicates de pair of roles and roles grants which will be added
+
* '''roleName''' and '''roleGrant''' arrays indicates de pair of roles and roles grants which will be added.
−
* LOG_FILE_NAME indicates the name of the log file which will be stored under $TOMCAT_HOME folder
+
* LOG_'''FILE'''_NAME indicate the name of the log file which will be stored under $TOMCAT_HOME folder.
 +
 
 +
<source lang="java">
 +
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 org.apache.commons.dbcp.BasicDataSource;
 +
 
 +
import com.openkm.bean.Permission;
 +
import com.openkm.core.Config;
 +
import com.openkm.dao.LegacyDAO;
 +
 
 +
// PARAMETERS
 +
String LOG_FILE_NAME = "Security";
 +
int MAX_DEPTH = Integer.MAX_VALUE;
 +
String folder_UUID = "86d931e1-339a-4b74-8b8d-6a01a40ca6ad";
 +
String[] roleName = {"ROLE_X","ROLE_Y"};
 +
int[] roleGrant = {Permission.READ, Permission.READ|Permission.WRITE};
 +
   
 +
class FileLogger {
 +
        static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss,SSS";
 +
        static String LEVEL_INFO  = "INFO ";
 +
        static String LEVEL_WARN  = "WARN ";
 +
        static String LEVEL_ERROR = "ERROR";
 +
       
 +
        Writer cLogger = null;
 +
       
 +
 
 +
        static void info(String baseName, String message) throws IOException {
 +
                logWrite(baseName, LEVEL_INFO, message);
 +
        }
 +
       
 +
        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();
 +
        }
 +
        static String getLogFile(String baseName) {
 +
                String fileDate = new SimpleDateFormat("yyyyMMdd").format(new Date());
 +
                return Config.HOME_DIR + File.separator + baseName + "_" + fileDate + ".log";
 +
        }
 +
       
 +
        static void info(String message) throws IOException {
 +
                cLogger.write(getLogEntry(LEVEL_INFO, message));
 +
                cLogger.flush();
 +
        }
 +
       
 +
        static void info(String baseName, String message, String param1, String param2) throws IOException {
 +
                logWrite(baseName, LEVEL_INFO, message, param1, param2);
 +
        }
 +
   
 +
        static void warm(String baseName, String message, String param1, String param2) throws IOException {
 +
                logWrite(baseName, LEVEL_WARN, message, param1, param2);
 +
        }
 +
       
 +
        static void error(String baseName, String message, String param1, String param2) throws IOException {
 +
                logWrite(baseName, LEVEL_ERROR, message, param1, param2);
 +
        }
 +
       
 +
        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();
 +
        }
 +
 
 +
        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();
 +
        }
 +
       
 +
        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();
 +
        }
 +
}
 +
 
 +
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 {
 +
                    LegacyDAO.close(ps);
 +
                    LegacyDAO.close(rs);
 +
            }
 +
    }
 +
}
 +
 
 +
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 {
 +
            LegacyDAO.close(ps);
 +
    }
 +
}
 +
 
 +
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 {
 +
            LegacyDAO.close(ps);
 +
            LegacyDAO.close(rs);
 +
    }
 +
}
 +
 
 +
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 {
 +
            LegacyDAO.close(ps);
 +
    }
 +
}
 +
 
 +
 
 +
FileLogger.info(LOG_FILE_NAME, "**** Security change started ****");
 +
Connection con = LegacyDAO.getConnection();
 +
con.setAutoCommit(false);
 +
updateSecurity(con, folder_UUID, 0);
 +
con.close();
 +
FileLogger.info(LOG_FILE_NAME, "**** Security change ended ****");
 +
 
 +
</source>
 +
 
 +
== Example ==
 +
'''Initial security status'''
 +
[[File:Okm_user_guide_371.png|800px|center]]
 +
 
 +
 
 +
'''Folder UUID'''
 +
[[File:Okm_user_guide_372.png|800px|center]]
 +
 
 +
 
 +
'''Execute Script'''
 +
Change script parameters by your owns and execute script.
 +
[[File:Okm_user_guide_373.png|800px|center]]
 +
 
 +
 
 +
Consider take a look at file log to see how advance and if has been some error.
 +
[[File:Okm_user_guide_374.png|800px|center]]
 +
 
 +
 
 +
'''Reindex lucene indexes'''
 +
Because have changed security at a row level is necessary for maintaining consistence integrity with search engine, reindex lucene indexes
 +
[[File:Okm_user_guide_375.png|800px|center]]
 +
 
 +
 
 +
[[File:Okm_user_guide_376.png|800px|center]]
 +
 
 +
 
 +
'''See the final result'''
 +
[[File:Okm_user_guide_377.png|800px|center]]
 +
 
 +
[[Category: Utilities]]

Latest revision as of 13:12, 15 March 2013

That is an administrator script that should be used only when is needed propagate security changes under a folder wich have a significative amount of nodes. These kind of changes made from UI will need a lot of time to be propagated across repository and that utility does it much faster.


Nota clasica.png Our recomendation is use when changes affects more than 100.000 nodes otherside we recommend do it from UI

In these example the security change start with some folder UUID node and propagates across all childs. The logic is remove actual grants for each node and replace by newer based only on roles. Note that you can build other kind of logic take that example as the basis.

Description:

  • UUID is the folder Unique Identifier.
  • MAX_DEPTH indicates how much deep should advance the script in subfolders childs ( note than 0 indicates the UUID folder ).
  • roleName and roleGrant arrays indicates de pair of roles and roles grants which will be added.
  • LOG_FILE_NAME indicate the name of the log file which will be stored 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 org.apache.commons.dbcp.BasicDataSource;

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

// PARAMETERS
String LOG_FILE_NAME = "Security";
int MAX_DEPTH = Integer.MAX_VALUE;
String folder_UUID = "86d931e1-339a-4b74-8b8d-6a01a40ca6ad";
String[] roleName = {"ROLE_X","ROLE_Y"};
int[] roleGrant	= {Permission.READ, Permission.READ|Permission.WRITE};
    
class FileLogger {
        static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss,SSS";
        static String LEVEL_INFO  = "INFO ";
        static String LEVEL_WARN  = "WARN ";
        static String LEVEL_ERROR = "ERROR";
        
        Writer cLogger = null;
        

        static void info(String baseName, String message) throws IOException {
                logWrite(baseName, LEVEL_INFO, message);
        }
        
        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();
        }
        static String getLogFile(String baseName) {
                String fileDate = new SimpleDateFormat("yyyyMMdd").format(new Date());
                return Config.HOME_DIR + File.separator + baseName + "_" + fileDate + ".log";
        }
        
        static void info(String message) throws IOException {
                cLogger.write(getLogEntry(LEVEL_INFO, message));
                cLogger.flush();
        }
        
        static void info(String baseName, String message, String param1, String param2) throws IOException {
                logWrite(baseName, LEVEL_INFO, message, param1, param2);
        }
    
        static void warm(String baseName, String message, String param1, String param2) throws IOException {
                logWrite(baseName, LEVEL_WARN, message, param1, param2);
        }
        
        static void error(String baseName, String message, String param1, String param2) throws IOException {
                logWrite(baseName, LEVEL_ERROR, message, param1, param2);
        }
        
        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();
        }

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

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 {
                    LegacyDAO.close(ps);
                    LegacyDAO.close(rs);
            }
    }
}

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 {
            LegacyDAO.close(ps);
    }
}

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 {
            LegacyDAO.close(ps);
            LegacyDAO.close(rs);
    }
}

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 {
            LegacyDAO.close(ps);
    }
}


FileLogger.info(LOG_FILE_NAME, "**** Security change started ****");
Connection con = LegacyDAO.getConnection();
con.setAutoCommit(false);
updateSecurity(con, folder_UUID, 0);
con.close();
FileLogger.info(LOG_FILE_NAME, "**** Security change ended ****");

Example

Initial security status

Okm user guide 371.png


Folder UUID

Okm user guide 372.png


Execute Script Change script parameters by your owns and execute script.

Okm user guide 373.png


Consider take a look at file log to see how advance and if has been some error.

Okm user guide 374.png


Reindex lucene indexes Because have changed security at a row level is necessary for maintaining consistence integrity with search engine, reindex lucene indexes

Okm user guide 375.png


Okm user guide 376.png


See the final result

Okm user guide 377.png
Retrieved from "https://www.openkm.com/wiki/index.php?title=Low_level_security_change&oldid=9117"