1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.krad.dao.jdbc;
17
18 import java.sql.Connection;
19 import java.sql.ResultSet;
20 import java.sql.SQLException;
21 import java.sql.Statement;
22 import java.util.HashMap;
23 import java.util.List;
24 import java.util.Map;
25
26 import org.apache.commons.lang.StringUtils;
27 import org.apache.log4j.Logger;
28 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
29 import org.kuali.rice.devtools.pdle.PostDataLoadEncryptionDao;
30 import org.springframework.dao.DataAccessException;
31 import org.springframework.jdbc.core.ConnectionCallback;
32 import org.springframework.jdbc.core.RowMapper;
33
34 public class PostDataLoadEncryptionDaoJdbc extends PlatformAwareDaoBaseJdbc implements PostDataLoadEncryptionDao {
35 private static final Logger LOG = Logger.getLogger(PostDataLoadEncryptionDaoJdbc.class);
36
37 protected static final String AND_SEPARATOR = " AND ";
38 protected static final String COMMA_SEPARATOR = ", ";
39 protected static final String BACKUP_TABLE_EXTENSION = "_bak";
40 protected static final String BACKUP_TABLE_ENCRYPT_IND = "ENCRYPT_IND";
41
42 void executeSql(String sql) {
43 LOG.info("Executing sql: " + sql);
44 getJdbcTemplate().execute(sql);
45 }
46
47 public void createBackupTable(String tableName) {
48 executeSql(getDbPlatform().getCreateTableFromTableSql(tableName + "_bak", tableName));
49 }
50
51 public void truncateTable(String tableName) {
52 executeSql(getDbPlatform().getTruncateTableSql(tableName));
53 }
54
55 public void restoreTableFromBackup(String tableName) {
56 truncateTable(tableName);
57 executeSql(getDbPlatform().getInsertDataFromTableSql(tableName, tableName + "_bak"));
58 }
59
60 public void dropBackupTable(String tableName) {
61 executeSql(getDbPlatform().getDropTableSql(tableName + "_bak"));
62 }
63
64 public boolean doesBackupTableExist(String tableName){
65 try{
66 Object tableNameObj = getJdbcTemplate().queryForObject(
67 "SELECT count(*) FROM " + tableName + BACKUP_TABLE_EXTENSION + " WHERE 0=1", Integer.class);
68 return true;
69 } catch(Exception ex){
70 return false;
71 }
72 }
73
74 public void addEncryptionIndicatorToBackupTable(String tableName){
75 executeSql(new StringBuffer().append("ALTER TABLE ")
76 .append(tableName + BACKUP_TABLE_EXTENSION)
77 .append(" ADD ")
78 .append(BACKUP_TABLE_ENCRYPT_IND)
79 .append(" VARCHAR2(1)").toString());
80 }
81
82 public void dropEncryptionIndicatorFromBackupTable(String tableName){
83 executeSql(new StringBuffer().append("ALTER TABLE ")
84 .append(tableName + BACKUP_TABLE_EXTENSION)
85 .append(" DROP COLUMN ")
86 .append(BACKUP_TABLE_ENCRYPT_IND).toString());
87 }
88
89 public List<Map<String, String>> retrieveUnencryptedColumnValuesFromBackupTable(String tableName, final List<String> columnNames, int numberOfRowsToCommitAfter) {
90 return getJdbcTemplate().query(
91 getSelectBackupTableColumnsSql(tableName, columnNames, numberOfRowsToCommitAfter),
92 new RowMapper() {
93 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
94 Map<String, String> columnValuesMap = new HashMap<String, String>();
95 for(String columnName: columnNames){
96 columnValuesMap.put(columnName, rs.getString(columnName));
97 }
98 return columnValuesMap;
99 }
100 });
101 }
102
103 public void updateColumnValuesInBackupTable(
104 String tableName, Map<String, List<String>> columnNameOldNewValuesMap) {
105 if(columnNameOldNewValuesMap==null || columnNameOldNewValuesMap.size()<1)
106 return;
107 executeSql(getUpdateBackupTableColumnsSql(tableName, columnNameOldNewValuesMap));
108 }
109
110 public String getUpdateBackupTableColumnsSql(
111 String tableName, Map<String, List<String>> columnNameOldNewValuesMap){
112 tableName = tableName + BACKUP_TABLE_EXTENSION;
113 StringBuffer columnsNamesNewValuesBuf = new StringBuffer();
114 List<String> columnOldNewValueList;
115 String columnNewValue;
116 for(String columnName: columnNameOldNewValuesMap.keySet()){
117 columnOldNewValueList = columnNameOldNewValuesMap.get(columnName);
118 columnNewValue = (columnOldNewValueList!=null&&columnOldNewValueList.size()>1)?columnOldNewValueList.get(ENCRYPTED_VALUE_INDEX):"";
119 columnsNamesNewValuesBuf.append(columnName)
120 .append("='")
121 .append(columnNewValue)
122 .append("'")
123 .append(COMMA_SEPARATOR);
124 }
125 columnsNamesNewValuesBuf.append(BACKUP_TABLE_ENCRYPT_IND)
126 .append("='Y'");
127
128 StringBuffer columnsNamesOldValuesBuf = new StringBuffer();
129 String columnOldValue;
130 for(String columnName: columnNameOldNewValuesMap.keySet()){
131 columnOldNewValueList = columnNameOldNewValuesMap.get(columnName);
132 columnOldValue = (columnOldNewValueList!=null&&columnOldNewValueList.size()>0)?columnOldNewValueList.get(UNENCRYPTED_VALUE_INDEX):"";
133 if(StringUtils.isEmpty(columnOldValue) || columnOldValue.equalsIgnoreCase("null")){
134 columnsNamesOldValuesBuf.append(columnName)
135 .append(" IS NULL ")
136 .append(AND_SEPARATOR);
137 } else{
138 columnsNamesOldValuesBuf.append(columnName)
139 .append("='")
140 .append(columnOldValue)
141 .append("'")
142 .append(AND_SEPARATOR);
143 }
144 }
145 columnsNamesOldValuesBuf.append(BACKUP_TABLE_ENCRYPT_IND)
146 .append(" IS NULL ");
147
148 return new StringBuffer("UPDATE ")
149 .append(tableName)
150 .append(" SET ")
151 .append(columnsNamesNewValuesBuf)
152 .append(" WHERE ")
153 .append(columnsNamesOldValuesBuf)
154 .toString();
155 }
156
157 protected String getSelectBackupTableColumnsSql(String tableName, List<String> columnNames, int numberOfRowsToCommitAfter){
158 tableName = tableName + BACKUP_TABLE_EXTENSION;
159 StringBuffer columnsNamesBuf = new StringBuffer();
160 for(String columnName: columnNames){
161 columnsNamesBuf.append(columnName).append(COMMA_SEPARATOR);
162 }
163 String selectColumns = StringUtils.stripEnd(columnsNamesBuf.toString(), COMMA_SEPARATOR);
164 return new StringBuffer("SELECT ")
165 .append(selectColumns)
166 .append(" FROM ")
167 .append(tableName)
168 .append(" WHERE ")
169 .append(BACKUP_TABLE_ENCRYPT_IND)
170 .append(" IS NULL AND ROWNUM<=")
171 .append(numberOfRowsToCommitAfter).toString();
172 }
173
174 public boolean performEncryption(
175 final String tableName,
176 final List<Map<String, List<String>>> rowsToEncryptColumnNameOldNewValuesMap) throws Exception {
177
178 Boolean success = (Boolean) getJdbcTemplate().execute(new ConnectionCallback() {
179 public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
180 try{
181 conn.setAutoCommit(false);
182 Statement statement = conn.createStatement();
183 int counter = 0;
184 for (Map<String, List<String>> columnNameOldNewValuesMap: rowsToEncryptColumnNameOldNewValuesMap) {
185 statement.addBatch(getUpdateBackupTableColumnsSql(tableName, columnNameOldNewValuesMap));
186 counter++;
187 }
188 statement.executeBatch();
189 conn.commit();
190 LOG.info(new StringBuffer("Encrypted ").append(" attributes of table ").append(tableName));
191 }
192 catch (Exception e) {
193 LOG.error(new StringBuffer("Caught exception, while encrypting ").append(" attributes of table ").append(tableName), e);
194 conn.rollback();
195 return false;
196 }
197 return true;
198 }
199 });
200 return success;
201 }
202 }