Mysql Database Server:
MySql Database server is one of among most popular databases. Here you will learn how to install Mysql Database Server on Linux-Ubuntu Operating System and after installation, you will get to know how to create a new database user and how to give the root access to the new user or limited access to the newly created database user such as CREATE, DELETE, DROP, INSERT, SELECT, UPDATE. Here you will also know about how to configure a Database with the secure installation...
Download MySql Database in Ubuntu:
tom@hp~$ sudo apt install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
mysql-server is already the newest version
(8.0.27-0ubuntu0.20.04.1).
tom@hp~$ sudo mysql_secure_installation
Press y|Y for Yes, any other key For No: Y
There
are three level of password validation policy.
1.LOW 2.MEDIUM 3.STRONG? 2
Please set the password
for root here.
Enter Password : xyz1@A
Reenter Password: xyz1@A
... Success!
Disallow root login remotely? [Y/n] : n # Note: Mysql_Workbench not connect if entered Y.
... Success!
Remove test database and access to it? [Y/n] : N
-----Skipping
Reload privilege tables now? [Y/n] : Y
... Success!
All Done...
tom@hp~$ sudo apt-get install libmysqlclient-dev
tom@hp~$ sudo mysql
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'password@123' ;
Query OK, (0.00 sec)
mysql> FLUSH PRIVILEGES;
mysq> SELECT User, authentication_string, plugin FROM
mysql.user ;
+---------+--------------------------+--------------+
| user
| authentication_string
| plugin
|
+---------+-------------------------+---------------+
| root
| 3Tdv23asdejhfkasyla | mysql_native_password
| mysql.sys |
3cjdfglgrulagda.bvajke |
caching_sha2_password
| mysql.session | *Thcbdaflahrkwa.JDdd |
caching_sha2_password
+-----------------+--------------+--------------------+
How to Create a New User in MySql Database.
tom@hp~$ mysql -u root -p
enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
mysql>
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password@123';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
# Note: Affter giving All kind of
Prmission, Newuser can do CREATE, DELETE, DROP, INSERT, SELECT, UPDATE Operations on
Database.
mysql> FLUSH PRIVILEGES;
mysql> exit
hp@tommy~$ mysql -u newuser -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or
its
affiliates. Other names may be trademarks of their
respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
+-------|----------+
| Database
|
+----------------- +
|
sys
|
| mysql
|
| TestDB
|
+-----------------+
mysql>
How to Give Limited Access Permission to the Database User in MySql Database.
- CREATE- allows them to create new tables or databases
- DROP- allows them to delete tables or databases
- DELETE- allows them to delete rows from tables
- INSERT- allows them to insert rows into tables
- SELECT- allows them to use the SELECT command to read through databases
- UPDATE- allow them to update table rows
- GRANT OPTION- allows them to grant and/or remove other users’ privileges
hp@tommy~$ mysql -u root -p
enter password:
Welcome to the MySQL monitor. Commands end with ; or
\g.
Your MySQL connection id is 20
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or
its
affiliates. Other names may be trademarks of their
respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
### Example: GRANT type_of_permission ON
database_name.table_name TO 'username'@'localhost';
mysql>
GRANT CREATE, INSERT ON test_database.* TO
'newuser2'@'localhost';
Query OK,
mysql> FLUSH PRIVILEGES;
Query OK,
- If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.
- Each time you update or change a permission be sure to use the Flush Privileges command
How to Revoke Permission From the User in MySql.
###Example: REVOKE type_of_permission ON database_name.table_name FROM
'username'@'localhost';
mysql> REVOKE CREATE ON test_database.* FROM 'newuser2'@'localhost';
mysql> FLUSH PRIVILEGES;
How to check user’s current permissions in MySql Database.
mysql> SHOW GRANTS FOR 'username'@'localhost';
How to Delete User in MySql Database.
mysql> DROP USER 'username'@'localhost';
How To Install Mysql Client and MySQL Connector.
mysql> pip install
mysql-connector-python==8.0.15
mysql> pip install mysqlclient==2.0.3
# change version number that you required.