Admin permissions quandry for MySql permissions schema

[DBDivaAuto]

I have a MySql db on an external server where we will be putting an Intranet Site. I have 30 tables built out all under the site /db admin login ID. I finally got to where I only had permissions errors. Did some research and found an article re: the Information_Schema DB and the table SCHEMA_PRIVILEGES.

Now the only grantee is my admin account. The Is_Grantable is set to NO and needs to be YES so that SELECTS, UPDATES, INSERTS and DELETES can work.

I wrote a simple update to SET Is_Grantable to 'YES' WHERE Is_Grantable = 'NO ' and it says my admin account doesn't have privileges to change the table.

So how in the HECK does one change the table when HELLO you have to be able to look up and write to the tables?

 

[spamjim]

I finally got to where I only had permissions errors

I would back up and explain these errors first.

Are you truly the admin of the MySQL server (root) or are you just operating an "admin" account that administers a single database on the server?

 

[DBDivaAuto]

Jim - I am the only one who currently has access to this account and the user name/ password is the only account with admin privileges on the server. My assistant could have access with this account as he knows the login info but he refrains from database anything because it isn't
his strong suit.

I created the DB with this account, created all tables under this login, and had an ODBC working until about 4 weeks ago with this login. It would be what I would call the Windows God account.

 

[DBDivaAuto]

And yes - before you ask - the account has the same credentials for DB

 

[spamjim]

I would back up and explain these errors first.

Those error messages are still missing from this discussion.

To make sure we are conversing at the same level, are you understanding that there would be a 'root' user and your 'admin' user in the information_schema.USER_PRIVILEGES table? If your 'admin' account does not have sufficient privileges for what you want, you need to control the server as 'root', granting to the 'admin' account. (if you cannot see 'root' in this table, then you really do not control the DB server...contact the server admin to get the expected permissions for your own account)

If 'admin' and 'root' are the same user in the system, you have bigger security issues to worry about. If so, reset your thinking on the user structure.

 

[DBDivaAuto]

I haven't worked with mySQL DBs in years but do have 20 years with SQL. I can tell you this is on a dedicated server I set up and there are no other users.

On the Information_schema.USER_PRIVILEGES table only the admin acct I set up initially with this DB is present.

 

[spamjim]

I would back up and explain these errors first.

Those error messages are still missing from this discussion.

Nobody here knows what those error messages are, still.

On the Information_schema.USER_PRIVILEGES table only the admin acct I set up initially with this DB is present.

Then you probably need to log in as root, not 'admin'.

Again, without posting the specific error messages, this is just a pointless guessing game.

 

[DBDivaAuto]

#1044 is the error

Don't worry Jim - If I have to wipe the whole frickin thing I will.

Found out Plesk resets the root login and password to whatever you set as administrator. My thought now is that when I went on vacation for 2 weeks the password must have expired and is locking me out.

 

[spamjim]

1044 is the type of error but it is not the error message.

Example:
#1044 - Access denied for user 'somebody'@'12.10.25.1' to database 'crm'

Sharing an error message could have saved a lot of typing.

There are always ways to reset/update a MySQL root password. Look to that option before wiping everything out.

MySQL can authenticate based on the user and their location. So in the example error message above 'somebody' may not be able to access the 'crm' database from 12.10.25.1, but they may be successful if they log in from localhost... or any other specific IP address granted in the system. I understand that in MS SQL, there is no location authentication, unless you block the user via firewall...elsewhere in the system.

 

[DBDivaAuto]

I am trying to reset the password - but it tells me I need the Mysql command line utility - I don't have and installing has become quite time intensive. I thought I had it installed and it tells me no connection possible.

I am forced to using a DB with mediocre admin compared to SQL and getting it fixed is going to be another reason why MySql sucks

 

[spamjim]

Plesk should already have everything you need, including the mysql command line utility.

If your installation of mysql sucks, consider other options to use mysql (or mariadb) on Windows.

Portable mysql server options:

https://www.apachefriends.org/index.html

http://www.uniformserver.com/

Mysql GUI tools (also portable-ish):

https://github.com/webyog/sqlyog-community

https://www.heidisql.com/

https://dev.mysql.com/downloads/workbench/6.2.html

 

[spamjim]

I have a MySql db on an external server

...it tells me no connection possible.

Operating as root assumes you have local access to the machine. If this is an external server, you might need to take a trip (physical or virtual) to get on it.