When you migrate to MySQL Database Service on Oracle Cloud Infrastructure (MDS on OCI), the easiest, fastest and recommended way it to use MySQL Dump & Load Utility.
For more information check these different links:
To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:
mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key,
name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key,
name varchar(20), something varchar(20));
Now, let’s create a user :
mysql> create user myuser identified by 'mypassword';
And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:
MySQL Roles are becoming more and more popular. Therefor, we receive more and more questions related to them.
First I encourage you to read this previous 2 posts:
In this post, I will share you some queries I find useful when using MySQL Roles.
Listing the Roles
The first query allows you to list the Roles created on your MySQL Server and if they are assigned to users, and how many:
SELECT any_value(User) 'Role Name',
IF(any_value(from_user) is NULL,'No', 'Yes') Active,
count(to_user) 'Assigned …[Read more]
One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:
I am happy that the MySQL team is, during the last years, blogging about each major feature that MySQL Server is getting; for example, the series on Recursive Common Table Expressions. Being extremely busy myself, …
[Read more]In my previous article about roles I said that one of the problems with role usage is that roles need to be activated before they kick in. Let's recap briefly what the problem is:
## new session, as user `root`
mysql [localhost] {root} ((none)) > create role viewer;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} ((none)) > grant select on *.* to viewer;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} ((none)) > create user see_it_all identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} ((none)) > grant viewer to see_it_all;
Query OK, 0 rows affected (0.01 sec)
## NEW session, as user `see_it_all`
mysql [localhost] {see_it_all} ((none)) > use test
ERROR 1044 (42000): Access denied for user 'see_it_all'@'%' to database 'test'
mysql [localhost] {see_it_all} ((none)) > show grants\G …
With the next version of MySQL that is MySQL 8, there is a very
nice feature of creating "roles" which can be assigned
certain privileges and then these roles can be assigned to users,
thus helping us in maintaining the principle of least
privilege.
It makes our life as developers and DBAs easier as we do not have
to remember what are the specific privileges assigned to
different users. These "roles" can simply be assigned or
de-assigned from users as necessary.
The setup process is pretty easy and intuitive.
1. Create Role:
CREATE ROLE 'read', 'write', 'read_write';
Which essentially …
One of the most interesting features introduced in MySQL 8.0 is roles or the ability of defining a set of privileges as a named role and then granting that set to one or more users. The main benefits are more clarity of privileges and ease of administration. Using roles we can assign the same set of privileges to several users, and eventually modify or revoke all privileges at once.
Roles in a nutshell
Looking at the manual, we see that using roles is a matter of several steps.
(1) Create a role. The statement is similar to
CREATE USER though the effects are slightly
different (we will see it in more detail later on.)
mysql …[Read more]
Using Vault with MySQL
In my previous post I discussed using GPG to secure your database credentials. This relies on a local copy of your MySQL client config, but what if you want to keep the credentials stored safely along with other super secret information? Sure, GPG could still be used, but there must be an easier way to do this.
This post will look at a way to use Vault to store your credentials in a central location and use them to access your database. For those of you that have not yet come across Vault, it is a great way to manage your secrets – securing, storing and tightly controlling access. It has the added benefits of being able to handle leasing, key revocation, key rolling and auditing.
During this blog post we’ll accomplish the following …
[Read more]This is a blogpost about MySQL 8.0.0, the content here could be subject to changes in the future since this is not a stable release.
This is a feature that will make life the many DevOps and DBAs easier. Believe it or not, a lot of people control access to the database by sharing the same username and password. Which is completely insecure for a number of reasons:
That also means: no more querying to Stack Overflow to get that
giant GRANT statement for each user you need to
create. (Thank you @mwop for reminding me of this).
Creating a Role
This is a group of privileges that will be assigned to users:
CREATE ROLE …[Read more]