Do mysql root user always come with all privileges as default?

Question

I'm following this answer to set up my root password on Ubuntu 16.04. msql --version gives me this:

mysql  Ver 15.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Following the answer, when I type UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; I get this:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I discovered when I type the same command with the same password it gives me this:

Query OK, 0 row affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Which I thought would be correct. Then I type the last two commands and when I type mysql -uroot -p and then password I still get this:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Is there any extremely rookie step I'm skipping? Or any command to output to you more data?

UPDATE

When I type sudo service mysql status I get this:

   Active: active (running) since Sáb 2017-11-11 00:44:57 BRST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 4808 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 5330 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─5358 /bin/bash /usr/bin/mysqld_safe
           ├─5502 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --p
           └─5503 logger -t mysqld -p daemon error

Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] InnoDB: 128 rollback segment(s) are active.
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] InnoDB: Waiting for purge to start
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.0 started; log sequence nu
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] Plugin 'FEEDBACK' is disabled.
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] Server socket created on IP: '127.0.0.1'.
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin spe
Nov 11 00:44:56 acer mysqld[5503]: 171111  0:44:56 [Note] /usr/sbin/mysqld: ready for connections.
Nov 11 00:44:56 acer mysqld[5503]: Version: '10.0.31-MariaDB-0ubuntu0.16.04.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 16.04
Nov 11 00:44:57 acer mysql[5330]:    ...done.
Nov 11 00:44:57 acer systemd[1]: Started LSB: Start and stop the mysql database server daemon.

As showed here I saw my log should be normal. Is that correct?

UPDATE

This answer worked for me. But looking for root privileges, I ended up on this question and it says "My root user doesn't have all privileges". Is this a standand? If yes, isn't root user supposed to have all these privileges? Is it doesn't, why? Is this a secure issue?

Don't confuse Ubuntu's "root" with MySQL's "root". – Rick James Nov 13 '17 at 18:31 — Rick James, Nov 13 '17 at 18:31

score 2 · Accepted Answer · answered Nov 11 '17 at 04:05

2

My understanding is that, by default, root@localhost has full permissions and root@% (remote connecting as root) doesn't. This is a good thing... in day to day database use, you shouldn't be using the root account.

If you need to connect remotely with super-user privileges, I'd suggest creating an admin account which can do as much as it needs to. (You'd still be able to remote in and access the root account.) Specific security provisions (what rights admin would need) would probably be a separate question-- possibly an unanswerable question, unless you can be very specific about your use case.

answered Nov 11 '17 at 04:05

Brian Dewhirst

307
3
7

But from my story I can't tell that as `root@localhost` I had full permission. As you can see I was trying to use `root@localhost` and still wasn't allowed to log in. – rado Nov 11 '17 at 12:58
I don't know what you changed that made it work, but setting up MySQL often also requires tinkering with firewall settings. (If you made no changes to port/firewall settings, it wasn't that.) – Brian Dewhirst Nov 11 '17 at 14:03

score 0 · Answer 2 · answered Nov 11 '17 at 02:36

0

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your New Password';

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Your New Password');

answered Nov 11 '17 at 02:36

Hiroto

133
2
13

I edited my question to specify my new doubt. Thanks for your time and patience. If you know the new question, feel free to edit your answer – rado Nov 11 '17 at 03:14

Do mysql root user always come with all privileges as default?

2 Answers2