[RESOLVED] Changing Privileges via code

[Nightwalker83]

Hi,

This question follows on from this thread (http://www.vbforums.com/showthread.php?t=577057&highlight=privileges) but it's about the code rather than the program.

Is it possible to change the user privileges of a database via code rather than having to manually add or adjust them?

Thanks,


Nightwalker

[kows]

you can run queries to grant privileges if you need to, though if you're running on a shared host this may not be possible.

you will have to run the two following SQL queries:

GRANT select,insert,update,delete,create,drop ON database.* TO username@localhost identified BY 'password';
FLUSH PRIVILEGES;

note that the host (localhost) might need to be changed, or can be set to any host by setting it to "%" (including the quotes -- though, this is not exactly advised). and you can specify which tables this user has these privileges on by removing the "catch all":

GRANT select ON database.table_name TO username@localhost identified BY 'password';

make sure you flush your privileges regardless of what you do, assuming it all works. you probably need a root database account to be able to do this.

[SambaNeko]

You can also use "REVOKE (http://dev.mysql.com/doc/refman/5.0/en/revoke.html)" (in place of "GRANT") with similar syntax.

[Nightwalker83]

This is the script (http://www.vbforums.com/showpost.php?p=3633357&postcount=41) I will be using to create the database. Although, there are 2 or 3 other scripts that not only add the data to the database but check to see if the table and fields needed at the time are available before adding the data and create them if they are not.

Edit:

I found an example of what kows said:


source: http://www.trap17.com/index.php/Adding-Users-Databases-Phpmyadmin_t31803.html
GRANT privileges
ON databasename.tablename
TO username
Identified by 'password';

example:

GRANT SELECT, INSERT //or GRANT ALL to grant all privileges
ON DB1.*
TO AMEZIS
IDENTIFIED BY 'PIZZA';


Is it also possible to create a new user via code? I have searched on google but couldn't find any info about creating a new phpadmin user via code.

Edit:

Yes it is! One way to do it is add a user to the mysql user table. Here (http://www.databasef1.com/tutorial/mysql-create-user.html) is the source of the info and below is a test script I wrote.


<?php
// Database connection variables
$dbDatabase = "mysql";

//connect to db
$conn = mysql_connect("localhost", "root", "");
if (!$conn) {
die("Connection failed: " .mysql_error());
}

//select database
if (mysql_select_db($dbDatabase, $conn)) {
echo ("Database selection successful!");
}else {
die ("Could not locate test database" .mysql_error());
}

//insert data into tables
$insert = "INSERT INTO user (
Host,
User,
Password,
Select_priv,
Insert_priv,
Update_priv,
Delete_priv,
Create_priv,
Drop_priv,
Reload_priv,
Shutdown_priv,
Process_priv,
File_priv,
Grant_priv,
References_priv,
Index_priv,
Alter_priv,
Show_db_priv,
Super_priv,
Create_tmp_table_priv,
Lock_tables_priv,
Execute_priv,
Repl_slave_priv,
Repl_client_priv,
Create_view_priv,
Show_view_priv,
Create_routine_priv,
Alter_routine_priv,
Create_user_priv,
Event_priv,
Trigger_priv,
ssl_type,
max_questions,
max_updates,
max_connections,
max_user_connections
)
VALUES (
'local', 'admin', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '0', '0', '0', '0'
)";
if (mysql_query($insert, $conn)) {
echo ("Insert query successful!");
}else {
die ("Database query failed: " .mysql_error());
$query ="FLUSH PRIVILEGES";
}
?>

[Nightwalker83]

Can someone tell me why the above php code is only working some of the time when I run it from a script? If I paste the sql into phpmyadmin it works ok.

[SambaNeko]

mysql_query() doesn't support multiple queries - you've got an INSERT and a FLUSH, you can only do one at a time through mysql_query().

[Nightwalker83]

mysql_query() doesn't support multiple queries - you've got an INSERT and a FLUSH, you can only do one at a time through mysql_query().

Ah ok! Can you tell me what I flush does and how to use it? I assume that a flash wipes everything so that you can reuse the tables, etc without having to recreate them?

[SambaNeko]

MySQL FLUSH (http://dev.mysql.com/doc/refman/5.0/en/flush.html)

FLUSH does not delete anything from your tables, it "clears or reloads various internal caches used by MySQL." (see the link above for more info) If you want to clear out all rows of a table, you'd use TRUNCATE (http://dev.mysql.com/doc/refman/5.0/en/truncate.html).

[Nightwalker83]

MySQL FLUSH (http://dev.mysql.com/doc/refman/5.0/en/flush.html)

FLUSH does not delete anything from your tables, it "clears or reloads various internal caches used by MySQL." (see the link above for more info) If you want to clear out all rows of a table, you'd use TRUNCATE (http://dev.mysql.com/doc/refman/5.0/en/truncate.html).

Thanks! I edited my code in post #4 with the new code which works. I noticed in the old code that there was a small typo I was missing a ")" as well as the flush.