How to securely connect to MySQL database

January 1, 2022

I have my MySQL database on a Raspberry Pi. I set it up like this:

sudo mariadb -u root -p

CREATE DATABASE Login;
CREATE USER RaspberryPi@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON Login.* TO RaspberryPi@'%';
SELECT Login;
CREATE TABLE Users;

I have this python code:

import mysql.connector
mydb = mysql.connector.connect(host="addr_of_pi", user="RaspberryPi", passwd="password", database="Login")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM Users")
print(mycursor)

The problem is, that I want to give this script to a person as an .exe file but I don’t want to give him the account credentials to my database. I tried using PyProtect but the .exe script can still be read in some way. I also tried using os.gentenv(...) but then you have to create a .env file with the credentials that also can be read. So is there an other way?

>Solution :

Try stored procedures.

Exe files can easily be decompiled. Often you can just open the file in a text editor and see the strings, so your approach is not safe.

You should instead:

Create a stored procedure which does the select:

    CREATE DEFINER=root@'%' PROCEDURE SelectUsers()
        READS SQL DATA
    BEGIN
      SELECT
        *
      FROM Users;
    END

Only grant permissions to execute the procedure to the user. Since the procedure is defined with DEFINER=root@'%' the RaspberryPi user will get the data on behalf of the root user, therefore there is no need to grant access to the table itself:

    GRANT EXECUTE ON PROCEDURE Login.SelectUsers TO 'RaspberryPi'@'%';

Hand out your script like this:

    import mysql.connector
    mydb = mysql.connector.connect(host="addr_of_pi", user="RaspberryPi", passwd="password", database="Login")
    mycursor = mydb.cursor()
    mycursor.execute("CALL SelectUsers()")
    print(mycursor)

With this approach your RaspberryPi user will have no access to any table in your database, it can just CALL the stored procedure you granted execute permission. So, no need to "exe" your Python script.