[selowitch]

Hi.

I want to grant full permissions to myself on my ISP's installation of MySQL.
To do so, I suppose I need to login as root and then do something like

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

But this command produces an error:

mysql -u root -p

It says "Access denied to root@locahost using password YES"

So now what?

[Phil Sherry]

When setting myself up on a fresh OS X installation, I use the following three steps:

# 1. set the admin password to new_password
mysqladmin -u root password new_password

# 2. login to the mysql shell
mysql -h localhost -u root -p

# 3. give the user the right permissions
GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY 'new_password';

I just use the default mysql user, so that works for me.

[selowitch]

Well, FWIW I'm using Linux, not OS X, for my webserver, and it's not my own server but one maintained by my ISP.

I'm wondering two things:

1) I assume/hope that changing the root password will not affect the ability of people visiting my web pages to view data from the databases? I'm actually wondering which "account" is used for calls from my web pages, if any; 2) Could it be that my ISP has to grant me more privileges or that they have to change the root password for me?

[Simon Mundy]

Originally Posted by selowitch

Well, FWIW I'm using Linux, not OS X, for my webserver, and it's not my own server but one maintained by my ISP.

I'm wondering two things:

1) I assume/hope that changing the root password will not affect the ability of people visiting my web pages to view data from the databases? I'm actually wondering which "account" is used for calls from my web pages, if any; 2) Could it be that my ISP has to grant me more privileges or that they have to change the root password for me?

Is it a shared box? If it is, there's no way you'll ever get the root password! That doesn't stop you getting an administrative account, though, just for your own database. Your ISP should set you up with an account that allows full access (drop, alter, create temporary tables, etc) just for your database(s).

It certainly doesn't affect the ability for visitors viewing your website. PHP is middleware for the MySQL client, and all it does is use your u/p for access.

This can be either your 'admin' account to access the database (not really recommended in case anyone ever needs to share your code at any stage), or you can have your ISP also set up a more limited user account (ADD, INSERT, UPDATE, DELETE, SELECT only) simply for web access.

[selowitch]

Well, if I can't have a root password how the heck do I make it so that I get rid of the $*$@! "no privileges" message on my phpMyAdmin screen, how do I gain access to the Privileges tab (which is now inviisble, and how do I overcome the infuriating error I get "1045 -- access denied for user@localhost" when I try to import a text file.

It makes no sense --- how is it that I'm authorized to drop an entire database but somehow I can't import a file into it. That is bizarre.

[Simon Mundy]

Originally Posted by selowitch

Well, if I can't have a root password how the heck do I make it so that I get rid of the $*$@! "no privileges" message on my phpMyAdmin screen, how do I gain access to the Privileges tab (which is now inviisble, and how do I overcome the infuriating error I get "1045 -- access denied for user@localhost" when I try to import a text file.

It makes no sense --- how is it that I'm authorized to drop an entire database but somehow I can't import a file into it. That is bizarre.

OK, let's say your ISP is the 'root' user. You have a database on the server called 'selo' and you need a user account that is unrestricted on that database.

All your ISP needs to do is perform:-

%>GRANT ALL PRIVILEGES ON selo.* TO 'user'@'localhost' IDENTIFIED BY 'yourpassword' query;

This should give you all the flexibility you need. You shouldn't need to access the 'privileges' tab at all on phpMyAdmin, and since you don't have privileges for privileges(!) then it's an exercise in futility.

It sounds like the 'FILE' privilege wasn't added to your user abilities - that will be simple to remedy if the above query is applied.

You'll notice the above only lets you perform actions on your database 'selo' and all the tables contained within it (selo.*) - you shouldn't have any privileges to access any other databases on that server unless your admin REALLY trusts you

[selowitch]

I'll talk to my sysadmin and report back. Thanks!

EDIT: While I'm waiting, my ISP's main contact person insists that this is something I can do from the command line, but I'm inclined to doubt her. I have asked her before to have her sysadmin upgrade my privileges and she has resisted, saying it's my responsibility. At the same time, she also insisted that if I wanted to enable the relational features of phpMyAdmin, I'd have to install my own copy, which I did. I now have the relational features (like drop-down menus for picking entries for other tables identified with an id number, which is very handy!) but still can't import this text file I really need to upload.

Is she being a jerk or is she right and *I'm* the jerk?

FINAL EDIT: Well, neither one of us is the jerk, it turns out. Under the shared hosting arrangement I have, they don't allow me access to the root password and I can't create accounts or databases without going through the admin, hence the "No Privileges" messages in phpMyAdmin, which is expected and normal in this circumstance.

It's annoying that I can't do file uploads, but my workaround was simply to dump the stuff I needed into a text file and with the magic of search-and-replace, issue a whole bunch of SQL statements:

UPDATE items SET ebay_id = 3456 WHERE lot_number = 34;
UPDATE items SET ebay_id = 3457 WHERE lot_number = 35;
UPDATE items SET ebay_id = 3458 WHERE lot_number = 36;
UPDATE items SET ebay_id = 3459 WHERE lot_number = 37;
etc.

I copied and pasted that into the textarea under the SQL tab in phpMyAdmin and it worked great, even safely ignoring those instances where there was no "lot number x". So, not a bad workaround.

I wonder if when I next shop around for web hosts, I should seek out a dedicated server where I can have fuller permissions. Of course, as Uncle Ben says, "with great power comes great responsibility" — but I think I'm close to being ready to handle it.

Thanks for the help!

[selowitch]

*bump*

[madmacgames]

what admin control panel are they using? cPanel? Plesk? something else?

[selowitch]

Originally Posted by madmacgames

what admin control panel are they using? cPanel? Plesk? something else?

Beats me.

[madmacgames]

you said, "I can't create accounts or databases without going through the admin"... so what admin software do they provide for you to manage your accounts and databases through?

[selowitch]

You asked, "what admin control panel are they using (emphasis mine)?" The answer is I don't know. If you read the rest of this thread, you'll see that I'm using phpMyAdmin. By "the admin" I meant the system administrator.

Sorry if my language was imprecise.