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