001 /**
002 * Copyright 2005-2012 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.rice.krad.dao.jdbc;
017
018 import java.sql.Connection;
019 import java.sql.ResultSet;
020 import java.sql.SQLException;
021 import java.sql.Statement;
022 import java.util.HashMap;
023 import java.util.List;
024 import java.util.Map;
025
026 import org.apache.commons.lang.StringUtils;
027 import org.apache.log4j.Logger;
028 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
029 import org.kuali.rice.devtools.pdle.PostDataLoadEncryptionDao;
030 import org.springframework.dao.DataAccessException;
031 import org.springframework.jdbc.core.ConnectionCallback;
032 import org.springframework.jdbc.core.RowMapper;
033
034 public class PostDataLoadEncryptionDaoJdbc extends PlatformAwareDaoBaseJdbc implements PostDataLoadEncryptionDao {
035 private static final Logger LOG = Logger.getLogger(PostDataLoadEncryptionDaoJdbc.class);
036
037 protected static final String AND_SEPARATOR = " AND ";
038 protected static final String COMMA_SEPARATOR = ", ";
039 protected static final String BACKUP_TABLE_EXTENSION = "_bak";
040 protected static final String BACKUP_TABLE_ENCRYPT_IND = "ENCRYPT_IND";
041
042 void executeSql(String sql) {
043 LOG.info("Executing sql: " + sql);
044 getJdbcTemplate().execute(sql);
045 }
046
047 public void createBackupTable(String tableName) {
048 executeSql(getDbPlatform().getCreateTableFromTableSql(tableName + "_bak", tableName));
049 }
050
051 public void truncateTable(String tableName) {
052 executeSql(getDbPlatform().getTruncateTableSql(tableName));
053 }
054
055 public void restoreTableFromBackup(String tableName) {
056 truncateTable(tableName);
057 executeSql(getDbPlatform().getInsertDataFromTableSql(tableName, tableName + "_bak"));
058 }
059
060 public void dropBackupTable(String tableName) {
061 executeSql(getDbPlatform().getDropTableSql(tableName + "_bak"));
062 }
063
064 public boolean doesBackupTableExist(String tableName){
065 try{
066 Object tableNameObj = getJdbcTemplate().queryForObject(
067 "SELECT count(*) FROM " + tableName + BACKUP_TABLE_EXTENSION + " WHERE 0=1", Integer.class);
068 return true;
069 } catch(Exception ex){
070 return false;
071 }
072 }
073
074 public void addEncryptionIndicatorToBackupTable(String tableName){
075 executeSql(new StringBuffer().append("ALTER TABLE ")
076 .append(tableName + BACKUP_TABLE_EXTENSION)
077 .append(" ADD ")
078 .append(BACKUP_TABLE_ENCRYPT_IND)
079 .append(" VARCHAR2(1)").toString());
080 }
081
082 public void dropEncryptionIndicatorFromBackupTable(String tableName){
083 executeSql(new StringBuffer().append("ALTER TABLE ")
084 .append(tableName + BACKUP_TABLE_EXTENSION)
085 .append(" DROP COLUMN ")
086 .append(BACKUP_TABLE_ENCRYPT_IND).toString());
087 }
088
089 public List<Map<String, String>> retrieveUnencryptedColumnValuesFromBackupTable(String tableName, final List<String> columnNames, int numberOfRowsToCommitAfter) {
090 return getJdbcTemplate().query(
091 getSelectBackupTableColumnsSql(tableName, columnNames, numberOfRowsToCommitAfter),
092 new RowMapper() {
093 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
094 Map<String, String> columnValuesMap = new HashMap<String, String>();
095 for(String columnName: columnNames){
096 columnValuesMap.put(columnName, rs.getString(columnName));
097 }
098 return columnValuesMap;
099 }
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 }