×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Admin permissions quandry for MySql permissions schema

Admin permissions quandry for MySql permissions schema

Admin permissions quandry for MySql permissions schema

(OP)
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?

RE: Admin permissions quandry for MySql permissions schema

Quote:

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?

RE: Admin permissions quandry for MySql permissions schema

(OP)
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.

RE: Admin permissions quandry for MySql permissions schema

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

RE: Admin permissions quandry for MySql permissions schema

Quote (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.

RE: Admin permissions quandry for MySql permissions schema

(OP)
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.

RE: Admin permissions quandry for MySql permissions schema

Quote (spamjim)

I would back up and explain these errors first.

Quote (spamjim)

Those error messages are still missing from this discussion.

Nobody here knows what those error messages are, still.

Quote (DBDivaAuto)

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.

RE: Admin permissions quandry for MySql permissions schema

(OP)
#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.

RE: Admin permissions quandry for MySql permissions schema

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.

RE: Admin permissions quandry for MySql permissions schema

(OP)
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

RE: Admin permissions quandry for MySql permissions schema

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

RE: Admin permissions quandry for MySql permissions schema

Quote (DBDivaAuto)

I have a MySql db on an external server

Quote (DBDivaAuto)

...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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close