I am tasked with security of the systems database, as the previous operation lacked security and left gaping holes in the system. The Systems information was leaked, and clients records were leaked as well as workers salaries. I need to find One technique or strategy to prevent this information loss, and propose how we will prevent this in future. I am relatively new to DBMS, can you please help me.
Which database are you using MySQL, SQL server, Oracle DB which one?
In a secure system, no lines should be echoed back. Another way to do the same:
mysql> SHOW GRANTS FOR ''@'localhost';
mysql> SHOW GRANTS FOR ''@'myhost';
If the grants exist, then anybody can access the database and at least use the default database“test”. Check this with:
shell> mysql -u blablabla
To remove the account, execute the following command:
mysql> DROP USER "";
The MySQL “DROP USER” command is supported starting with MySQL version 5.0. If you use an older version of MySQL, you can remove the account as follows:
mysql> use mysql;
mysql> DELETE FROM user WHERE user="";
mysql> flush privileges;
2. Disable or restrict remote access
Consider whether MySQL will be accessed from the network or only from its own server.
If remote access is used, ensure that only defined hosts can access the server. This is typically done through TCP wrappers, iptables, or any other firewall software or hardware available on the market.
To restrict MySQL from opening a network socket, the following parameter should be added in the [mysqld] section of my.cnf or my.ini:
The file is located in the “C:\Program Files\MySQL\MySQL Server 5.1” directory on the Windows operating system or “/etc/my.cnf” or “/etc/mysql/my.cnf” on Linux.
3.Disable the use of LOCAL INFILE
The next change is to disable the use of the “LOAD DATA LOCAL INFILE” command, which will help to prevent unauthorized reading from local files. This is especially important when new SQL Injection vulnerabilities in PHP applications are found.
In addition, in certain cases, the “LOCAL INFILE” command can be used to gain access to other files on the operating system, for instance “/etc/passwd”, using the following command:
mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE table1
4. Change root username and password
The default administrator username on the MySQL server is “root”. Hackers often attempt to gain access to its permissions. To make this task harder, rename “root” to something else and provide it with a long, complex alphanumeric password.
To rename the administrator’s username, use the rename command in the MySQL console:
mysql> RENAME USER root TO new_user;
5 Lower system privileges; increase database security with Role Based Access Control
A very common database security recommendation is to lower the permissions given to various parties. MySQL is no different.
To protect your database, make sure that the file directory in which the MySQL database is actually stored is owned by the user “mysql” and the group “mysql”.
shell>ls -l /var/lib/mysql
In addition, ensure that only the user “mysql” and “root” have access to the directory/var/lib/mysql.
When an application takes user data as an input, there is an opportunity for a malicious user to enter carefully crafted data that causes the input to be interpreted as part of a SQL query instead of data.
For example, imagine this line of code:which is designed to show all records from the table "Users" for a username and password supplied by a user. Using a Web interface, when prompted for his username and password, a malicious user might enter:
SQL Server supports two modes of authentication: Windows Authentication and Mixed Mode Authentication. In accordance with SQL Server security best practices, always choose Windows Authentication for your SQL Server installation unless legacy applications require Mixed Mode Authentication for backward compatibility and access.
2. SQL Server instance: Server-Level and Database Permissions
You need to be very careful with server-level permissions as they grant access on the instance-level. The available fixed server roles in SQL Server 2016 are:
Make sure that you include only the necessary logins to memberships on the above roles. For example, never assign the SysAdmin role to BUILTIN\Administrators windows group. Machine administrators must not have access to the SQL Server instance. Now, if you want to grant access to SQL Server to a person that he or she is also a machine administrator, you can do this individually for the specific login and not the BUILTIN\Administrators group.
3. SQL Server instance: Password Policies
When Windows Authentication Mode is used, then password policy enforcement (i.e. complexity validation for strong passwords), support for account lockout and password expiration are some of security features that are applied by default for the logins that use this mode.
If you want to apply the same complexity and expiration policies used in Windows for SQL logins (that use a username and password) as well, then you need to make sure that Password Policy and Password Expiration options are set for these logins as well.
4. SQL Server instance: Patching
It is essential that you keep your SQL Server instances up to date by installing the latest service pack and critical cumulative patches. However, prior to applying any patch or service pack, you need to verify that it will not create any issues for your current SQL Server setup. To this end, always test service packs and patches on a Test environment first and proceed to Production only after you verify that everything works properly.
5. SQL Server instance: Encryption
If you have databases that store sensitive data, SQL Server provides a number of encryption options that you can use.
The encryption options currently available are:
Firewalls are important to help secure the SQL Server installation. Firewalls will be most effective if you follow these guidelines:
Put a firewall between the server and the Internet. Enable your firewall. If your firewall is turned off, turn it on. If your firewall is turned on, do not turn it off.
Divide the network into security zones separated by firewalls. Block all traffic, and then selectively admit only what is required.
In a multi-tier environment, use multiple firewalls to create screened subnets.
When you are installing the server inside a Windows domain, configure interior firewalls to allow Windows Authentication.
7. Running SQL Server in Different Port other than the Default Port
By default SQL Server uses the TCP/IP Port 1433 for the Database Engine. As a best practice DBAs should change the TCP/IP Port on which SQL Server Database Engine will be listening. These changes must always be done using SQL Server Configuration Manager.
Links to refer:
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly