001: /*
002:
003: Derby - Class org.apache.derby.impl.tools.dblook.DB_Alias
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to you under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derby.impl.tools.dblook;
023:
024: import java.sql.Connection;
025: import java.sql.Statement;
026: import java.sql.ResultSet;
027: import java.sql.SQLException;
028:
029: import java.util.StringTokenizer;
030:
031: import org.apache.derby.tools.dblook;
032:
033: public class DB_GrantRevoke {
034:
035: /** ************************************************
036: * Generate Grant & Revoke statements if sqlAuthorization is on
037: *
038: * @param conn Connection to use
039: */
040: public static void doAuthorizations(Connection conn)
041: throws SQLException {
042:
043: // First generate table privilege statements
044: Statement stmt = conn.createStatement();
045: ResultSet rs = stmt
046: .executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, SELECTPRIV, "
047: + "DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV FROM "
048: + "SYS.SYSTABLEPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = "
049: + "S.SCHEMAID AND T.TABLEID = P.TABLEID");
050: generateTablePrivs(rs);
051:
052: // Generate column privilege statements
053: rs = stmt
054: .executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, TYPE, COLUMNS FROM "
055: + "SYS.SYSCOLPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = "
056: + "S.SCHEMAID AND T.TABLEID = P.TABLEID");
057: generateColumnPrivs(rs, conn);
058:
059: // Generate routine privilege statements
060: rs = stmt
061: .executeQuery("SELECT GRANTEE, SCHEMANAME, ALIAS, ALIASTYPE FROM "
062: + "SYS.SYSROUTINEPERMS P, SYS.SYSALIASES A, SYS.SYSSCHEMAS S WHERE A.SCHEMAID = "
063: + "S.SCHEMAID AND P.ALIASID = A.ALIASID");
064: generateRoutinePrivs(rs);
065:
066: rs.close();
067: stmt.close();
068: return;
069:
070: }
071:
072: /** ************************************************
073: * Generate table privilege statements
074: *
075: * @param rs Result set holding required information
076: ****/
077: private static void generateTablePrivs(ResultSet rs)
078: throws SQLException {
079: boolean firstTime = true;
080: while (rs.next()) {
081:
082: if (firstTime) {
083: Logs
084: .reportString("----------------------------------------------");
085: Logs.reportMessage("DBLOOK_TablePrivHeader");
086: Logs
087: .reportString("----------------------------------------------\n");
088: }
089:
090: String authName = rs.getString(1);
091: String schemaName = dblook.addQuotes(dblook
092: .expandDoubleQuotes(rs.getString(2)));
093: String tableName = dblook.addQuotes(dblook
094: .expandDoubleQuotes(rs.getString(3)));
095: String fullName = schemaName + "." + tableName;
096:
097: if (dblook.isIgnorableSchema(schemaName))
098: continue;
099:
100: Logs.writeToNewDDL(tablePrivStatement(rs, fullName,
101: authName));
102: Logs.writeStmtEndToNewDDL();
103: Logs.writeNewlineToNewDDL();
104: firstTime = false;
105: }
106: }
107:
108: private static String separatorStr(boolean addSeparator) {
109: return (addSeparator) ? ", " : "";
110: }
111:
112: /** **************************************************
113: * Generate table privilege statement for the current row
114: *
115: * @param rs ResultSet holding tableperm information
116: * @param fullName Table's qualified name
117: * @param authName Authorization id for grant statement
118: */
119: private static String tablePrivStatement(ResultSet rs,
120: String fullName, String authName) throws SQLException {
121: boolean addSeparator = false;
122: StringBuffer grantStmt = new StringBuffer("GRANT ");
123:
124: if (rs.getString(4).toUpperCase().equals("Y")) {
125: grantStmt.append("SELECT");
126: addSeparator = true;
127: }
128:
129: if (rs.getString(5).toUpperCase().equals("Y")) {
130: grantStmt.append(separatorStr(addSeparator) + "DELETE");
131: addSeparator = true;
132: }
133:
134: if (rs.getString(6).toUpperCase().equals("Y")) {
135: grantStmt.append(separatorStr(addSeparator) + "INSERT");
136: addSeparator = true;
137: }
138:
139: if (rs.getString(7).toUpperCase().equals("Y")) {
140: grantStmt.append(separatorStr(addSeparator) + "UPDATE");
141: addSeparator = true;
142: }
143:
144: if (rs.getString(8).toUpperCase().equals("Y")) {
145: grantStmt.append(separatorStr(addSeparator) + "REFERENCES");
146: addSeparator = true;
147: }
148:
149: if (rs.getString(9).toUpperCase().equals("Y")) {
150: grantStmt.append(separatorStr(addSeparator) + "TRIGGER");
151: addSeparator = true;
152: }
153:
154: grantStmt.append(" ON " + fullName + " TO " + authName);
155:
156: return grantStmt.toString();
157: }
158:
159: /** ************************************************
160: * Generate column privilege statements
161: *
162: * @param rs ResultSet holding column privilege information
163: * @param conn Connection to use. Used to get another ResultSet
164: ****/
165:
166: private static void generateColumnPrivs(ResultSet rs,
167: Connection conn) throws SQLException {
168: boolean firstTime = true;
169: while (rs.next()) {
170: if (firstTime) {
171: Logs
172: .reportString("----------------------------------------------");
173: Logs.reportMessage("DBLOOK_ColumnPrivHeader");
174: Logs
175: .reportString("----------------------------------------------\n");
176: }
177:
178: String authName = rs.getString(1);
179: String schemaName = dblook.expandDoubleQuotes(rs
180: .getString(2));
181: String tableName = dblook.expandDoubleQuotes(rs
182: .getString(3));
183:
184: if (dblook.isIgnorableSchema(schemaName))
185: continue;
186:
187: // Create another resultSet to get column names
188: Statement stmtCols = conn.createStatement();
189: String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME "
190: + "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S "
191: + "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "
192: + "and T.TABLENAME = '" + tableName
193: + "' AND SCHEMANAME = '" + schemaName
194: + "' ORDER BY COLUMNNUMBER";
195:
196: ResultSet rsCols = stmtCols.executeQuery(queryCols);
197: String fullName = dblook.addQuotes(schemaName) + "."
198: + dblook.addQuotes(tableName);
199:
200: Logs.writeToNewDDL(columnPrivStatement(rs, fullName,
201: authName, rsCols));
202: Logs.writeStmtEndToNewDDL();
203: Logs.writeNewlineToNewDDL();
204: firstTime = false;
205: }
206: }
207:
208: private static String privTypeToString(String privType) {
209: if (privType.equals("S"))
210: return "SELECT";
211: else if (privType.equals("R"))
212: return "REFERENCES";
213: else if (privType.equals("U"))
214: return "UPDATE";
215:
216: // Should throw an exception?
217: return "";
218: }
219:
220: /** ************************************************
221: * Generate one column grant statement
222: *
223: * @param columns List of columns to grant required privs
224: * @param rsCols ResultSet for mapping column numbers to names
225: ****/
226:
227: private static String mapColumnsToNames(String columns,
228: ResultSet rsCols) throws SQLException {
229: StringBuffer colNames = new StringBuffer();
230: rsCols.next();
231: int curColumn = 1;
232: boolean addSeparator = false;
233:
234: // Strip out outer {} in addition to spaces and comma
235: StringTokenizer st = new StringTokenizer(columns, " ,{}");
236: while (st.hasMoreTokens()) {
237: int colNum = Integer.parseInt(st.nextToken());
238: while (colNum + 1 > curColumn) {
239: rsCols.next();
240: curColumn = rsCols.getInt(1);
241: }
242: colNames.append(separatorStr(addSeparator));
243: colNames.append(rsCols.getString(2));
244: addSeparator = true;
245: }
246:
247: return colNames.toString();
248: }
249:
250: /** ************************************************
251: *
252: * @param rs ResultSet with info for this GRANT statement
253: * @param fullName Full qualified name of the table
254: * @param authName Authorization name for this GRANT
255: * @param rsCols ResultSet for mapping column numbers to names
256: ****/
257:
258: private static String columnPrivStatement(ResultSet rs,
259: String fullName, String authName, ResultSet rsCols)
260: throws SQLException {
261: StringBuffer grantStmt = new StringBuffer("GRANT ");
262:
263: String privType = rs.getString(4).toUpperCase();
264: String columns = rs.getString(5);
265: grantStmt.append(privTypeToString(privType));
266: grantStmt.append("(");
267: grantStmt.append(mapColumnsToNames(columns, rsCols));
268: grantStmt.append(")");
269: grantStmt.append(" TO ");
270: grantStmt.append(authName);
271:
272: return grantStmt.toString();
273: }
274:
275: /** ************************************************
276: * Generate routine privilege statements
277: *
278: * @param rs ResultSet holding required information
279: ****/
280: public static void generateRoutinePrivs(ResultSet rs)
281: throws SQLException {
282: boolean firstTime = true;
283: while (rs.next()) {
284: String authName = rs.getString(1);
285: String schemaName = dblook.addQuotes(dblook
286: .expandDoubleQuotes(rs.getString(2)));
287: String aliasName = dblook.addQuotes(dblook
288: .expandDoubleQuotes(rs.getString(3)));
289: String fullName = schemaName + "." + aliasName;
290: String aliasType = rs.getString(4);
291:
292: if (dblook.isIgnorableSchema(schemaName))
293: continue;
294:
295: // Ignore SYSCS_UTIL privileges as all new databases automatically get them
296: if (schemaName.equals("\"SYSCS_UTIL\""))
297: continue;
298:
299: if (firstTime) {
300: Logs
301: .reportString("----------------------------------------------");
302: Logs.reportMessage("DBLOOK_RoutinePrivHeader");
303: Logs
304: .reportString("----------------------------------------------\n");
305: }
306:
307: Logs.writeToNewDDL(routinePrivStatement(fullName, authName,
308: aliasType));
309: Logs.writeStmtEndToNewDDL();
310: Logs.writeNewlineToNewDDL();
311: firstTime = false;
312: }
313: }
314:
315: private static String routinePrivStatement(String fullName,
316: String authName, String aliasType) throws SQLException {
317: boolean addSeparator = false;
318: StringBuffer grantStmt = new StringBuffer("GRANT EXECUTE ON ");
319:
320: grantStmt.append((aliasType.equals("P")) ? "PROCEDURE "
321: : "FUNCTION ");
322: grantStmt.append(fullName);
323: grantStmt.append(" TO ");
324: grantStmt.append(authName);
325:
326: return grantStmt.toString();
327: }
328: }
|