Create Mysql User and Grant Privileges

In this tutorial, we are going to learn how we can create MySQL users and grant privileges to that users.

For MySQL installation please follow the tutorial, Install Mysql Specific Version on Ubuntu.

Create MySQL User:

Before creating a new user we need to first log in with the root user, for this, use the following command.

mysql -u username -puserpassword

Here, username is the root user's username and userpassword is the root user's password, use your root user's username and password.

Now, let's create the user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace the username with your desired new user name and password as desired strong password. Here we are using localhost as we are considering the MySQL setup from the same server, if it is for the remote server then use IP address of host/server as below.

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

For this created user, they can access the database from that server not remotely. If we want to create user that can connect from any server or machine as below.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant Privileges:

The general syntax for grant privileges is

GRANT <privileges_type> ON <database_table> TO 'username'@'localhost';

Let's look into some examples

Grant all privileges to user for all database

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

Here, *.* will grant for all databases, and "ALL PRIVILEGES" will grant all the privileges

Grant all privileges to the user for a particular database

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

database_name.* will grant all privileges for all the tables of that database with the database name "database_name"

Grant all privileges to user for a particular database table

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';

We can do database query-specific privileges like Insert, Delete, Create, Drop, Select, Update, etc. For example

GRANT INSERT ON *.* TO 'username'@'localhost';

Here, the user can insert rows into tables for all the databases but can't do other operations like drop, create, update, etc.

Flush Privileges:

After we change the user privileges we need to flush it to reflect the changes. We can do so using following command

FLUSH PRIVILEGES;

Revoke Privileges:

We can revoke the grant privileges for the user as below

REVOKE <privileges_type> ON <database_table> FROM 'username'@'localhost';

Remove User:

We can remove the MySQL user as well by using the following command

DROP USER 'username'@'localhost';

Read More

Install Mysql Specific Version on Ubuntu/Linux using debian package

In this tutorial, we are going to install MySQL version 5.7 using the Debian package(.deb)

Step 1. Add the MySQL Apt Repository

First download the Debian(.deb) package from MySQL Apt Repo. Here we are using the wget to download as below:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

Step 2. Configure the .deb file

sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

You can see the below prompt screen

If somehow we are unable to see the prompt screen then either dpkg is interrupted or broken. Use the below command to clean the config after that use the previous dpkg command, now we can see the screen

apt-get purge mysql-apt-config

Select the ubuntu bionic option and the second screen will pop up as below. Now select the first option to select the MySQL version

Now select the desired version of MySQL, Here we are selecting the MySQL 5.7

Once selected the desired version of MySQL then select ok to configure.

Now the configure suppose to get successful and we can see the output as below:

Selecting previously unselected package mysql-apt-config.
(Reading database ... 36242 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.12-1_all.deb ...
Unpacking mysql-apt-config (0.8.12-1) ...
Setting up mysql-apt-config (0.8.12-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK

Step 3. Install the MySQL Server

Now update the MySQL repository

sudo apt-get update

Install the MySQL using the following command:

sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*

If you chose the latest version of MySQL then no need to specify the package.

Next, the screen will pop up to enter the root password. Enter the password and press ok. After that MySQL is installed and running successfully. Test the MySQL version and MySQL status by using the below command:

mysql --version
sudo systemctl status mysql

Step 4. Secure MySQL Installation

Use the following command to secure MySQL installation:

sudo mysql_secure_installation

Enter your MySQL root password and answer all of the security questions.

Now, log in using the root credential

mysql -u root -p

Step 5. Create a New Mysql User

Before creating new users we need to first log in with the root user as mentioned above. Now, create the user using the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Here, use your own username and password and localhost as we are working on the same machine.

For example:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpassword';

If we want to create a user that can be connected from any machine then use the following command

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Now, let's grant the permission to the newly created user.

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

Here, user have full access to the database with ALL PRIVILEGES and for all the databases as we are using *.*

However, we can grant privileges for specific databases as

GRANT ALL PRIVILEGES ON database_name.* TO 'testuser'@'localhost';

After grant privileges to user, we need to flush the privileges

FLUSH PRIVILEGES;

We can revoke privileges using the following command:

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

Remove User:

DROP USER 'testuser'@'localhost';

Step 6: Some commands for MySQL server:

Stop the MySQL server:

sudo systemctl stop mysql

Start the MySQL server:

sudo systemctl start mysql

Check the status of MySQL server:

sudo systemctl status mysql

Restart MySQL server

sudo systemctl restart mysql

Step 7: Completly remove/uninstall MySQL server

sudo apt-get remove --purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean

Read More

How to use SiftingAppender in Gradle Groovy project

 In this tutorial, we are going to set up the SiftingAppender in our Gradle Groovy project.

Sifting Appender is useful when we want to separate the log files during runtime i.e if we want to separate the log files per thread or per user session basis.

Unfortunately, in the Gradle Groovy project, SiftingAppender is no longer supported since version 1.0.12 as mentioned in Groovy Configuration.

Let's look into the simple example where we are going to configure the SiftingAppender in logback.groovy, where we want to configure the per-user logging mechanism.

import ch.qos.logback.classic.PatternLayout
import ch.qos.logback.classic.sift.MDCBasedDiscriminator
import ch.qos.logback.classic.sift.SiftingAppender

appender("USER_ROLLING", SiftingAppender) {
    discriminator(MDCBasedDiscriminator) {
        key = 'userid'
        defaultValue = 'NONE'
    }
    sift {
        appender("FILE-${userid}", FileAppender) {
            file = "Path-to-log/${userid}.log"
            append = true
            layout(PatternLayout) {
                pattern = "%level %logger - %msg%n"
            }
        }
    }
}
logger("package-to-log",TRACE,['USER_ROLLING'], false)

This is a simple example SiftingAppender configuration; this is derived from logback sifting appender xml configuration.

We are using the Mapped Diagnostic Context for mapping the context user to create a separate file. We can do a similar for the thread as well.

Let's set up the MDC for user, the sample example looks as below.

import groovy.util.logging.Slf4j
import org.slf4j.MDC
@Slf4j
class UserLogging {

    public static void debug(String message, String userid = '') {
        setUserMDC(userid)
        log.debug(message)
    }

    public static void error(String message, String userid = '') {
        setUserMDC(userid)
        log.error(message)
    }

    public static void info(String message, String userid = '') {
        setUserMDC(userid)
        log.info(message)
    }

    public static void setUserMDC(String userid) {
        try {
            if (!userid) {
                MDC.remove("userid")
                return
            }
            MDC.put("userid", userid)
        }catch(e) {
            log.error("Error setting user Mapped Diagnostic Context due to "+e.getMessage())
        }
    }
}

Here, if the userid is available then we are setting the MDC for userid so the log file can be written in a separate file per user. If you want to do with request user do the similar in filter class or the place where it suits.

Now, if we run the application we will get the error as a result the appender doesn't work. So, here we found the solution project that extends Logback Gaffer so that SiftingAppender can be configured in Groovy DSL from this Github repo.

Simply download the jar file for that project and add it to the application.

For the Gradle project create a libs directory under the project directory and load and compile from build.gradle file.

Under build.gradle under dependencies{} section:

compile fileTree(dir: 'libs', include: '*.jar')

If we run the application it suppose to work. The log file will be created on the respective file path.

Read More