MYSQL privileges + WEBMIN

[caicedo2040]

Hi,

On Webmin, some values were changed for root user in MYSQL DATABASE SERVER tool, so when I try to access database with root user, I only see databases 'information_schema' and 'test', but I'm not able to see databases as 'asterisk' and the others.

How can I restablish all access for root user to all mysql databases?

I tried with user 'cron', to give all privileges to 'root' with GRANT instruction, but I received following message:
ERROR 1045 (28000): Access denied for user 'cron'@'localhost' (using password: YES)

Also, If I try to create a new user, but this procedure was not allowed neither.

Thanks in advance.

Juan

[williamconley]

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root

this should give you command line access (and possibly webmin access) to modify the "root" user in any way you see fit.

[caicedo2040]

Hi William,

thanks for your answer.

The sequence gave me access for command lines and webmin, but when I try to restore privileges on root or other user, it shows following message:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

If my sql is started, the user is restricted again.

On webmin, I see all databases, but after try to make one single change, it returns again to the last restrictions state showing no permission alarm.

At the end no change is allowed.

Please let me know if I'm missing something else.

Thank you

[williamconley]

lol

try (with both logins) creating a new user with full privileges to log in as

you can also look into editing the "mysql" users table directly and altering privleges there

[caicedo2040]

HI,

I know it sounds funny, but already tried to add new users and login after with them. But after login and make changes I receive:

DBI connect failed : Access denied for user ''@'localhost' to database 'mysql'

strange thing is <''@'localhost'> restriction in the message when I'm logged on to mysql for example with 'root' or 'newuser' created...

Thanks

[williamconley]

did you try modifying the users table in the mysql database?

[caicedo2040]

Hi,

yes, I tried that.

Now i'm doing other tests. I'm performing queries using new users through a php simple code. It doesn't show me error connections, but no information is being showed related to queries sent.

Accessing databases it shows me restrictions.
Making queries by php it doesn't show restrictions.
No information is showed after a query.

I think I'll need to reinstall the application and try not to play with root user. Dialer function is working fine, but main idea of accessing database is to show to the agents information of customers called and relevant comments inserted during a call (I see they are on vicidial_list table).

Or is it already installed a module like that on the application?

Thanks

[williamconley]

mysqldump all your databases and set up mysql all over again. the process will allow you to re-set your root user.

then you can re-create your cron and other necessary users without weirdness.

[caicedo2040]

Hi,

I performed following sequence:
as you recomended:
1. # /etc/init.d/mysql stop
2. # mysqld_safe --skip-grant-tables &

then as follows:
3. Create user and obtain message restriction
4. # /etc/init.d/mysql start
5. Perform 1 and 2.

6. Modify new users permissions and obtain message restriction
7. Perform 1, 2 and 4.

"After doing this, I'm able to modify databases and tables from php + mysql with the new user created".

dumping databases could be a little hard because databases I want to access are the default installed with vicidialnow application like asterisk db. So for this case and having the iso, it would be more faster to reinstall vicidialnow . In both cases I will lose data.

I can now work on the reports I want to show based on vicidial_list table.

I'll install application on a more powerfull server, and then will be more easy to copy database information from one server to other, and get ride of this issue.

Thank you very much for your help.

[williamconley]

you will lose NO data if you do a mysqldump and then reload and then put the data back.

you could also use the ADMIN_backup script for this task.