How to Install MySQL Server on Ubuntu | Setup new MySQL User with Limited Access Permission.

install mysql in ubuntu linux pdfcup.com

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:

Step 1: Open Terminal and paste the below command.

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).

Step 2: After successfully installation, use the below command for secure database installation. This means setting a custom password to your database.

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

Remove anonymous users? [Y/n] : Y
... 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...

Step 3: Install MySql Client that is used to integrate your database with your web application.

tom@hp~$ sudo apt-get install libmysqlclient-dev

Step 4: Open your MySql Database through the command line.

tom@hp~$ sudo mysql

Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

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;

Step 4: Check the Password set successfully.

How to Create a New User in MySql Database.

Note: only During this step, you can easily give full or partial access of your database to the newly created user. Otherwise, you have another option to change the user permission later but you have to write a few more commands for this process.

Step 1: Open your MySql Shell

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>

Step 2: Create new user.

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password@123';

Step 3: Give all types of permission to the new user.

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.

Step 4: Refresh the MySQL permission.

mysql> FLUSH PRIVILEGES;
mysql> exit

Step 5: Test New user created successfully with a password.

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.

mysql> show databases;

+-------|----------+
| Database |
+----------------- +
| sys |
| mysql |
| TestDB |
+-----------------+

mysql>

How to Give Limited Access Permission to the Database User in MySql Database.

Here is a short list of other types of permissions that users can Access.

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

Step 1: You must have open mysql as root user to set/delete permission.

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)

mysql>

Step 2: Gives only CREATE and INSERT permission to new user.

### 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,

Note:

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.

NOTE: If you need to revoke permission, the structure is almost identical to granting it:

###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;

NOTE: If you want to Revoke permission on a particular table in a Database, then replace this:

test_database.TableName ,therwise condition will apply on each table.

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.

PDFcup.com