Privileges problems with non-root regular user [solved]

[david1212]

I have privileges problems using xampp-win32-1.7.7-VC9.7z. But all is OK in xampp-win32-1.7.3.zip

I unpack and run setup for xampp-win32-1.7.3.zip on windows xp and windows 7.

I put a password on root using => http://localhost/security/xamppsecurity.php <=.

I do the things listed in the code below by using the menus in phpmyadmin and it creates the code and phpmyadmin runs it.

I log in as user ab20 in phpmyadmin. I add data and it works. I try to delete a row and it is denied -- good.

But.

When I try to do the same in xampp-win32-1.7.7-VC9.7z on windows xp and windows 7.

I cannot log in as ab20 with password abc.
I can log in with no password.
but, there is no database ab20 visible.

Any help will be greatly appreciated.

Code: Select all

CREATE DATABASE `ab20` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `ab20`.`peop` (
`aname` TEXT NOT NULL ,
`aadr` TEXT NOT NULL
) ENGINE = InnoDB;

CREATE USER 'ab20'@'%' IDENTIFIED BY '***';

GRANT SELECT , FILE ,REFERENCES ,INDEX ,LOCK TABLES ,CREATE VIEW ,SHOW VIEW ,
CREATE ROUTINE
ON * . * TO 'ab20'@'%' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `ab20` . * TO 'ab20'@'%';

REVOKE ALL PRIVILEGES ON `ab20` . * FROM 'ab20'@'%';

GRANT SELECT ,INSERT ,UPDATE ,REFERENCES ,INDEX ,CREATE TEMPORARY TABLES ,
LOCK TABLES ,CREATE VIEW ,EVENT,TRIGGER,SHOW VIEW ,CREATE ROUTINE,
EXECUTE ON `ab20` . * TO 'ab20'@'%';


[hackattack142]

In MySQL, users with defined hosts take precedence over users with wildcard hosts (%). There is an entry by default that applies to any username for localhost. This is overriding your localhost access which I assume you are trying to do. You do not need to define a user with the wildcard if you do not intend to connect from a remote machine. If you do want to keep the wildcard option, you can simply create another 'ab20' user with the same settings except with localhost as the host. This should fix your issue.

[david1212]

Thank you.
That explains it.