<<

. 100
( 132 .)



>>

Happily, MySQL has some built-in statements that make user administration a
whole lot easier. Knowing the grant and revoke statements saves you from having
to send individual queries.

The grant statement
Before we get into specifics of this statement, take a look at the statement that grants
all rights on the database named guestbook to user jim; jim™s password is pword.
Appendix E: MySQL User Administration 603

mysql> grant all on guestbook.* to jim@localhost identified by
“pword”;

This command makes all the necessary changes to the user and db tables.
The first part of the grant statement can take the argument all (which must be
followed by WITH GRANT if it™s really to grant all privileges), or it can take any of
the options listed in the user table. Most often you are granting rights to use SQL
statements (select, create, alter, delete, drop, index, insert, and update).
The second portion of the grant statement (on guestbook in the example) iden-
tifies where privileges are to be applied: universally, to a single database, to tables,
or to columns. Table E-1 shows how to indicate where privileges should be applied.


TABLE E-1 SETTING PERMISSIONS

Identifier Meaning

Rights are universal; inserted into the user
grant all on *.*
table
Rights apply to all tables in a single database
grant all on database.*

Rights apply to a single table
grant all on database.table_name

Rights apply only to specific columns in a
grant all(col1, col2)
specific database and table
on database.table_name




The third portion of the grant statement (to jim@localhost in the example)
indicates the user to be given access. As we mentioned earlier, MySQL needs both a
name and a host. In the grant statement these are separated by the @ symbol.
Finally, the identified by portion of the grant statement gives the user a
password.
Here are a few more examples of grant statements:

grant select, update, insert on guestbook2k.guestbook to
alvin@localhost identified by “pword”;

The preceding statement allows alvin to view, update, and insert records into the
table guestbook in database guestbook2k.

grant select, update (name, url) on guestbook2k.guestbook to
chipmunk@localhost identified by “pword”;
604 Part V: Appendixes

With the preceding statement the user can view and update only two columns
(name and url). No deletes or inserts are allowed.

grant all on *.* to josh@localhost identified by “pword” WITH GRANT
OPTION;

The preceding statement gives this user all privileges, which means that josh@
localhost is even allowed to grant privileges to other users.


The revoke statement
If you want to remove some of a user™s privileges, you can use the revoke statement.
To remove shutdown privileges from a user who had been granted all privileges, like
josh in the preceding example, you can run the following:

revoke Shutdown on *.* from josh@localhost;

Notice that the word from is used in the revoke statement in place of to.
Otherwise revoke works just like grant.


To remove a user entirely you must run a delete statement against the user
table. Because the user is identified by a name and a host, the following
should do it:
delete from user where user=™username™ and
host=™hostname™




Viewing grants
You can use the SHOW GRANTS statement to see the exact grants available at a given
time. All you need to know is the username and host.

mysql> show grants for jayg@localhost;

+-------------------------------------------------------------+
| Grants for jayg@localhost |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON my_test.* TO ˜jayg™@™localhost™ |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
Appendix E: MySQL User Administration 605


Reloading grants
The grant tables are loaded into memory when the MySQL daemon is started.
Changes made to the grant tables that do not make use of the grant command do
not take effect until you tell MySQL to reload the grant tables. You can do this in
the shell with the mysqladmin program:

shell> mysqladmin flush-privileges

or in the mysql client with the flush privileges command. Just run:

flush privileges
Appendix F

PHP Function Reference
PHP CONTAINS MORE FUNCTIONS than could possibly be listed in this book. The follow-
ing tables present many of the most commonly used functions available as of PHP
version 4. To keep up on exactly what™s available in PHP, and to check out what
new functions are available in PHP 5, make sure to check in with the online docu-
mentation: http://www.php.net/docs.php.


TABLE F-1 MYSQL FUNCTIONS

Function Return Value Action

resource Opens a connection to a
mysql_connect([string hostname
MySQL server
[:port][:/path/to/socket]]
[, string username] [, string
password] [, bool new])

resource Opens a persistent
mysql_pconnect([string hostname
connection to a MySQL
[:port][:/path/to/socket]]
server
[, string username] [, string
password])

bool Closes a MySQL connection
mysql_close([int link_identifier])

Selects a MySQL database
mysql_select_db(string bool
database_name [, int
link_identifier])

string Returns a string that
mysql_get_client_info(void)
represents the client-
library version
string Returns a string describing
mysql_get_host_info([int
the type of connection in
link_identifier])
use, including the server-
host name
Continued



607
608 Part V: Appendixes


TABLE F-1 MYSQL FUNCTIONS (Continued)

Function Return Value Action

int Returns the protocol
mysql_get_proto_info
version used by the current
([int link_identifier])
connection
string Returns a string that
mysql_get_server_info
represents the server-
([int link_identifier])
version number
bool Creates a MySQL database
mysql_create_db(string
database_name
[, int link_identifier])

bool Drops (deletes) a MySQL
mysql_drop_db(string
database
database_name [, int
link_identifier])

resource Sends an SQL query to
mysql_query(string query
MySQL
[, int link_identifier]
[, int result_mode])

resource Sends an SQL query to
mysql_unbuffered_query(string
MySQL, without fetching
query [, int link_identifier]
and buffering the result
[, int result_mode])
rows
resource Sends an SQL query to
mysql_db_query(string
MySQL
database_name, string query
[, int link_identifier])

resource Lists the databases
mysql_list_dbs([int
available on a MySQL
link_identifier])
server
resource Lists the tables in a MySQL
mysql_list_tables(string
database
database_name [, int
link_identifier])

resource Lists the MySQL result
mysql_list_fields(string
fields
database_name, string table_name
[, int link_identifier])

string Returns the text of the
mysql_error([int
error message from the
link_identifier])
previous MySQL operation
Appendix F: PHP Function Reference 609



Function Return Value Action

int Returns the number of the
mysql_errno([int
error message from the
link_identifier])
previous MySQL operation
int Gets the number of
mysql_affected_rows([int
affected rows in the
link_identifier])
previous MySQL operation
string Escape string for a MySQL
mysql_escape_string(string
query
to_be_escaped)

int Gets the ID generated from
mysql_insert_id([int
the previous INSERT
link_identifier])
operation
mixed Gets result data
mysql_result(int result,
int row [, mixed field])

int Gets the number of rows in
mysql_num_rows(int result)
a result

<<

. 100
( 132 .)



>>