import java.io.*;
import java.sql.*;

public class GenerateRoleScript {
    private static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String JDBC_URL = "jdbc:oracle:thin:@10.200.100.80:1521:MY_SCHEMA";
    private static final String JDBC_USER = "MY_SCHEMA";
    private static final String JDBC_PASSWD = "MY_PASSWORD";

    public static void main(String[] args) throws Exception {
        if (args.length < 1) {
            usage();
            System.exit(1);
        }

        String roleName = args[0];

        Class.forName(JDBC_DRIVER);
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD);
            generateRoleScript(conn, roleName);
        }
        finally {
            if (conn != null) conn.close();
        }
    }

    private static void generateRoleScript(Connection conn, String roleName) 
                        throws Exception {
        Statement stmt = conn.createStatement();
        
        ResultSet rs = stmt.executeQuery(
                            "SELECT "
                          + "owner, "
                          + "table_name, "
                          + "privilege, "
                          + "grantable "
                          + "FROM role_tab_privs "
                          + "WHERE role = '" 
                          + roleName
                          + "'");
        PrintWriter p = new PrintWriter(new FileWriter(roleName + "_role.sql"));
        p.println("create role " + roleName + ";");
        while (rs.next()) {
            String owner = rs.getString("OWNER");
            String tableName = rs.getString("TABLE_NAME");
            String privilege = rs.getString("PRIVILEGE");
            String grantable = rs.getString("GRANTABLE");
            p.println(String.format("GRANT %s ON %s.%s TO %s %s;",
                                    privilege,
                                    owner,
                                    tableName,
                                    roleName,
                                    "NO".equals(grantable) ? "" : "WITH GRANT OPTION"));
        }
        rs.close();
        p.close();
    }

    private static void usage() {
        System.out.println("java GenerateRoleScript roleName");
    }
}