/* * Copyright 2018. Gatekeeper Contributors * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * */ package org.finra.gatekeeper.services.db.connections; import org.apache.commons.dbcp.BasicDataSource; import org.finra.gatekeeper.configuration.GatekeeperProperties; import org.finra.gatekeeper.exception.GatekeeperException; import org.finra.gatekeeper.rds.exception.GKUnsupportedDBException; import org.finra.gatekeeper.rds.interfaces.DBConnection; import org.finra.gatekeeper.rds.interfaces.GKUserCredentialsProvider; import org.finra.gatekeeper.rds.model.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.CannotGetJdbcConnectionException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.StatementCallback; import org.springframework.stereotype.Component; import java.sql.*; import java.util.*; /** * Interface for dealing with MySQL RDS Instances. */ @Component public class MySQLDBConnection implements DBConnection { private final Logger logger = LoggerFactory.getLogger(MySQLDBConnection.class); private final GKUserCredentialsProvider gkUserCredentialsProvider; private final String gkUserName; private final String ssl; @Autowired public MySQLDBConnection(GatekeeperProperties gatekeeperProperties, @Qualifier("credentialsProvider") GKUserCredentialsProvider gkUserCredentialsProvider){ this.gkUserCredentialsProvider = gkUserCredentialsProvider; this.gkUserName = gatekeeperProperties.getDb().getGkUser(); this.ssl = gatekeeperProperties.getDb().getMysql().getSsl(); } public boolean grantAccess(RdsGrantAccessQuery rdsGrantAccessQuery) throws Exception{ try { createUser(rdsGrantAccessQuery); return true; }catch(Exception ex){ logger.error("An exception was thrown trying to create user " + getGkUserName(rdsGrantAccessQuery.getUser(), rdsGrantAccessQuery.getRole()) + " at address " + rdsGrantAccessQuery.getAddress(), ex); return false; } } public boolean revokeAccess(RdsRevokeAccessQuery rdsRevokeAccessQuery) throws Exception{ String user = rdsRevokeAccessQuery.getUser(); String address = rdsRevokeAccessQuery.getAddress(); String userRole = getGkUserName(user, rdsRevokeAccessQuery.getRole()); try{ JdbcTemplate conn = connect(address, gkUserCredentialsProvider.getGatekeeperSecret(rdsRevokeAccessQuery)); logger.info("Deleting User " + user + " if they already exist on DB " + address); conn.execute("GRANT USAGE ON *.* to " + userRole); conn.execute("DROP USER '" + userRole + "'"); logger.info("Deleted existing " + userRole + " on database " + address); return true; }catch (Exception ex){ logger.error("An exception was thrown Trying to revoke access to " + userRole + " from " + address, ex); return false; } } public List<String> checkDb(RdsQuery rdsQuery) throws GKUnsupportedDBException{ String checkGrants = "SHOW GRANTS FOR CURRENT_USER"; String address = rdsQuery.getAddress(); List<String> issues = new ArrayList<>(); try{ logger.info("Checking the gatekeeper setup for " + address); JdbcTemplate template = connect(address, gkUserCredentialsProvider.getGatekeeperSecret(rdsQuery)); String roleCheckResult = template.queryForObject(checkGrants, String.class); if(!roleCheckResult.contains("CREATE USER")){ issues.add("gatekeeper is missing CREATE USER"); } }catch(SQLException e){ logger.error("Error running check query", e); } catch(CannotGetJdbcConnectionException ex){ logger.error("Could not connect", ex); if(ex.getMessage().contains("password")) { issues.add("Password authentication failed for gatekeeper user"); } else{ issues.add("Unable to connect to DB (Check network configuration)"); } } return issues; } public List<DbUser> getUsers(RdsQuery rdsQuery) throws SQLException { String getUsers = "select user from mysql.user"; List<DbUser> users = new ArrayList<>(); try { JdbcTemplate template = connect(rdsQuery.getAddress(), gkUserCredentialsProvider.getGatekeeperSecret(rdsQuery)); users = template.query(getUsers, new MySqlDbUserMapper()); }catch(SQLException e){ logger.error("Error retrieving users from DB", e); } return users; } public List<String> checkIfUsersHasTables(RdsCheckUsersTableQuery rdsQuery){ return Collections.emptyList(); } public List<String> getAvailableRoles(RdsQuery rdsQuery) throws SQLException{ return Arrays.asList("gk_readonly", "gk_datafix", "gk_dba"); } /** * MySQL has a limit of 16 characters in the user name, if the user that gatekeeper wants to generate that is > 16 characters then * gatekeeper will attempt to use a shorter role, if it's STILL too long then just cut off from the username (hope this is rare case.) * * @param user * @param role * @return */ private String getGkUserName(String user, RoleType role){ return role != null ? user + "_" + role.getShortSuffix() : user; } //pulls all the non system schemas for granting private List<String> getSchemasForDb(JdbcTemplate conn){ String getSchemas = "select distinct table_schema from information_schema.tables where table_schema not in ('information_schema', 'mysql', 'sys', 'performance_schema')"; return conn.queryForList(getSchemas, String.class); } public Map<RoleType, List<String>> getAvailableTables(RdsQuery rdsQuery) throws SQLException{ String address = rdsQuery.getAddress(); Map<RoleType, List<String>> results = new HashMap<>(); JdbcTemplate template = connect(address, gkUserCredentialsProvider.getGatekeeperSecret(rdsQuery)); String schemaQuery = "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema not in ('information_schema', 'mysql', 'sys', 'performance_schema')"; for(RoleType roleType : RoleType.values()) { try { results.put(roleType, template.queryForList(schemaQuery, String.class)); } catch (Exception ex) { logger.error("An exception was thrown while trying to fetch tables over MySQL at address " + address, ex); results.put(roleType, Collections.singletonList("Unable to get available schemas")); } } return results; } private String generateQuery(String roles, String user, String schema){ return "GRANT "+roles+" ON " + schema + ".* TO " + user + " REQUIRE SSL"; } private JdbcTemplate connect(String url, String gkUserPassword) throws SQLException { String dbUrl = "jdbc:mysql://" + url; BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName("org.mariadb.jdbc.Driver"); dataSource.setUrl(dbUrl+"?"+ssl); dataSource.setUsername(gkUserName); dataSource.setPassword(gkUserPassword); dataSource.setMinIdle(0); dataSource.setMaxIdle(0); return new JdbcTemplate(dataSource); } private void createUser(RdsGrantAccessQuery rdsGrantAccessQuery) throws Exception{ String address = rdsGrantAccessQuery.getAddress(); String user = rdsGrantAccessQuery.getUser(); String password = rdsGrantAccessQuery.getPassword(); RoleType role = rdsGrantAccessQuery.getRole(); String gkuserPassword = gkUserCredentialsProvider.getGatekeeperSecret(rdsGrantAccessQuery); JdbcTemplate conn = connect(address, gkuserPassword); String userRole = getGkUserName(user, role); //16 is the maximum length for a user in MySQL, if there's a user hitting this limit, a shorter suffix shall be used //revoke the user if they exist revokeAccess(new RdsRevokeAccessQuery(rdsGrantAccessQuery.getAccount(), rdsGrantAccessQuery.getAccountId(), rdsGrantAccessQuery.getRegion(), rdsGrantAccessQuery.getSdlc(), rdsGrantAccessQuery.getAddress(), rdsGrantAccessQuery.getDbInstanceName()) .withUser(user) .withRole(role)); logger.info("Creating User " + userRole + " if they dont already exist"); boolean wasUserCreated = conn.execute(new MySqlStatement("CREATE USER " + userRole + " IDENTIFIED BY '" + password + "'")); logger.info(wasUserCreated ? "User " + userRole + " successfully created on database" + address : "Failed to create " + userRole + " on database " + address ); List<String> schemasToGrant = getSchemasForDb(conn); logger.info("User " + userRole + " has role "+ role + " granting him those privs"); String privs; switch(role){ case READONLY: privs = "SELECT"; break; case DATAFIX: privs = "SELECT, INSERT, DELETE, UPDATE"; break; case DBA: privs = "SELECT, CREATE, ALTER, DROP "; break; default: throw new GatekeeperException("Unknown Role provided: " + role); } logger.info("Granting roles to all of the non mysql schemas (" +schemasToGrant + ")"); schemasToGrant.forEach(schema -> { logger.info("Granting " + privs + " for " + userRole + " on " + schema); conn.execute(generateQuery(privs, userRole, schema)) ; logger.info("Done!"); }); logger.info("Successfully Created " + userRole + " with "+ role + " for the following schemas " + schemasToGrant); } private class MySqlStatement implements StatementCallback<Boolean>{ private String sql; public MySqlStatement(String sql){ this.sql = sql; } public Boolean doInStatement(Statement statement) throws SQLException, DataAccessException { return statement.execute(sql); } } private class MySqlDbUserMapper implements RowMapper<DbUser> { public DbUser mapRow(ResultSet rs, int rowNum) throws SQLException{ return new DbUser() .setUsername(rs.getString(1)); } } }