I am in the process of developing a Python3/tkinter application that I want to have its database features based on a remote MySQL server. I have found SQLalchemy and I am trying to understand what the best practices are in terms of remote access and user authentication. There will be a client application that will ask for a username and password and then it will get, insert and update data in the remote database.
Right now, I am starting with a clear board, following the steps in some tutorial:
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql+pymysql://DB_USER:DB_PASSWORD@DATABASE_URL:PORT/DATABASENAME', pool_recycle=3600, echo=True)
Base = declarative_base()
connection = engine.connect()
class Process(Base):
__tablename__ = "processes"
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
Assuming this is the right way to do it, my first question about this code is:
- Isn't here a potential security problem by sending unencrypted user and password through the Internet? Should be taken some kind of measures to prevent password steal? If so, what should I be doing instead?
The other question that I have right now is about users:
- Should each application user correspond to a different MySQL database user, or is it more correct to have the database client with a single user and then add my client users in a table (user id, password, ...), defining and managing them in the application code?
