MySQL Database User Setup

This guide explains how to create a dedicated MySQL database user with the appropriate permissions for executing SQL queries and retrieving schema information in your Text-to-SQL application.

Prerequisites

• Access to a MySQL server with administrative privileges
• MySQL command-line client or a MySQL administration tool like phpMyAdmin

Creating a Database User

Using MySQL Command Line

1. Connect to your MySQL server as an administrator:

mysql -u root -p

2. Create a new user with a secure password:

CREATE USER 'texttosql_user'@'%' IDENTIFIED BY 'your_secure_password';

3. Grant the necessary permissions for schema retrieval and query execution:

-- Grant read-only access to schema information
GRANT SELECT ON information_schema.* TO 'texttosql_user'@'%';
 
-- Grant read-only access to all tables in a specific database
-- Replace 'your_database' with your actual database name
GRANT SELECT ON your_database.* TO 'texttosql_user'@'%';
 
-- If you need the user to execute other types of queries (INSERT, UPDATE, DELETE)
-- add the appropriate permissions:
-- GRANT INSERT, UPDATE, DELETE ON your_database.* TO 'texttosql_user'@'%';

4. Apply the changes:

FLUSH PRIVILEGES;

5. Exit the MySQL client:

EXIT;

Using phpMyAdmin

1. Log in to phpMyAdmin with administrative privileges
2. Navigate to the "User accounts" tab
3. Click "Add user account"
4. Enter the username (e.g., texttosql_user) and a secure password
5. Under "Global privileges", select:
   • USAGE
   • SELECT (for the information_schema database)
6. Click "Go" to create the user
7. Navigate to the specific database you want to grant access to
8. Click the "Privileges" tab
9. Add the new user and grant the SELECT privilege (and any other required privileges)

Verifying the Setup

To verify that the user has the correct permissions:

1. Connect to MySQL using the new user:

mysql -u texttosql_user -p

2. Try to retrieve schema information:

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'your_database';

3. Try executing a simple query:

USE your_database;
SELECT * FROM your_table LIMIT 5;

Connection Configuration

When configuring your Text-to-SQL application, use these credentials:

• Host: Your MySQL server hostname or IP
• Port: 3306 (default MySQL port)
• Database: Your database name
• Username: texttosql_user
• Password: your_secure_password

Security Considerations

• Use a strong, unique password for the database user
• Consider restricting the user's access to specific IP addresses if possible
• Regularly audit the user's permissions to ensure they remain appropriate
• Store connection credentials securely and never expose them in client-side code