Skip to content

Modify access to source data systems

This document provides step-by-step instructions and guidelines for adjusting access settings in PostgreSQL, MySQL, Oracle, and SQL Server databases. It covers specific topics such as modifying the pg_hba.conf file for PostgreSQL and specifying IP masks in MySQL, while also addressing potential firewall changes for Oracle and SQL Server. Using this guide, you can effectively update the access configurations to meet your requirements for each source system.

PostgreSQL

PostgreSQL allows access via CIDR blocks. To modify access for CIDR blocks in PostgreSQL, you need to make changes in the pg_hba.conf file. This file lets you define rules for access control based on IP addresses or CIDR blocks. By specifying the appropriate IP address range, you can allow the required IP addresses for accessing the PostgreSQL database.

  1. Locate the pg_hba.conf file: The pg_hba.conf file is located in the data directory of your PostgreSQL installation. The path to the file may vary depending on your operating system and PostgreSQL version. Common locations include:
    • Linux: /etc/postgresql//main/pg_hba.conf
    • Windows: C:\Program Files\PostgreSQL\\data\pg_hba.conf
  2. Open the pg_hba.conf file in a text editor: Use a text editor of your choice to open the pg_hba.conf file. Make sure you have the necessary permissions to modify the file.
  3. Understand the file structure: The pg_hba.conf file consists of a series of records, each specifying a set of connection parameters and access rules. Each record is represented by a line in the file.
  4. Identify the IPv4 section: The file contains multiple sections, and you need to locate the section that corresponds to the type of connection you want to modify. For example, if you want to modify access for IPv4 connections, look for the section that starts with # IPv4 local connections.
  5. Modify the access rules: Within the relevant section, you'll find lines specifying access rules. Each rule consists of six fields separated by whitespace:
  6. type: Specifies the type of connection (e.g., local, host, hostssl, hostnossl).
  7. database: Specifies the name of the target database, or all for all databases.
  8. user: Specifies the name of the user, or all for all users.
  9. address: Specifies the IP address or CIDR block for the client or for all addresses.
  10. method: Specifies the authentication method (e.g., md5, password, trust).
  11. options: Specifies additional options or parameters.
  12. Add or modify an access rule: To allow access from a specific CIDR block, you can add a new line or modify an existing line within the relevant section. For example, to allow access from the CIDR block 192.168.0.0/24, you can add the following line:
    • host all 192.168.0.0/24 md5
  13. Save the changes: Once you have made the necessary modifications, save the pg_hba.conf file.
  14. Restart PostgreSQL: To apply the changes, you need to restart the PostgreSQL service or reload the configuration. The exact method depends on your operating system and PostgreSQL installation.

Note

Modifying the pg_hba.conf file requires careful consideration of security and authentication requirements. It's recommended to consult the official PostgreSQL documentation and seek guidance from database administrators or IT professionals to ensure proper access control configuration.

Here's the link to the official PostgreSQL documentation on pg_hba.conf for further reference and details: pg_hba.conf documentation.

MySQL

In MySQL, access control is primarily managed through user account management. By granting privileges to users and specifying IP addresses or subnet masks, you can allow specific IP address ranges for accessing the MySQL database. This ensures that only the allowed IP addresses are permitted to connect.

  1. Connect to MySQL: Use a MySQL client or command-line interface to connect to your MySQL server as a user with administrative privileges. For example, you can use the MySQL command-line tool with the appropriate credentials.

  2. Grant privileges: To grant privileges to a user with a specific IP mask, you can use the GRANT statement. The syntax is as follows:

    GRANT privileges ON database.table TO
  'username'@'ip_mask' IDENTIFIED BY 'password';
    • Replace privileges with the specific privileges you want to grant (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES).
    • Replace database.table with the specific database and table to which the privileges apply. You can use * as a wildcard to grant privileges to all databases or tables.
    • Replace 'username' with the desired username for the account.
    • Replace 'ip_mask' with the IP mask to specify the allowed IP range.
    • Replace 'password' with the password for the user.

    For example, to grant all privileges on mydatabase.mytable to a user with the IP mask 192.168.0.0/24, you would use the following statement:

        GRANT ALL PRIVILEGES ON mydatabase.mytable TO 'myuser'@'192.168.0.0/24' IDENTIFIED BY 'mypassword';`

  3. Flush privileges: After granting privileges, you need to flush the privileges to ensure the changes take effect immediately. Use the following statement: FLUSH PRIVILEGES;

  4. Verify access: You can verify the access by connecting to the MySQL server using the specified IP mask and credentials.

    Note

    • IP masks in MySQL use wildcard characters for specifying IP ranges. The % wildcard represents any number of characters, and the _ wildcard represents a single character. For example, 192.168.0.% allows access from any IP address that starts with 192.168.0.
    • MySQL supports both CIDR notation or subnet masks for defining a range of IP addresses. CIDR block support was added with MySQL version 8.

Here's the link to the official MySQL documentation for managing user accounts, which provides more details and examples MySQL User Account Management documentation.

Oracle

Access controls for Oracle databases are typically configured at the network infrastructure or firewall level. This involves modifying firewall rules or network settings to allow the required IP address range. By allowing access only from the specified IP addresses, you ensure that the Oracle database can only be accessed by authorized sources.

  1. Identify the network infrastructure or firewall: Determine the network infrastructure or firewall that controls the access to your Oracle database. This could be a physical firewall appliance, a software-based firewall, or network routers and switches.
  2. Access the network infrastructure/firewall: Gain administrative access to the network infrastructure or firewall system. This may require working with your network or IT team, or contacting the relevant system administrator.
  3. Determine the IP ranges to allow or restrict: Identify the specific IP ranges that you want to allow or restrict from accessing your Oracle database. This may involve considering the IP addresses of client systems, applications, or other entities that require access.
  4. Configure firewall rules or network settings: Use the administrative interface or command-line interface of your network infrastructure or firewall to configure the necessary rules or settings. The exact method varies depending on the system you are using.
  5. Create inbound or outbound rules: Create firewall rules to allow or restrict access to the Oracle database based on the identified IP ranges. You may need to specify the source IP addresses or ranges, destination IP address or hostname of the Oracle server, and the ports used for Oracle database communication (usually port 1521 for the default Oracle listener).
  6. Apply the changes: Save and apply the configured firewall rules or network settings. This will ensure that the desired IP ranges are allowed or restricted as intended.
  7. Test the access: Verify the access by attempting to connect to the Oracle database from systems within the specified IP ranges. Ensure that the connections are successful and that access is properly restricted from other IP ranges.

Note

Specific steps and methods for configuring access controls may vary depending on the network infrastructure or firewall system you are using. It's recommended to consult the documentation or resources provided by the manufacturer or vendor of your network infrastructure/firewall system for detailed instructions.

Oracle's documentation covers network and security configurations, which can provide further guidance specific to your Oracle database and environment. You can refer to the Oracle documentation for more information here.

SQL Server

Similar to Oracle, access control for SQL Server databases is commonly managed at the network infrastructure or firewall level. Adjusting firewall rules or network settings allows you to allow the necessary IP address range. By doing so, you restrict access to the SQL Server database to the specified IP addresses, enhancing security.

Here's a detailed explanation of how to manage access control for SQL Server:

  1. Identify the network infrastructure or firewall: Determine the network infrastructure or firewall system that controls the access to your SQL Server. This could be a physical firewall appliance, a software-based firewall, or network routers and switches.
  2. Gain administrative access: Obtain administrative access to the network infrastructure or firewall system. This may require working with your network or IT team, or contacting the relevant system administrator.
  3. Determine the IP ranges to allow or restrict: Identify the specific IP ranges that you want to allow or restrict for accessing your SQL Server. Consider the IP addresses of client systems, applications, or other entities that require access to the SQL Server.
  4. Configure firewall rules or network settings: Use the administrative interface or command-line interface of your network infrastructure or firewall system to configure the necessary rules or settings. The exact method varies depending on the system you are using.
  5. Create inbound or outbound rules: Create firewall rules to allow or restrict access to the SQL Server based on the identified IP ranges. You may need to specify the source IP addresses or ranges, destination IP address or hostname of the SQL Server, and the ports used for SQL Server communication (usually port 1433 for the default instance).
  6. Apply the changes: Save and apply the configured firewall rules or network settings. This ensures that the desired IP ranges are allowed or restricted as intended.
  7. Test the access: Verify the access by attempting to connect to SQL Server from systems within the specified IP ranges. Ensure that the connections are successful and that access is properly restricted from other IP ranges.

Note

Specific steps and methods for configuring access controls may vary depending on the network infrastructure or firewall system you are using. Consult the documentation or resources provided by the manufacturer or vendor of your network infrastructure/firewall system for detailed instructions.

Microsoft provides documentation on securing SQL Server and network configurations, which can provide further guidance specific to your SQL Server and environment. You can refer to the Microsoft SQL Server documentation for more information here.

© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement