This is continuation from MySQL-Python – 1 post where we installed MySQL server.
In this page:
Accessing database from MySQL Workbench
We will access Sakila sample database with Python.
First to remeber, when we installed MySQL we choose to install and configure "Samples and Examples". This option installed Sakila database.
We can see it fast, that it is on our server, either using in Windows 11: All Apps > MySQL > MySQL Workbench 8.0 CE
Click on Local instance
If password requested for root, type it, then MySQL Workbench will open and display Administration in Navigator panel.
Clik Schemas and we will see sakila db.
And clicking further on Tables we see those.
Accessing database using MySQL Command Line Client
Similar we can see sakila database using MySQL Command Line Client
In Windows 11: All Apps > MySQL > MySQL 8.0 Command Line Client
When prompt to enter password, enter root password.
Now we can use sakila database, for example type USE sakila;
then we can execute a sql command for example select how many lines are in actor table.
Observation: if sakila database was not installed initial when MySQL was installed
it can be added later, for example follow procedure like here.
In both cases from above user was root. In real life when access databases in applications we use a normal user (a user with fewer privileges, from security reason). Below is the procedure to create the user.
Creating user from MySQL Command Line Client
Lets suppose we need to create user u2024, after you login to MySQL Command Line tool like root run "SELECT user FROM mysql.user;" to check if user already exist.
u2024 is not in the list.
To create it run: CREATE USER 'u2024' IDENTIFIED BY 'password1234';
After create user we use again "SELECT user FROM mysql.user;" which confirm that user is created.
Next step is to grant privileges to u2024 in sakila database for this we run:
GRANT SELECT,CREATE,INSERT,DELETE,UPDATE,EXECUTE,ALTER,DROP,LOCK TABLES,CREATE TEMPORARY TABLES ON sakila.* TO 'u2024';
To test user, open a command prompt , change directory to folder where mysql is:
cd "C:\Program Files\MySQL1\MySQL Server 8.0\bin"
then connect with that user: mysql -u u2024 -p
Further we verify if we run commands, for example select.
Run quit; to exit from MySQL Command Line
Creating user from MySQL Workbench CE
Lets suppose we need to create user u2023.
After open MySQL Workbench CE and use root to connect we have this screen.
Click on "Users and Privileges" there is:
click "Add Account"
Write details as in underlined with red edit boxes, then clik Account Limits
Here we can set limits for user connections, updates and queries.
Click Administrative Roles.
here we can add administrative roles used for administer server. We not check something here because u2023 is just a user with limited privileges.
Click Schema Privileges.
To add privileges for sakila schema click "Add Entry..."
Here select schema sakila then click Ok.
Here select rights, similar with what we granted previous for u2024
Click Apply.
User u2023 is created.
