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}