org.kawanfw.sql.api.server.firewall.CsvRulesManager Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of aceql-http Show documentation

AceQL HTTP is a framework of REST like http APIs that allow to access to remote SQL databases over http from any device that supports http. AceQL HTTP is provided with four client SDK: - The AceQL C# Client SDK allows to wrap the HTTP APIs using Microsoft SQL Server like calls in their code, just like they would for a local database. - The AceQL Java Client SDK allows to wrap the HTTP APIs using JDBC calls in their code, just like they would for a local database. - The AceQL Python Client SDK allows SQL calls to be encoded with standard unmodified DB-API 2.0 syntax

There is a newer version: 12.2

Show newest version

/*
 * Copyright (c)2022 KawanSoft S.A.S. All rights reserved.
 * 
 * Use of this software is governed by the Business Source License included
 * in the LICENSE.TXT file in the project's root directory.
 *
 * Change Date: 2026-11-01
 *
 * On the date above, in accordance with the Business Source License, use
 * of this software will be governed by version 2.0 of the Apache License.
 */
package org.kawanfw.sql.api.server.firewall;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.attribute.BasicFileAttributes;
import java.nio.file.attribute.FileTime;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.TreeSet;

import org.kawanfw.sql.api.server.DatabaseConfigurator;
import org.kawanfw.sql.api.server.SqlEvent;
import org.kawanfw.sql.api.server.StatementAnalyzer;
import org.kawanfw.sql.api.util.firewall.CsvRulesManagerLoader;
import org.kawanfw.sql.api.util.firewall.DatabaseUserTableTriplet;
import org.kawanfw.sql.api.util.firewall.TableAllowStatements;
import org.kawanfw.sql.metadata.AceQLMetaData;
import org.kawanfw.sql.servlet.injection.classes.InjectedClassesStore;
import org.kawanfw.sql.servlet.injection.properties.PropertiesFileStore;
import org.kawanfw.sql.servlet.util.logging.LoggerWrapper;
import org.kawanfw.sql.util.FrameworkDebug;
import org.kawanfw.sql.util.SqlTag;
import org.kawanfw.sql.util.TimestampUtil;
import org.slf4j.Logger;

/**
 * Firewall manager that checks each SQL request against the content of a CSV
 * File. The CSV file is loaded in memory at AceQL server startup. 

 * 

 * The name of the CSV file that will be used by a database is: 
 * <database>_rules_manager.csv, where {@code database} is
 * the name of the database declared in the {@code aceql-server.properties} files.

 * The file must be located in the same directory as the
 * {@code aceql-server.properties} file used when starting the AceQL server.

 * 

 * The CSV file contains the rules for accessing the tables, with semicolon for
 * separator:
 * 
 * First line contains the element names:
 * username;table;delete;insert;select;update;optional comments
 * Subsequent lines contain the rules, with the values for each element:
 * 
 * {@code username}: AceQL username of the connected client.
 * {@code table}: the table name to access. Name must not include dots and
 * prefixes.
 * {@code delete}: {@code true} if the username has the right to delete rows
 * of the table, else {@code false}.
 * {@code insert}: {@code true} if the username has the right to insert rows
 * in the table, else {@code false}.
 * {@code select}: {@code true} if the username has the right to select rows
 * of the table, else {@code false}.
 * {@code update}: {@code true} if the username has the right to update rows
 * of the table, else {@code false}.
 * Optional comments for the rule.
 * 
 * 
 * 

 * Note that:
 * 
 * {@code public} value may be used for the {@code username} column and
 * means any username. At execution time: if a rule with {@code public} returns
 * true for a CSV column, the rule supersedes other declared rules declared for
 * specific users for the same CSV column.
 * 
{@code all} value is allowed for {@code table} column and means any
 * table. At execution time: If a rule with {@code all} returns true for a CSV
 * column, the rule supersedes other specific rules declared for specific tables
 * for the same CSV column.
 * 
 * 

 * Note that updating the CSV file will reload the rules. If you prefer
 * to disallow dynamic reloading, use a {@link CsvRulesManagerNoReload}
 * implementation. 

 * 

 * See an example of CSV file: sampledb_rules_manager.csv
 * 

 * 

 *
 * @author Nicolas de Pomereu
 * @since 4.1
 */
public class CsvRulesManager implements SqlFirewallManager {

    private static boolean DEBUG = FrameworkDebug.isSet(CsvRulesManager.class);

    private Set databaseSetForReset = new HashSet<>();
    
    /**
     * The map that contains for each database/username the table and their rights
     */
    private Map mapTableAllowStatementsSet = null;

    private FileTime storedFileTime = null;

    /** Default behavior is to allow reload of rules if CSV file is updated */
    protected boolean allowReload = true;

    /**
     * Allows the execution of the statement if an allowing rules exists in
     * the:  <database>_rules_manager.csv file.
     */
    @Override
    public boolean allowSqlRunAfterAnalysis(SqlEvent sqlEvent, Connection connection) throws IOException, SQLException {
	// Load all rules if not already done:
	loadRules(sqlEvent.getDatabase(), connection);

	boolean isAllowed = isAllowed(sqlEvent.getUsername(), sqlEvent.getDatabase(), sqlEvent.getSql(),
		sqlEvent.getParameterValues());
	return isAllowed;

    }

	/**
     * @return true. (Client programs will be allowed to create
     *         raw Statement, i.e. call statements without parameters.)
     */
    @Override
    public boolean allowStatementClass(String username, String database, Connection connection)
	    throws IOException, SQLException {
	return true;
    }


    /**
     * @return true. (Client programs will be allowed to call
     *         the Metadata Query API).
     */
    @Override
    public boolean allowMetadataQuery(String username, String database, Connection connection)
	    throws IOException, SQLException {
	return true;
    }
    
    /**
     * Will say id there is a rule that allows for the usename the SQL statement.
     *
     * @param username
     * @param database
     * @param sql
     * @param parameterValues
     * @return
     * @throws SQLException
     */
    private boolean isAllowed(String username, String database, String sql, List