Basic question about bash permissions

bash has nothing to do with the underlying POSIX permissions.

From your examples, it appears the mysql wants the user who attempts to log into the root account have elevated privileges.

In your second attempt, you used sudo to elevate your privileges, and then logged into mysql.

You'd have to ask mysql if there is a way to log into the root account without elevated privileges.

Why not just create another mysql user that has admin privileges on the mysql server. I don't know if that will solve the requirement for sudo, but perhaps it will.

Under the covers the mysql code is either using "ssh root@localhost", or using the 'su' command to become the 'root' users (the almost ultimate privileged user on any Unix based operating system).

Since macOS ships with the root account password disabled (for VERY GOOD REASON, as it is a huge security hole), you will never succeed in entering a password for root.

When you use 'sudo' the command that follows sudo is run as 'root', so it does not need to enter the 'root' password because it is already 'root' and mysql is happy.

I very strongly suggest you never ever give 'root' a valid password. That is just asking for security troubles as there are script kiddies on the internet constantly probing systems to see if 'root' will respond to common passwords.

Under the covers the mysql code is either using "ssh root@localhost", or using the 'su' command to become the 'root' users (the almost ultimate privileged user on any Unix based operating system).

It's not the same root. mysql has a root account, similar in nature to the root account in unix.

Those are two different passwords. When you get the prompt that says just "Password:", that is the sudo command. It wants your account password. If your account is an administrator account, then it runs the command.

The next prompt is "Enter password:". That is completely separate and is part of MySQL. Since you are running mysql with "-u root" it wants the password for the MySQL "root" account, which is completely different from the "root" on the Mac.

It sounds like you are entering your user account password for both, that's why it only works with sudo. If you aren't using sudo, then all you need to provide is the MySQL root account password.

man sudo

sudo allows you to execute a command as if you were another user. The 99.44% usage is for that other use to be 'root' the all powerful account that every Unix system has.

sudo checks to see if you are allowed to run sudo by looking in the /etc/sudoers file. The user could be explicitly specified as being allowed to use the sudo command or the user could belong to a group that is allowed to use sudo. On the Mac most users are members of the admin group, which is setup in the macOS /etc/sudoers file.

For authentication, the sudo command makes sure that you are you, by making you enter your account's password, so that if you walked away from your computer and someone walked up and tried to execute a sudo command, they would not know your account password.

On original Unix systems the 'root' user could do absolutely anything. Over the years that has been pared back a bit, and especially with macOS where there are many things that even 'root' is not allowed to do, unless the Mac is booted into a special mode, like when your Mac reboots to install system updates.

Apple found that support costs went up a great deal when users started playing with the 'root' account, either by enabling it, or by using the sudo command. macOS locks down a lot of things so that users cannot shot-themselves-in-the-foot.

Final comment. If you are going to play with sudo or the root account, then make sure you have regular backups, because eventually you will make a mistake and turn your Mac into an overpriced doorstop. I'm speaking from experience. Not with Mac, but with multi-million dollar computer systems where I had administrator privileges. That is the kind of thing that sticks with you. In one instance, I had backups. In the other instance, it was the backups I messed up.

And God forbid that anyone should ever attempt to become bash root. ;-)

There is no 'root' in bash. Bash is a shell. It is not a file system with users and permissions.