You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2006/03/20 08:03:25 UTC
svn commit: r387160 - in /db/derby/code/trunk/java/tools/org/apache/derby:
impl/tools/dblook/DB_GrantRevoke.java loc/toolsmessages.properties
tools/dblook.java
Author: bandaram
Date: Sun Mar 19 23:03:19 2006
New Revision: 387160
URL: http://svn.apache.org/viewcvs?rev=387160&view=rev
Log:
DERBY-464: Submit initial changes to make dblook report GRANT statements in sqlAuthorization mode.
Need to add or change existing test to use sqlAuthorization database.
Submitted by Satheesh Bandaram (satheesh@sourcery.org)
Added:
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java (with props)
Modified:
db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties
db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java
Added: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java?rev=387160&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java Sun Mar 19 23:03:19 2006
@@ -0,0 +1,321 @@
+/*
+
+ Derby - Class org.apache.derby.impl.tools.dblook.DB_Alias
+
+ Copyright 2006 The Apache Software Foundation or its licensors, as applicable.
+
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derby.impl.tools.dblook;
+
+import java.sql.Connection;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import java.util.StringTokenizer;
+
+import org.apache.derby.tools.dblook;
+
+public class DB_GrantRevoke {
+
+ /** ************************************************
+ * Generate Grant & Revoke statements if sqlAuthorization is on
+ *
+ * @param conn Connection to use
+ */
+ public static void doAuthorizations(Connection conn)
+ throws SQLException {
+
+ // First generate table privilege statements
+ Statement stmt = conn.createStatement();
+ ResultSet rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, SELECTPRIV, " +
+ "DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV FROM " +
+ "SYS.SYSTABLEPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = " +
+ "S.SCHEMAID AND T.TABLEID = P.TABLEID");
+ generateTablePrivs(rs);
+
+ // Generate column privilege statements
+ rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, TABLENAME, TYPE, COLUMNS FROM " +
+ "SYS.SYSCOLPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE T.SCHEMAID = " +
+ "S.SCHEMAID AND T.TABLEID = P.TABLEID");
+ generateColumnPrivs(rs, conn);
+
+ // Generate routine privilege statements
+ rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, ALIAS, ALIASTYPE FROM " +
+ "SYS.SYSROUTINEPERMS P, SYS.SYSALIASES A, SYS.SYSSCHEMAS S WHERE A.SCHEMAID = " +
+ "S.SCHEMAID AND P.ALIASID = A.ALIASID");
+ generateRoutinePrivs(rs);
+
+ rs.close();
+ stmt.close();
+ return;
+
+ }
+
+ /** ************************************************
+ * Generate table privilege statements
+ *
+ * @param rs Result set holding required information
+ ****/
+ private static void generateTablePrivs(ResultSet rs)
+ throws SQLException
+ {
+ boolean firstTime = true;
+ while (rs.next()) {
+
+ if (firstTime) {
+ Logs.reportString("----------------------------------------------");
+ Logs.reportMessage( "DBLOOK_TablePrivHeader");
+ Logs.reportString("----------------------------------------------\n");
+ }
+
+ String authName = rs.getString(1);
+ String schemaName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(2)));
+ String tableName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(3)));
+ String fullName = schemaName + "." + tableName;
+
+ if (dblook.isIgnorableSchema(schemaName))
+ continue;
+
+ Logs.writeToNewDDL(tablePrivStatement(rs, fullName, authName));
+ Logs.writeStmtEndToNewDDL();
+ Logs.writeNewlineToNewDDL();
+ firstTime = false;
+ }
+ }
+
+ private static String separatorStr(boolean addSeparator)
+ {
+ return (addSeparator) ? ", " : "";
+ }
+
+ /** **************************************************
+ * Generate table privilege statement for the current row
+ *
+ * @param rs ResultSet holding tableperm information
+ * @param fullName Table's qualified name
+ * @param authName Authorization id for grant statement
+ */
+ private static String tablePrivStatement(ResultSet rs, String fullName, String authName)
+ throws SQLException
+ {
+ boolean addSeparator = false;
+ StringBuffer grantStmt = new StringBuffer("GRANT ");
+
+ if (rs.getString(4).toUpperCase().equals("Y"))
+ {
+ grantStmt.append("SELECT");
+ addSeparator = true;
+ }
+
+ if (rs.getString(5).toUpperCase().equals("Y"))
+ {
+ grantStmt.append(separatorStr(addSeparator)+ "DELETE");
+ addSeparator = true;
+ }
+
+ if (rs.getString(6).toUpperCase().equals("Y"))
+ {
+ grantStmt.append(separatorStr(addSeparator)+ "INSERT");
+ addSeparator = true;
+ }
+
+ if (rs.getString(7).toUpperCase().equals("Y"))
+ {
+ grantStmt.append(separatorStr(addSeparator)+ "UPDATE");
+ addSeparator = true;
+ }
+
+ if (rs.getString(8).toUpperCase().equals("Y"))
+ {
+ grantStmt.append(separatorStr(addSeparator)+ "REFERENCES");
+ addSeparator = true;
+ }
+
+ if (rs.getString(9).toUpperCase().equals("Y"))
+ {
+ grantStmt.append(separatorStr(addSeparator)+ "TRIGGER");
+ addSeparator = true;
+ }
+
+ grantStmt.append(" ON " + fullName + " TO " + authName);
+
+ return grantStmt.toString();
+ }
+
+ /** ************************************************
+ * Generate column privilege statements
+ *
+ * @param rs ResultSet holding column privilege information
+ * @param conn Connection to use. Used to get another ResultSet
+ ****/
+
+ private static void generateColumnPrivs(ResultSet rs, Connection conn)
+ throws SQLException
+ {
+ boolean firstTime = true;
+ while (rs.next()) {
+ if (firstTime) {
+ Logs.reportString("----------------------------------------------");
+ Logs.reportMessage( "DBLOOK_ColumnPrivHeader");
+ Logs.reportString("----------------------------------------------\n");
+ }
+
+ String authName = rs.getString(1);
+ String schemaName = dblook.expandDoubleQuotes(rs.getString(2));
+ String tableName = dblook.expandDoubleQuotes(rs.getString(3));
+
+ if (dblook.isIgnorableSchema(schemaName))
+ continue;
+
+ // Create another resultSet to get column names
+ Statement stmtCols = conn.createStatement();
+ String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME " +
+ "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " +
+ "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "+
+ "and T.TABLENAME = '"+tableName+"' AND SCHEMANAME = '"+schemaName +
+ "' ORDER BY COLUMNNUMBER";
+
+ ResultSet rsCols= stmtCols.executeQuery(queryCols);
+ String fullName = dblook.addQuotes(schemaName) + "." + dblook.addQuotes(tableName);
+
+ Logs.writeToNewDDL(columnPrivStatement(rs, fullName, authName, rsCols));
+ Logs.writeStmtEndToNewDDL();
+ Logs.writeNewlineToNewDDL();
+ firstTime = false;
+ }
+ }
+
+ private static String privTypeToString(String privType)
+ {
+ if (privType.equals("S"))
+ return "SELECT";
+ else if (privType.equals("R"))
+ return "REFERENCES";
+ else if (privType.equals("U"))
+ return "UPDATE";
+
+ // Should throw an exception?
+ return "";
+ }
+
+ /** ************************************************
+ * Generate one column grant statement
+ *
+ * @param columns List of columns to grant required privs
+ * @param rsCols ResultSet for mapping column numbers to names
+ ****/
+
+ private static String mapColumnsToNames(String columns, ResultSet rsCols)
+ throws SQLException
+ {
+ StringBuffer colNames = new StringBuffer();
+ rsCols.next();
+ int curColumn = 1;
+ boolean addSeparator = false;
+
+ // Strip out outer {} in addition to spaces and comma
+ StringTokenizer st = new StringTokenizer(columns, " ,{}");
+ while (st.hasMoreTokens())
+ {
+ int colNum = Integer.parseInt(st.nextToken());
+ while (colNum+1 > curColumn)
+ {
+ rsCols.next();
+ curColumn = rsCols.getInt(1);
+ }
+ colNames.append(separatorStr(addSeparator));
+ colNames.append(rsCols.getString(2));
+ addSeparator = true;
+ }
+
+ return colNames.toString();
+ }
+
+ /** ************************************************
+ *
+ * @param rs ResultSet with info for this GRANT statement
+ * @param fullName Full qualified name of the table
+ * @param rs authorization name for this GRANT
+ * @param rsCols ResultSet for mapping column numbers to names
+ ****/
+
+ private static String columnPrivStatement(ResultSet rs, String fullName,
+ String authName, ResultSet rsCols) throws SQLException
+ {
+ StringBuffer grantStmt = new StringBuffer("GRANT ");
+
+ String privType = rs.getString(4).toUpperCase();
+ String columns = rs.getString(5);
+ grantStmt.append(privTypeToString(privType));
+ grantStmt.append("(");
+ grantStmt.append(mapColumnsToNames(columns, rsCols));
+ grantStmt.append(")");
+ grantStmt.append(" TO ");
+ grantStmt.append(authName);
+
+ return grantStmt.toString();
+ }
+
+ /** ************************************************
+ * Generate routine privilege statements
+ *
+ * @param rs ResultSet holding required information
+ ****/
+ public static void generateRoutinePrivs(ResultSet rs) throws SQLException
+ {
+ boolean firstTime = true;
+ while (rs.next()) {
+ String authName = rs.getString(1);
+ String schemaName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(2)));
+ String aliasName = dblook.addQuotes(dblook.expandDoubleQuotes(rs.getString(3)));
+ String fullName = schemaName + "." + aliasName;
+ String aliasType = rs.getString(4);
+
+ if (dblook.isIgnorableSchema(schemaName))
+ continue;
+
+ // Ignore SYSCS_UTIL privileges as all new databases automatically get them
+ if (schemaName.equals("\"SYSCS_UTIL\""))
+ continue;
+
+ if (firstTime) {
+ Logs.reportString("----------------------------------------------");
+ Logs.reportMessage("DBLOOK_RoutinePrivHeader");
+ Logs.reportString("----------------------------------------------\n");
+ }
+
+ Logs.writeToNewDDL(routinePrivStatement(fullName, authName, aliasType));
+ Logs.writeStmtEndToNewDDL();
+ Logs.writeNewlineToNewDDL();
+ firstTime = false;
+ }
+ }
+
+ private static String routinePrivStatement(String fullName, String authName, String aliasType)
+ throws SQLException
+ {
+ boolean addSeparator = false;
+ StringBuffer grantStmt = new StringBuffer("GRANT EXECUTE ON ");
+
+ grantStmt.append((aliasType.equals("P")) ? "PROCEDURE " : "FUNCTION ");
+ grantStmt.append(fullName);
+ grantStmt.append(" TO ");
+ grantStmt.append(authName);
+
+ return grantStmt.toString();
+ }
+}
Propchange: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties?rev=387160&r1=387159&r2=387160&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties Sun Mar 19 23:03:19 2006
@@ -215,6 +215,9 @@
DBLOOK_SynonymHeader=DDL Statements for Synonyms
DBLOOK_TriggersHeader=DDL Statements for triggers
DBLOOK_ViewsHeader=DDL Statements for views
+DBLOOK_TablePrivHeader=GRANT statements for tables
+DBLOOK_ColumnPrivHeader=GRANT statements for columns
+DBLOOK_RoutinePrivHeader=GRANT statements for routines
DBLOOK_Jar_Note=\
**** NOTE **** In order for jar files to be loaded correctly,\n\
-- you must either 1) ensure that the DBJARS directory (created\n\
Modified: db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java?rev=387160&r1=387159&r2=387160&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java Sun Mar 19 23:03:19 2006
@@ -47,6 +47,7 @@
import org.apache.derby.impl.tools.dblook.DB_Alias;
import org.apache.derby.impl.tools.dblook.DB_Trigger;
import org.apache.derby.impl.tools.dblook.DB_View;
+import org.apache.derby.impl.tools.dblook.DB_GrantRevoke;
import org.apache.derby.impl.tools.dblook.Logs;
public final class dblook {
@@ -79,6 +80,8 @@
private static LocalizedResource langUtil;
+ private static boolean sqlAuthorization;
+
/* ************************************************
* main:
* Initialize program state by creating a dblook object,
@@ -535,6 +538,8 @@
DB_Trigger.doTriggers(this.conn);
+ DB_GrantRevoke.doAuthorizations(this.conn);
+
// That's it; we're done.
if (getColNameFromNumberQuery != null)
getColNameFromNumberQuery.close();
@@ -604,6 +609,16 @@
addQuotes(expandDoubleQuotes(rs.getString(2))));
}
+ // Check if sqlAuthorization mode is on. If so, need to generate
+ // authorization statements.
+ rs = stmt.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY" +
+ "('derby.database.sqlAuthorization')");
+ if (rs.next())
+ {
+ String sqlAuth = rs.getString(1);
+ if (Boolean.valueOf(sqlAuth).booleanValue())
+ sqlAuthorization = true;
+ }
stmt.close();
// Load default property values.
@@ -725,6 +740,15 @@
return "\"" + name + "\"";
+ }
+
+
+ public static String addSingleQuotes(String name) {
+
+ if (name == null)
+ return null;
+
+ return "'" + name + "'";
}
/* ************************************************