Member Avatar for berserk

My problem Im almost certian is a permissions issue but im still very new to this so bare with me. I have a server running the latest mysql with mysql workbench installed and im tryign to modify the users and privilieges for either my one table or all my tables, im not sure what i need to do to get this thing working the way i want.
I have the ability to read from the database and update it thats working fine also i am about to create tables too thats something i tested out but what i cannot do is insert, which is a huge problem for me. My server is set up on IIS and i am using PHP with phpmyadmin to manage the database locally this is mainly for just quick glances to see if my submitting forms has done anything.

I dont know what i really need to do in the situation, im sure there is a permission that i have not configured correctly somehwere stopping me from inserting data into the database at least remotely over the internet.

If i did not give enough information i apologize because im literally learning this system as i go. Any help would be vastly appreciated, thank you for your time.

  • 2 Contributors
  • 2 Replies
  • 381 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by berserk

Dani AI

Generated

— building on ’s hint about accounts, the most likely causes are (a) MySQL matched a different account than you expect (anonymous or a host-specific user), or (b) the server/network is using a different connection path (socket vs TCP) so a different grant set applies. The first diagnostic is to discover which account MySQL actually authenticated for the failing remote request.

Run these inside the mysql client and compare results:

SELECT USER(), CURRENT_USER();

USER() shows the client-supplied login string; CURRENT_USER() shows the account MySQL authenticated. If CURRENT_USER() is an anonymous user or a different host than you expect, inspect that account’s grants:

-- replace 'user'@'host' with the CURRENT_USER() value
SHOW GRANTS FOR 'user'@'host';

If INSERT is missing, grant it (replace DB, user and host with your values; prefer a specific IP or hostname instead of %):

GRANT INSERT ON your_db.* TO 'webuser'@'203.0.113.5' IDENTIFIED BY 'StrongPass!';
FLUSH PRIVILEGES;

Checklist of practical points to verify (each is a common trap):

  • Confirm the application actually uses the intended username/password (log or error-report the credentials used for the failing attempt).
  • Local tools can connect over the Unix socket or localhost and match a different account than remote TCP clients — that difference often explains “works locally but not remotely.”
  • Check server networking: ensure MySQL is not bound to loopback only and that skip-networking is not enabled; verify port 3306 is listening and allowed by any firewall.
  • Capture DB errors in the application (example for mysqli):
$mysqli = new mysqli($host,$user,$pass,$db);
if (!$mysqli->query("INSERT ...")) error_log("Insert failed: ".$mysqli->error);

If anonymous accounts exist, list and remove them after verifying what they are:

SELECT User,Host FROM mysql.user WHERE User='';
DROP USER ''@'%';   -- remove specific anonymous entries as appropriate
FLUSH PRIVILEGES;

Security note: avoid using root for application traffic, restrict host parts, use least-privilege accounts, and consider SSH tunnels or an API rather than exposing MySQL to the public internet.

Recommended Answers

All 2 Replies

Member Avatar for Member #120589

Out of the box, the default user should be username='root' and password=''. This user should have ALL permissions. That user can GRANT or REVOKE permissions for other users.

Member Avatar for berserk

here are all the users i have currently set up
(!) <anonymous %
me %
root localhost
root 127.0.0.1
root ::1

all of these users have all global priviliges except anonymous but theres no way to change anonymous (the server made that one)
my problem might have something to do with anonymous but i am honestly not sure at this point.

i am currently using MySQL workbench 6.3 with mySQL server version 5.6 with a minimal changes made

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.