|
1
21
22 package org.apache.derby.impl.tools.dblook;
23
24 import java.sql.Connection  ;
25 import java.sql.Statement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28
29 import java.util.StringTokenizer ;
30
31 import org.apache.derby.tools.dblook;
32
33 public class DB_GrantRevoke {
34
35
40 public static void doAuthorizations(Connection conn)
41 throws SQLException {
42
43 Statement stmt = conn.createStatement();
45 ResultSet rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, SELECTPRIV, " +
46 "DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV FROM " +
47 "SYS.SYSTABLEPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = " +
48 "S.SCHEMAID AND T.TABLEID = P.TABLEID");
49 generateTablePrivs(rs);
50
51 rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, TYPE, COLUMNS FROM " +
53 "SYS.SYSCOLPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = " +
54 "S.SCHEMAID AND T.TABLEID = P.TABLEID");
55 generateColumnPrivs(rs, conn);
56
57 rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, ALIAS, ALIASTYPE FROM " +
59 "SYS.SYSROUTINEPERMS P, SYS.SYSALIASES A, SYS.SYSSCHEMAS S WHERE A.SCHEMAID = " +
60 "S.SCHEMAID AND P.ALIASID = A.ALIASID");
61 generateRoutinePrivs(rs);
62
63 rs.close();
64 stmt.close();
65 return;
66
67 }
68
69
74 private static void generateTablePrivs(ResultSet rs)
75 throws SQLException
76 {
77 boolean firstTime = true;
78 while (rs.next()) {
79
80 if (firstTime) {
81 Logs.reportString("----------------------------------------------");
82 Logs.reportMessage( "DBLOOK_TablePrivHeader");
83 Logs.reportString("----------------------------------------------\n");
84 }
85
86 String authName = rs.getString(1);
87 String schemaName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(2)));
88 String tableName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(3)));
89 String fullName = schemaName + "." + tableName;
90
91 if (dblook.isIgnorableSchema(schemaName))
92 continue;
93
94 Logs.writeToNewDDL(tablePrivStatement(rs, fullName, authName));
95 Logs.writeStmtEndToNewDDL();
96 Logs.writeNewlineToNewDDL();
97 firstTime = false;
98 }
99 }
100
101 private static String separatorStr(boolean addSeparator)
102 {
103 return (addSeparator) ? ", " : "";
104 }
105
106
113 private static String tablePrivStatement(ResultSet rs, String fullName, String authName)
114 throws SQLException
115 {
116 boolean addSeparator = false;
117 StringBuffer grantStmt = new StringBuffer ("GRANT ");
118
119 if (rs.getString(4).toUpperCase().equals("Y"))
120 {
121 grantStmt.append("SELECT");
122 addSeparator = true;
123 }
124
125 if (rs.getString(5).toUpperCase().equals("Y"))
126 {
127 grantStmt.append(separatorStr(addSeparator)+ "DELETE");
128 addSeparator = true;
129 }
130
131 if (rs.getString(6).toUpperCase().equals("Y"))
132 {
133 grantStmt.append(separatorStr(addSeparator)+ "INSERT");
134 addSeparator = true;
135 }
136
137 if (rs.getString(7).toUpperCase().equals("Y"))
138 {
139 grantStmt.append(separatorStr(addSeparator)+ "UPDATE");
140 addSeparator = true;
141 }
142
143 if (rs.getString(8).toUpperCase().equals("Y"))
144 {
145 grantStmt.append(separatorStr(addSeparator)+ "REFERENCES");
146 addSeparator = true;
147 }
148
149 if (rs.getString(9).toUpperCase().equals("Y"))
150 {
151 grantStmt.append(separatorStr(addSeparator)+ "TRIGGER");
152 addSeparator = true;
153 }
154
155 grantStmt.append(" ON " + fullName + " TO " + authName);
156
157 return grantStmt.toString();
158 }
159
160
166
167 private static void generateColumnPrivs(ResultSet rs, Connection conn)
168 throws SQLException
169 {
170 boolean firstTime = true;
171 while (rs.next()) {
172 if (firstTime) {
173 Logs.reportString("----------------------------------------------");
174 Logs.reportMessage( "DBLOOK_ColumnPrivHeader");
175 Logs.reportString("----------------------------------------------\n");
176 }
177
178 String authName = rs.getString(1);
179 String schemaName = dblook.expandDoubleQuotes(rs.getString(2));
180 String tableName = dblook.expandDoubleQuotes(rs.getString(3));
181
182 if (dblook.isIgnorableSchema(schemaName))
183 continue;
184
185 Statement stmtCols = conn.createStatement();
187 String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME " +
188 "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " +
189 "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "+
190 "and T.TABLENAME = '"+tableName+"' AND SCHEMANAME = '"+schemaName +
191 "' ORDER BY COLUMNNUMBER";
192
193 ResultSet rsCols= stmtCols.executeQuery(queryCols);
194 String fullName = dblook.addQuotes(schemaName) + "." + dblook.addQuotes(tableName);
195
196 Logs.writeToNewDDL(columnPrivStatement(rs, fullName, authName, rsCols));
197 Logs.writeStmtEndToNewDDL();
198 Logs.writeNewlineToNewDDL();
199 firstTime = false;
200 }
201 }
202
203 private static String privTypeToString(String privType)
204 {
205 if (privType.equals("S"))
206 return "SELECT";
207 else if (privType.equals("R"))
208 return "REFERENCES";
209 else if (privType.equals("U"))
210 return "UPDATE";
211
212 return "";
214 }
215
216
222
223 private static String mapColumnsToNames(String columns, ResultSet rsCols)
224 throws SQLException
225 {
226 StringBuffer colNames = new StringBuffer ();
227 rsCols.next();
228 int curColumn = 1;
229 boolean addSeparator = false;
230
231 StringTokenizer st = new StringTokenizer (columns, " ,{}");
233 while (st.hasMoreTokens())
234 {
235 int colNum = Integer.parseInt(st.nextToken());
236 while (colNum+1 > curColumn)
237 {
238 rsCols.next();
239 curColumn = rsCols.getInt(1);
240 }
241 colNames.append(separatorStr(addSeparator));
242 colNames.append(rsCols.getString(2));
243 addSeparator = true;
244 }
245
246 return colNames.toString();
247 }
248
249
256
257 private static String columnPrivStatement(ResultSet rs, String fullName,
258 String authName, ResultSet rsCols) throws SQLException
259 {
260 StringBuffer grantStmt = new StringBuffer ("GRANT ");
261
262 String privType = rs.getString(4).toUpperCase();
263 String columns = rs.getString(5);
264 grantStmt.append(privTypeToString(privType));
265 grantStmt.append("(");
266 grantStmt.append(mapColumnsToNames(columns, rsCols));
267 grantStmt.append(")");
268 grantStmt.append(" TO ");
269 grantStmt.append(authName);
270
271 return grantStmt.toString();
272 }
273
274
279 public static void generateRoutinePrivs(ResultSet rs) throws SQLException
280 {
281 boolean firstTime = true;
282 while (rs.next()) {
283 String authName = rs.getString(1);
284 String schemaName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(2)));
285 String aliasName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(3)));
286 String fullName = schemaName + "." + aliasName;
287 String aliasType = rs.getString(4);
288
289 if (dblook.isIgnorableSchema(schemaName))
290 continue;
291
292 if (schemaName.equals("\"SYSCS_UTIL\""))
294 continue;
295
296 if (firstTime) {
297 Logs.reportString("----------------------------------------------");
298 Logs.reportMessage("DBLOOK_RoutinePrivHeader");
299 Logs.reportString("----------------------------------------------\n");
300 }
301
302 Logs.writeToNewDDL(routinePrivStatement(fullName, authName, aliasType));
303 Logs.writeStmtEndToNewDDL();
304 Logs.writeNewlineToNewDDL();
305 firstTime = false;
306 }
307 }
308
309 private static String routinePrivStatement(String fullName, String authName, String aliasType)
310 throws SQLException
311 {
312 boolean addSeparator = false;
313 StringBuffer grantStmt = new StringBuffer ("GRANT EXECUTE ON ");
314
315 grantStmt.append((aliasType.equals("P")) ? "PROCEDURE " : "FUNCTION ");
316 grantStmt.append(fullName);
317 grantStmt.append(" TO ");
318 grantStmt.append(authName);
319
320 return grantStmt.toString();
321 }
322 }
323 |
Popular Tags |
Java API By Example, From Geeks To Geeks.