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}