001package com.identityworksllc.iiq.common.tools; 002 003import org.apache.commons.logging.Log; 004import org.apache.commons.logging.LogFactory; 005import picocli.CommandLine; 006import sailpoint.api.SailPointContext; 007import sailpoint.api.SailPointFactory; 008import sailpoint.object.AuditEvent; 009import sailpoint.object.Identity; 010import sailpoint.plugin.PluginBaseHelper; 011import sailpoint.plugin.SqlScriptExecutor; 012import sailpoint.server.Auditor; 013import sailpoint.server.Environment; 014import sailpoint.server.SailPointConsole; 015import sailpoint.tools.GeneralException; 016import sailpoint.tools.Util; 017 018import java.io.File; 019import java.sql.Connection; 020import java.sql.SQLException; 021import java.util.ArrayList; 022import java.util.List; 023import java.util.concurrent.Callable; 024 025/** 026 * A command-line tool to run one or more SQL Scripts via the Plugin SQL execution 027 * tool. 028 * 029 * You need to use this via the Launcher entry point, aka the 'iiq' command. The Launcher 030 * automatically loads command classes from WEB-INF, so it's sufficient for IIQ Common 031 * to be there, along with picocli. 032 * 033 * Usage: ./iiq com.identityworksllc.iiq.common.tools.RunSQLScript path/to/script1 path/to/script2... 034 */ 035@CommandLine.Command(name = "sql-script", synopsisHeading = "", customSynopsis = { 036 "Usage: ./iiq com.identityworksllc.iiq.common.tools.RunSQLScript FILE [FILE...]" 037}, mixinStandardHelpOptions = true) 038@SuppressWarnings("unused") 039public class RunSQLScript implements Callable<Integer> { 040 041 /** 042 * Logger 043 */ 044 private static final Log log = LogFactory.getLog(RunSQLScript.class); 045 046 /** 047 * Main method, to be invoked by the Sailpoint Launcher. Defers immediately 048 * to picocli to handle the inputs. 049 * 050 * @param args The command line arguments 051 */ 052 public static void main(String[] args) { 053 new CommandLine(new RunSQLScript()).execute(args); 054 } 055 056 /** 057 * The password, which can be provided at the command line, or can be 058 * interactively entered if not present. 059 */ 060 @CommandLine.Option(names = {"-p", "--password"}, arity = "0..1", interactive = true) 061 private String password; 062 063 /** 064 * If present, specifies that the commands ought to be run against the plugin 065 * schema, not the identityiq schema. 066 */ 067 @CommandLine.Option(names = {"--plugin-schema"}, description = "If specified, run the script against the plugin schema instead of the IIQ schema") 068 private boolean pluginSchema; 069 070 /** 071 * The command line arguments, parsed as File locations 072 */ 073 @CommandLine.Parameters(paramLabel = "FILE", arity = "1..", description = "One or more SQL scripts to execute", type = File.class) 074 private List<File> sqlScripts = new ArrayList<>(); 075 076 /** 077 * The username provided at the command line 078 */ 079 @CommandLine.Option(names = {"-u", "--user"}, required = true, description = "The username with which to log in to IIQ", defaultValue = "spadmin", showDefaultValue = CommandLine.Help.Visibility.ALWAYS) 080 private String username; 081 082 /** 083 * The main action, invoked by picocli after populating the parameters. 084 * 085 * @return The exit code 086 * @throws Exception on failures 087 */ 088 @Override 089 public Integer call() throws Exception { 090 SailPointContext context = SailPointFactory.createContext(); 091 try { 092 if (Util.isEmpty(sqlScripts)) { 093 throw new IllegalArgumentException("You must provide the path to at least one SQL file"); 094 } 095 096 if (Util.isEmpty(username) || Util.isEmpty(password)) { 097 throw new IllegalArgumentException("Missing authentication information"); 098 } 099 100 Identity authenticated = context.authenticate(username, password); 101 102 if (authenticated == null) { 103 throw new SailPointConsole.AuthenticationException("Authentication failed"); 104 } 105 106 Identity.CapabilityManager capabilityManager = authenticated.getCapabilityManager(); 107 108 if (!(capabilityManager.hasCapability("SystemAdministrator") || capabilityManager.hasRight("IIQCommon_SQL_Importer"))) { 109 throw new SailPointConsole.AuthenticationException("User cannot access the SQL importer"); 110 } 111 112 System.out.println("Logged in successfully as user: " + authenticated.getDisplayableName()); 113 114 for (File file : sqlScripts) { 115 if (!file.exists()) { 116 throw new IllegalArgumentException("File not found: " + file.getPath()); 117 } 118 119 if (!file.canRead()) { 120 throw new IllegalArgumentException("File exists but cannot be read: " + file.getPath()); 121 } 122 123 System.out.println("Executing SQL script file: " + file.getPath()); 124 125 String script = Util.readFile(file); 126 127 if (Util.isNotNullOrEmpty(script) || script.trim().isEmpty()) { 128 System.out.println(" WARNING: File was empty"); 129 } 130 131 AuditEvent ae = new AuditEvent(); 132 ae.setAction("iiqCommonSqlImport"); 133 ae.setServerHost(Util.getHostName()); 134 ae.setSource(authenticated.getName()); 135 ae.setAttribute("file", file.getPath()); 136 ae.setTarget(Util.truncateFront(file.getPath(), 390)); 137 138 Auditor.log(ae); 139 context.commitTransaction(); 140 141 try (Connection connection = getConnection()) { 142 // This is the plugin script executor. Why don't they use this 143 // at the console level? We'll use it anyway. We probably 144 // will want to extend this and write our own at some point, 145 // or use the MyBatis one. 146 SqlScriptExecutor scriptExecutor = new SqlScriptExecutor(); 147 scriptExecutor.execute(connection, script); 148 } 149 } 150 } finally { 151 SailPointFactory.releaseContext(context, false); 152 } 153 154 return 0; 155 } 156 157 /** 158 * Gets a connection to the database, depending on which flag is set 159 * @return The connection to the database 160 * @throws SQLException if a SQL exception occurs 161 * @throws GeneralException if a general exception occurs 162 */ 163 private Connection getConnection() throws SQLException, GeneralException { 164 if (pluginSchema) { 165 return PluginBaseHelper.getConnection(); 166 } else { 167 return Environment.getEnvironment().getSpringDataSource().getConnection(); 168 } 169 } 170}