Using Grant And Phpmyadmin

[nortk]

It seems that there have been some posts related to this topic, but none quite answers my question, so here goes.

 

Is it possible, using the SQL window in PHPMyAdmin, to issue GRANT commands? I have created a database, created users, assigned those users to the database, and have tried things like this:

 

>grant select, insert on databasename.* to username identified by 'password'

 

But what I get every time is the message:

 

>Access denied for user: 'myusername@localhost' to database 'databasename'

 

Any thoughts on what I'm doing wrong? Thanks for your help!

 

Norm Krumpe

[rayners]

First, the standard MySQL question: did you use the correctly formatted username and databasename? (i.e. prepend 'accountname_')

 

If so, I'm guessing the accounts setup through CPanel don't have permission to use the GRANT command.

[nortk]

Thanks for a quick response and, yes, I did the necessary prepending. So, based on your guess, it looks like I can't do it.

 

Being new to this (surprise, surprise), does the fact that I can't grant privileges via phpMyAdmin imply that I won't be able to grant privileges via a little PHP scripting? Or, are these two things independent?

 

Thanks again.

[rayners]

If you can't do it through phpMyAdmin, you won't be able to do it through a seperate script. The permission to use GRANT lies with the database user, not with the script running as that user (if that makes any sense outside of my head )

[Lianna]

I was able to successfully use grant in the SQL window of phpMyAdmin.

 

I was installing a script that I needed SSH for...which I don't have,nor do I want. I just copied the grant line from the script and pasted it, run, ok. All's well. Just for reference, here is the line that I used:

 

grant all privileges on phphelpdesk.* to phphdadmin@localhost identified by 'phphdpassword';

 

Replace all blue with the prepended db, prepended username, and password.

 

You may want to try to get the grant to work successfully as all privileges just to see if it's the priv's identified (insert, select).

 

Lianna

[nortk]

Tried your suggestion, and still get the same

 

Access denied for user: 'prepend@localhost' to database 'prepend_dbname'

 

Still stumped, but thanks for the suggestion.

 

Norm

[nortk]

In fact, and maybe this is related...

 

I also can't use phpMyAdmin to create a database. It's giving me the same error message described in my previous post.

[borfast]

Those errors are all because your user doesn't have permissions to perform those operations.

 

For isntance, to create a new database you have to do it through CPanel's MySQL manager.

 

Raul

[rayners]

I'm not surprised about that. I think it would be a bad idea to give users direct access to creating/deleting databases in MySQL. From what I remember, it's basically a yes or no situation. Either you can, or you can't. And if you can, not only can you create your own databases, but you can also delete other people's databases. Besides, there needs to be a way to enfore the account_database formatting rule, so that level of access has to be solely in CPanel.

[nortk]

Those errors are all because your user doesn't have permissions to perform those operations.

 

Ok, but my "user" is me. I created the database, say D. I created some user, say U. I added user U to database D.

 

Shouldn't it be possible for me to specify what privileges U has on my database? That's what's confusing me. I realize that I might not want U to grant or revoke privileges for another user, but shouldn't I be able to grant or revoke privileges to/from U, since I created U and the database myself?

 

Thanks for bearing with me on this.

[borfast]

When I said "your user" I meant you, as in "your username", or "your entity inside the webserver"

 

Anyway, what you say makes sense, I guess.

I don't know that much about MySQL administration, though, but from what I know, for you to be able to do that, you'd have to have access to the "mysql" database, where all the important information (usernames, passwords, permissions, etc) is kept, so it also makes sense that you can't change that info, because if you could change it, it would mean that you could change other users' info too.

 

Raul

[Paul]

well I got asked so I'll answer ... I don't think it can be done I've played with it since I found the information in the documentation but it appears that we need to have access to a secured database which we just don't have. The only thing I can think of regarding Lianna's being able to use the GRANT statement is that she has some kind of 'superuser' access that we don't . I am probably missing something incredibly obvious here so if anyone, anywhere has a sample script that has successfully run on the TCH user account PLEASE post it. I'm very uncomfortable putting a 'master' password in my php scripts but so far I haven't found a way around it.

 

I know this doesn't help but maybe it will inspire the head guru to jump in with some ideas on how to secure the databases, or explain why it's not necessary.

 

Thanks in advance for any input on this issue!

 

Later,

Paul

[nortk]

Good to know I'm not alone!

[rayners]

I don't think anybody here would argue against limiting permissions on a database users to only what it needs and nothing more.

 

For the most part, though, any non-homegrown scripts you install will probably need almost all of the permissions available. Invision Board, for example, has a web-based install and admin panel, in addition to the forum itself, therefore it needs create, insert, select, delete, drop, etc. permissions.

 

Out of curiousity, my memory is a little fuzzy about when you started this discussion last time, Paul. Were you uncomfortable putting passwords in your scripts because it gave the script too much acess or that you were concerned that somebody might be able to take a look at the file and get your password out of it?

 

And, I imagine that technically (from the database's perspective) you (i.e. your user) do not actually create the databases and users. I'm guessing CPanel, like Invision Board, is using an account seperate from yours that has full access to the MySQL server so that it can create databases and users.

 

Or am I just babbling a little too much this morning?

[Lianna]

First, believe me folks, I do NOT have any superuser privs in my account. And I like it that way. I was just explaining the same premise (different subject) to HeadGuru this morning. I like having what you have. It makes my ability to duplicate your issues, solve your issues, test your issues all the more valid and supportable. See what I mean? If I had some superuser powers, my ability to support you or give step-by-step instructions would be a waste of time in that your account would not necessarily behave like mine. ..........and, what the heck would I do with those powers if I had them? I've said before: "I don't have ssh access and wouldn't know what to do with it if I did." Truth.

 

And, I imagine that technically (from the database's perspective) you (i.e. your user) do not actually create the databases and users. I'm guessing CPanel, like Invision Board, is using an account seperate from yours that has full access to the MySQL server so that it can create databases and users.

Rayner, I think this is right on.

 

I am on my way back to my db to verify my earlier comment that the grant statement did indeed work as I purported.

 

Lianna

[Paul]

Thanks for the comments Rayners and Lianna. First your point Rayners ... actually all the scripts ask you to modify the configuration file to reflect the username, password and database name (or preface) that the script is to use to create and maintain the database. Now this is a certain level of security of course, since the only databases that can be accessed with that password are the one's that have been created by the application or program. My situation is this, I have a database with about 40 tables in it. The application I'm creating has a username/password that allows access to the database and all associated tables. Now "in theory" this password is stored in $_SESSION variables which should never be accessible to the users. Keyword here is "theory." My concern is that if some evil person somehow gained access to the raw PHP source to the application (don't ask me how it could be done, I'm not a cracker and have no desire to be one), that person would now have the ability to directly access the database and do whatever they wanted to do to the tables. Mind you, my application is far from a "top secret" type thing, but it is going to be a fairly sophisticated and database intensive system and I want to protect my data as much as possible. My goal in life was to have a username/password that would allow all the normal Db operations necessary but could be restricted from operations like "EMPTY" or "DROP" and such. I know it can be done in mySql but not the way it's set up right now.

 

I guess the point is this, the reason we can't do what I'd like to do is *probably* that in order for us to do it, TCH would have to allow each of us "full" privleges to the 'user' database, the same privleges we have to give the users to access to our databases. (Did that make sense to anyone else? LOL)

 

It's probably a moot point, I doubt that anyone is going to find my site valuable enough to justify breaking in. If I ever do carry super sensitive information I'll be much more concerned and probably be forced to going to a dedicated server to get full administrative privileges.

 

Anyway, that's just my thinking on the issue.

 

Later,

Paul

[borfast]

OK, first of all, IPB (Invision Power Board) doesn't need any special priviledges besides the regular create table, drop table, insert, select, modify, etc...

 

As for the password, you always have to provide it somewhere in your scripts, or else they won't be able to access the database. The way I like doing it is having a config.inc file somewhere, which is included in every scipt that needs to access the DB.

That way you don't have to put it in every script nor in a session variable nor even give it to your users.

 

As for the reason why we can't set those permissions, yes, that's because we don't have access to the "mysql" database, which is where all that information is stored and fetched when needed.

 

Raul

[rayners]

As for the password, you always have to provide it somewhere in your scripts, or else they won't be able to access the database. The way I like doing it is having a config.inc file somewhere, which is included in every scipt that needs to access the DB.

That way you don't have to put it in every script nor in a session variable nor even give it to your users.

You might want to either rename that file to .php or the like (so the server will attempt to parse and render it instead of just passing the text of the file back to the browser) or move it to a directory not viewable from the web.

 

Just a thought.

 

Anyways, yeah, I agree. There is no need to be passing passwords around like that. Database users and system/script/whatever users should be seperate entities, at least in my opinion. You have a db user that has enough permissions to do whatever the system needs, and then system users that have their permissions defined and controled within the system, totally seperate from the database.

[Paul]

OK, first of all, IPB (Invision Power Board) doesn't need any special priviledges besides the regular create table, drop table, insert, select, modify, etc...

I consider the ability to "DROP" a table a pretty special privilege, and that's exactly what I'm trying to keep certain user's from doing This is just a general security issue, not geared to any single application. Again the point is, the script has to have a username/password, that's a given, what I don't want to have happen is, through some hole that I missed in my code, someone entering the sql query code that would drop the table. Yes, it would be my fault for now 'safety-checking' all user input but it would sure be nice if I knew that the username/password that was logging into the Db didn't have the permissions to do it at all, that's all. Again, not a major motion-picture, just worrisome.

 

Later,

Paul