Attachment 63171 Details for Bug 98704 – mysql.xml

mysql.xml

mysql.xml (text/plain), 31.64 KB, created by Chris White (RETIRED) on 2005-07-11 13:04:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Chris White (RETIRED)

Created: 2005-07-11 13:04:15 UTC

Size: 31.64 KB

patch

obsolete

><?xml version="1.0" encoding="UTF-8"?>
><!DOCTYPE guide SYSTEM "http://www.gentoo.org/dtd/guide.dtd">
>
>
><guide link="/home/chriswhite/mysql.xml" lang="en">
><title>MySQL Startup Guide</title>
><author title="Author">
><mail link="chriswhite@gentoo.org">Chris White</mail>
></author>
>
><abstract>
></abstract>
>
>
>
><license/>
>
><version>1.0</version>
><date>2005-07-10</date>
>
><chapter>
><title>Getting Started With MySQL</title>
><section>
><title>Background</title>
><body>
>
>
>MySQL is a popular database server that is used in various applications. SQL
>stands for (S)tandard (Q)uery (L)anguage, which is what MySQL uses to
>communicate with other programs. On top of that, MySQL has its own expanded
>SQL functions to provide additional functionality to users. In this document,
>we'll look at how to do the initial MySQL installation, setup databases and
>tables and create new users. Let's start out with the initial installation.
>
>
></body>
></section>
><section>
><title>MySQL Installation</title>
><body>
>
>
>First make sure you have MySQL emerged on your system. You can do this by
>simply running:
>
>
><pre caption="MySQL emerge">
># emerge mysql
></pre>
>
><note>
>If you require specific functionality from mysql, add <c>-v</c> after emerge to
>show what USE flags are avaliable. These USE flags will help you fine tune
>your installation.
></note>
>
>
>Upon completion of the installation, you will be given the following notice:
>
>
><pre caption="MySQL einfo message">
>You might want to run:
>"ebuild /var/db/pkg/dev-db/mysql-4.0.24-r1/mysql-4.0.24-r1.ebuild config"
>if this is a new install.
></pre>
>
><impo>
>The version numbers displayed here may be different from your own.
></impo>
>
>
>Let's go ahead and run the given command since this is indeed a new installation:
>
>
><pre caption="MySQL configuration">
># ebuild /var/db/pkg/dev-db/mysql-4.0.24-r1/mysql-4.0.24-r1.ebuild config
> * MySQL DATADIR is /var/lib/mysql
> * Press ENTER to create the mysql database and set proper
> * permissions on it, or Control-C to abort now...
>
> Preparing db table
> Preparing host table
> Preparing user table
> Preparing func table
> Preparing tables_priv table
> Preparing columns_priv table
> Installing all prepared tables
>
> To start mysqld at boot time you have to copy support-files/mysql.server
> to the right place for your system
>
> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> To do so, issue the following commands to start the server
> and change the applicable passwords:
> /etc/init.d/mysql start
> /usr/bin/mysqladmin -u root -h pegasos password 'new-password'
> /usr/bin/mysqladmin -u root password 'new-password'
> Depending on your configuration, a -p option may be needed
> in the last command. See the manual for more details.
>
><comment>I've deleted some MySQL non-ebuild specific information from here so
>as to keep this document as consistant as possible.</comment>
>
> * For security reasons you should set your MySQL root
> * password as soon as possible.	 
></pre>
>
>
>Pressing <c>ENTER</c> when prompted while configure the MySQL database. What
>is basically being done here is that the main MySQL database is being setup.
>This database contains administrative information such as databases, tables,
>users, permissions and more. The configuration recommends that you change your
>root password as soon as possible. We will definately do this, otherwise
>someone could come along by chance and hack our default setup MySQL server. The
>configuration has already presented us with a way to set our password up, so
>let's go ahead and follow the instructions:
>
>
><pre caption="Setting up your MySQL root password">
># /etc/init.d/mysql start
> * Re-caching dependency info (mtimes differ)...
> * Starting mysqld (/etc/mysql/my.cnf) ...
> * [ ok ] 
># /usr/bin/mysqladmin -u root -h localhost password 'new-password'
></pre>
>
>
>You can now test that your root password was successfully configured by trying
>to login to your MySQL server:
>
>
><pre caption="Logging into the MySQL server using mysql">
># mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 4 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>The <c>-u</c> switch sets the user that will be logging in. The <c>-h</c>
>switch sets the host. This will usually be <c>localhost</c> unless you are
>setting up a remote server. Finally, <c>-p</c> tells the mysql client that you
>will be entering a password to access your database. Notice the
><c>mysql&gt;</c> prompt. This is where you type in all your commands. Now that
>we're in the mysql prompt as the root user, we can begin to setup our database.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Setting Up The Database</title>
><section>
><title>Creating A Database</title>
><body>
>
>
>We now have a mysql promt displayed. This prompt is where we type our commands
>to the MySQL server. First let's take a look at the databases we currently
>have. To do so, we use the <c>SHOW DATABASES</c> command as shown here:
>
>
><pre caption="Displaying MySQL databases">
>mysql&gt; SHOW DATABASES;
>+----------+
>| Database |
>+----------+
>| mysql |
>| test |
>+----------+
>2 rows in set (0.09 sec)
></pre>
>
><impo>
>Please remember that all MySQL commands end with semicolons!
></impo>
>
>
>Despite the fact that a test database is already created, we're going to create
>our own for a more complete guide. Databases are created using the <c>CREATE
>DATABASE</c> command. We'll create one named "gentoo" as shown here:
>
>
><pre caption="Creating the gentoo database">
>mysql&gt; CREATE DATABASE gentoo;
>Query OK, 1 row affected (0.08 sec)
></pre>
>
>
>The response let's us know that the command was executed ok. In this case, 1
>row was modified. This is a reference to the main mysql database, which
>carries a list of all the databases. You won't need to worry too much about
>the background details. The last number is how fast the query was executed.
>We can verify the database was created by running the <c>SHOW DATABASES</c>
>command again:
>
>
><pre caption="Verifing the database is created">
>mysql&gt; SHOW DATABASES;
>+----------+
>| Database |
>+----------+
>| gentoo |
>| mysql |
>| test |
>+----------+
>3 rows in set (0.00 sec)
></pre>
>
>
>Indeed our database has been created. In order to work with creating tables
>for our new gentoo database, we need to select it as our current database.
>To do so, we use the<c>USE</c> command. The <c>USE</c> command takes the 
>name of the database you wish to use as your current database. Another 
>option is to set it on the command line after the <c>-p</c> switch. Let's go
>ahead and switch to the gentoo database:
>
>
><pre caption="Switching our database">
>mysql&gt; USE gentoo;
>Database changed
></pre>
>
>
>And the current database is now our previously created gentoo database. Now
>that we're using it, we can start to create some tables and put information in
>them.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Working With Tables In MySQL</title>
><section>
><title>Creating the tables</title>
><body>
>
>
>In the structure of MySQL, there are databases, tables, records, and fields.
>Databases hold together tables, tables hold together records, records hold
>together fields, which contain the actual information. This structure lets
>users select how they want to access their information. So far we've dealt
>with databases, now let's work with tables. First off, tables can be listed
>similiarly to databases using the <c>SHOW TABLES</c> command. Right now there
>are no tables in our gentoo database, as running the command will show us:
>
>
><pre caption="Empty gentoo database">
>mysql&gt; SHOW TABLES;
>Empty set (0.00 sec)
></pre>
>
>
>This means we need to create some tables. In order to do so, we use the
><c>CREATE TABLE</c> command. However, this command is quite different from
>simple <c>CREATE DATABASE</c> command. This command takes a list of arguments
>you must give it. The form is as follows:
>
>
><pre caption="CREATE TABLE format">
>CREATE TABLE [table_name] ([field_name] [field_type]([size]));
></pre>
>
>
>table_name is the name of the table we wish to create. In this case, let's
>make a table named <c>developers</c>. This table will contain the 
>developer's name, email and job. field_name will contain the name of the 
>field. We have 3 required names in this case: name, email, and job. Now
>what about the field type? The field type is what type of information will
>be stored. The different formats avaliable can be found at the <uri
>link="http://dev.mysql.com/doc/mysql/en/column-types.html">this page</uri>. For
>our purposes, we'll use <c>VARCHAR</c> data type for all of our fields.
><c>VARCHAR</c> is one of the most simplistic data types when it comes 
>to working with strings. size is how many of the field_type we will be
>creating. In this case, we'll use 128 for our size. This means that the
>information can be stored in terms of 128 <c>VARCHAR</c>. You can safely think
>of this as 128 characters for the time being, though there is a somewhat more
>technical explanation that the above site will provide you with. Now that we
>know how we are going to create the table, we can do so now:
>
>
><pre caption="Creating our table">
>mysql&gt; CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
>Query OK, 0 rows affected (0.11 sec)
></pre>
>
>
>Looks like our table was created ok. Let's check it with the <c>SHOW
>TABLES</c> command:
>
>
><pre caption="Verifying our table">
>mysql&gt; SHOW TABLES;
>+------------------+
>| Tables_in_gentoo |
>+------------------+
>| developers |
>+------------------+
>1 row in set (0.00 sec)
></pre>
>
>
>Yes, there's our table. However, it doesn't seem to have any information on
>the types of fields we setup. For that, we use the <c>DESCRIBE</c> command,
>which takes the name of the table as its argument. Let's see what that gives
>us for our developers table:
>
>
><pre caption="Describing our developers table">
>mysql&gt; DESCRIBE developers;
>+-------+--------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+--------------+------+-----+---------+-------+
>| name | varchar(128) | YES | | NULL | |
>| email | varchar(128) | YES | | NULL | |
>| job | varchar(128) | YES | | NULL | |
>+-------+--------------+------+-----+---------+-------+
>3 rows in set (0.00 sec)
></pre>
>
><note>
>You can alternatively use <c>DESC</c> as well.
></note>
>
>
>This shows the different fields and their types. It also shows a few extra
>attributes that are beyond the scope of this doc and should be consulted with
>the MySQL documentation. We now have a table to work with. Now let's go ahead
>and put some information in it.
>
>
></body>
></section>
><section>
><title>Putting Information Into Our MySQL Database</title>
><body>
>
>
>Now that we have a table setup, let's go ahead and put some information in it.
>We do so using the <c>INSERT</c> command. Like the <c>CREATE TABLE</c>
>command, it also has a specific format:
>
>
><pre caption="INSERT format">
>INSERT INTO table VALUES('value1', 'value2', ...);
></pre>
>
>
>table contains the MySQL table we wish to enter the information into. value#
>contain the values you wish to insert into the table. In this case, we want to
>insert data into the developers table. Let's insert a sample record with 'Joe
>Smith', 'joesmith@gentoo.org', 'toolchain'. So we would do as follows:
>
>
><pre caption="Inserting information into our developers table">
>mysql&gt; INSERT INTO developers VALUES('Joe Smith', 'joesmith@gentoo.org', 'toolchain');
>Query OK, 1 row affected (0.06 sec)
></pre>
>
>
>According to our return result, it appears that the record was inserted ok.
>What if we want to input more information than just one record? Another handy
>command is the <c>LOAD DATA</c> command. This loads records from a tab
>seperated file. Let's try that by editing a file in our home directory with
>the records. We'll call this file <path>records.txt</path>. Here's a sample:
>
>
><pre caption="~/records.txt">
>John Doe	johndoe@gentoo.org	portage
>Chris White	chriswhite@gentoo.org	documentation
>Sam Smith	samsmith@gentoo.org	amd64
></pre>
>
><impo>
>Be sure you know what data you're dealing with! It's very unsafe to use LOAD
>DATA when you are uncertain of the file's contents!
></impo>
>
>
>Now the <c>LOAD DATA</c> command has a somewhat enlongated definition, but
>we'll use the most simplistic form here:
>
>
><pre caption="LOAD DATA format">
>LOAD DATA LOCAL INFILE '/path/to/filename' INTO TABLE table;
></pre>
>
>
><path>/path/to/filename</path> is the directory and filename that will be used.
>table is the name of our table. In this case, our file is
><path>~/records.txt</path> and the table is developers:
>
>
><pre caption="Loading in our data">
>mysql&gt; LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;
>Query OK, 3 rows affected (0.00 sec)
>Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
></pre>
>
><impo>
>If you come up with any strange behaviour, make sure your fields are seperated
>by tabs! If you paste information from another source, it may convert your
>tabs to spaces!
></impo>
>
>
>That worked as well. However, this simply inserts records, and does not give
>you any sort of control over MySQL. Many web applications use sql scripts in
>order to setup MySQL quickly and easily. If you want to use an sql script,
>you'll need to run mysql in batch mode, or source the file. Here's an example
>of running mysql in batch mode:
>
>
><pre caption="MySQL in batch mode">
>$ mysql -u root -h localhost -p &lt; sqlfile
></pre>
>
>
>Like <c>LOAD DATA</c>, be sure you can tell what sql file does. <e>Failure to 
>do so may cause your database to be comprimised</e>! Another way you can
>accomplish this is to use the <c>source</c> command. This command will run
>mysql commands from an sql file while in the mysql interactive mode. Here is
>how to source an sql file:
>
>
><pre caption="Sourcing an sql file">
>mysql&gt; source sqlfile;
></pre>
>
>
>If you see a web application wanting you to run an sql file, the two above
>commands can be used to accomplish that taks. We have our table setup, so how
>do we check our fields? We do this by searching our table with queries.
>
>
></body>
></section>
><section>
><title>Browsing MySQL Tables With Queries</title>
><body>
>
>
>Queries are one of the main features of any SQL database. They are what help
>us interpret information in our tables to something useful. Most queries are
>done with the <c>SELECT</c> command. The <c>SELECT</c> command is fairly
>complex, and we're only going to be looking at two main forms of the command in
>this document:
>
>
><pre caption="SELECT forms">
><comment>selects all entries in a table</comment>
>SELECT * FROM table;
><comment>selects specific entries in a table</comment>
>SELECT * FROM table WHERE field=value;
></pre>
>
>
>Let's take a quick look at the first form. It's relatively simple and gives
>you an overall look of your table. Let's go ahead and run it to see what data
>we have so far:
>
>
><pre caption="Overviewing our table">
>mysql&gt; SELECT * FROM developers;
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Joe Smith | joesmith@gentoo.org | toolchain |
>| John Doe | johndoe@gentoo.org | portage |
>| Chris White | chriswhite@gentoo.org | documentation |
>| Sam Smith | samsmith@gentoo.org | amd64 |
>+-------------+-----------------------+---------------+
>4 rows in set (0.00 sec)
></pre>
>
>
>We see both the data we inserted through <c>INSERT</c> and those inserted by
><c>LOAD DATA</c> present. Now, let's say that we just want to see the record
>for Chris White. We can do so with the second form of select as shown here:
>
>
><pre caption="Selecting a specific record using SELECT">
>mysql&gt; SELECT * FROM developers WHERE name = 'Chris White';
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Chris White | chriswhite@gentoo.org | documentation |
>+-------------+-----------------------+---------------+
>1 row in set (0.08 sec)
></pre>
>
>
>As expected, the specific entry that we were looking for has been selected.
>Now, right now, being the root mysql user, we have unlimited permissions to do
>what we wish with the MySQL database. In a server environment, a user with
>such priveleges can be quite problematic. In order to control who does what
>with the databases, we setup privleges.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>MySQL Privleges</title>
><section>
><title>Granting Privleges with GRANT</title>
><body>
>
>
>Privleges are what kind of access users have to databases, tables, pretty much
>anything. Right now in the gentoo database, root is the only account that can
>access it, given root's permissions. Now, let's create two somewhat generic
>users, guest and admin, who will access the gentoo database and work with the
>information in it. Now guest will be a restricted account. All it will be
>able to do is get information from the database, and that's it. admin will
>have the same control as root, but only for the gentoo database (not the main
>mysql databases). Before we start on that, let's have a closer look at this
>somewhat simplified format of the <c>GRANT</c> command:
>
>
><pre caption="GRANT format">
>GRANT [privleges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]';
></pre>
>
>
>First we have the privleges we wish to assign. With what we've learned so far,
>here are some of the privleges you can set:
>
>
><ul>
> <li>ALL - Gives the all privlege control for the database</li>
> <li>CREATE - Let's the user create tables</li>
> <li>SELECT - Allows users to query tables</li>
> <li>INSERT - Allows users to insert data into a table</li>
> <li>SHOW DATABASES - Allows users to see a list of databases</li>
> <li>USAGE - User has no privleges</li>
> <li>GRANT OPTION - Allows users to grant privleges</li>
></ul>
>
><note>
>If you're running MySQL to communicate data to a web application, CREATE,
>SELECT, INSERT (discussed here), DELETE and UPDATE <uri
>link="http://dev.mysql.com/doc/mysql/en/grant.html">(see MySQL
>documentation)</uri> are the only permissions you will most likely need. A lot
>of people make the mistake of granting all permissions when it's not really
>necessary. Check with the application developers to see if such permissions
>will cause issues with general operation.
></note>
>
>
>For our admin user, ALL will be sufficient. For the guest user, SELECT will be
>sufficient for read only access. database is the database we wish the user to
>have these permissions on. In this example, gentoo is the database. The .*
>means all tables. If you wanted to, you could apply per table access. user is
>the name of the user and host is the hostname the user will be accessing from.
>In most cases, this will be localhost. Finally, password is the user's
>password. Once we're done, we also will need to execute the <c>FLUSH
>PRIVILEGES</c> command. This updates user permissions without restarting the
>server. Given the information, let's go ahead and create our users:
>
>
><pre caption="Creating the admin and guest user">
><comment>admin</comment>
>mysql&gt; GRANT ALL ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
><comment>guest</comment>
>mysql&gt; GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'password';
>mysql&gt; FLUSH PRIVILEGES;
></pre>
>
>
>Now that we have the users setup, let's test them out. First we quit mysql by
>typing <c>quit</c> at the command prompt:
>
>
><pre caption="Quiting MySQL">
>mysql&gt; quit
></pre>
>
>
>We're now at the console. Now that we have our users setup, let's go ahead and
>see what they can do.
>
>
></body>
></section>
><section>
><title>Testing User Permissions</title>
><body>
>
>
>Now that we're back in the console, we'll attempt to login as the guest user.
>Currently, the guest user has <c>SELECT</c> only privleges. This basically
>comes down to being able to search and nothing more. Let's go ahead and login
>with the guest account:
>
>
><pre caption="Logging in with the guest account">
>$ mysql -u guest -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 6 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>First off we should test user restriction. Let's switch to the gentoo database:
>
>
><pre caption="Switching to the gentoo database">
>mysql&gt; USE gentoo;
>Reading table information for completion of table and column names
>You can turn off this feature to get a quicker startup with -A
>
>Database changed
></pre>
>
>
>Now let's test something we can do. We'll attempt to create a table for that
>purpose. Let's go ahead and try that:
>
>
><pre caption="Attempting to create a table with the guest user">
>mysql&gt; CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));
>ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
></pre>
>
>
>As you can see, this function fails, as our user does not have the appropriate
>access. However, one access we did grant is the <c>SELECT</c> statement.
>Let's go ahead and try that:
>
>
><pre caption="Attempting the SELECT statement">
>mysql&gt; SELECT * FROM developers;
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Joe Smith | joesmith@gentoo.org | toolchain |
>| John Doe | johndoe@gentoo.org | portage |
>| Chris White | chriswhite@gentoo.org | documentation |
>| Sam Smith | samsmith@gentoo.org | amd64 |
>+-------------+-----------------------+---------------+
>4 rows in set (0.01 sec)
></pre>
>
>
>The command succeeds, and we're given a glimpse of what user permissions can
>do. We did, however, create an admin account as well. This was created to
>show that even all permissions granted users can still have limitations. Go
>ahead and quit MySQL and login as the admin:
>
>
><pre caption="Logging in as admin">
>mysql&gt; quit
>Bye
>$ mysql -u admin -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 7 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>To begin, we'll try creating a new database with our admin user. This will
>test the user's access to the main MySQL database. Remember ealier that we
>only set permissions to a specific database. Let's go ahead and attempt now:
>
>
><pre caption="Attempting to create a new database">
>mysql&gt; CREATE DATABASE gentoo2;
>ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'
></pre>
>
>
>Indeed, the admin user cannot create databases on the main MySQL database,
>despite all of its permissions on the gentoo database. However, we're still
>able to use the admin account to modify the gentoo database, as shown here by
>this example data insertion:
>
>
><pre caption="admin permissions in gentoo database">
>mysql&gt; USE gentoo;
>Reading table information for completion of table and column names
>You can turn off this feature to get a quicker startup with -A
>
>Database changed
>mysql&gt; INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
>Query OK, 1 row affected (0.08 sec)
></pre>
>
>
>Indeed our admin can access the database as they please. Now sometimes, we
>need to get rid of user permissions. This could be anything from a problematic
>user to a retired employee. Let's take a look at how to disable user
>permissions with the REVOKE command.
>
>
></body>
></section>
><section>
><title>Removing User Access With The REVOKE Command</title>
><body>
>
>
>The <c>REVOKE</c> command let's us deny user access. We can either deny full
>access, or specific access. In fact, the format is very similiar to
><c>GRANT</c>:
>
>
><pre caption="REVOKE format">
>PRIVLEGES [privleges] ON database.* FROM '[user]'@'[host]';
></pre>
>
>
>Options here are explained in the <c>GRANT</c> command section. In this
>section however, we're going to deny full access to a user. Let's say we find
>out the guest account is causing some problems security wise. We decide to
>revoke all privleges. Let's go ahead and quit our current mysql session and 
>login as root:
>
>
><pre caption="Logging in as root">
>mysql&gt; quit
>Bye
>$ mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 8 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>Now then, let's go ahead and revoke the user's permissions as follows:
>
>
><pre caption="Revoking user permissions">
>mysql&gt; REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';
>Query OK, 0 rows affected (0.00 sec)
></pre>
>
><note>
>In this case, user access is simple, so per database revoking is not a problem.
>However, in larger cases, you would most likely be using *.* instead of
>gentoo.* to remove user access to all other databases.
></note>
>
>
>Now let's quit and attempt to login as a guest user:
>
>
><pre caption="Attempting guest user login">
>$ mysql -u guest -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Youra MySQL connection id is 9 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>We're able to login, but our access to gentoo is now gone:
>
>
><pre caption="Denied access to guest account">
>mysql&gt; USE gentoo;
>ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
></pre>
>
>
>And our problematic user is no longer able to access the gentoo database.
>Please not that the user was still able to login. That is because they remain
>in the main MySQL database. Let's take a look at how to completely remove an
>account with DELETE and the MySQL user table.
>
>
></body>
></section>
><section>
><title>Completely Removing Accounts Using DELETE And The MySQL user
>Table</title>
><body>
>
>
>The MySQL user table is a listing of all users and information about them.
>Let's logout of our previous guest account and back as root:
>
>
><pre caption="Logging in as root">
>mysql&gt; quit
>Bye
>$ mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 14 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>mysql&gt;
></pre>
>
>
>First off, let's go ahead and utilize the main MySQL database:
>
>
><pre caption="Using the main mysql database">
>mysql&gt; USE mysql;
>Reading table information for completion of table and column names
>You can turn off this feature to get a quicker startup with -A
>
>Database changed
>mysql&gt;
></pre>
>
>
>Now let's see what tables the mysql database has:
>
>
><pre caption="mysql database table listing">
>mysql&gt; SHOW TABLES;
>+-----------------+
>| Tables_in_mysql |
>+-----------------+
>| columns_priv |
>| db |
>| func |
>| host |
>| tables_priv |
>| user |
>+-----------------+
>6 rows in set (0.00 sec)
></pre>
>
>
>The user table is the table we're after. This contains the user information.
>Since the user table output is pretty large, we're going to do a simple
>restricted <c>SELECT</c> statement to find the guest user only:
>
>
><pre caption="Finding our guest user in the user table">
>mysql&gt; SELECT * FROM user WHERE User = 'guest';
>+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+-
>--------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+----------
>-------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------
>+---------------+-------------+-----------------+
>| 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_tm p_table_priv |
>Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type
>| ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates |
>max_connections |
>+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+-
>--------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+----------
>-------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------
>+---------------+-------------+-----------------+
>| localhost | guest | 4655c05b05f11fab | N | N | N
>| N | N | N | N | N | N
>| N | N | N | N | N | N
>| N | N | N | N | N
>| N | | | | | 0 |
>0 | 0 |
>+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+-
>--------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+----------
>-------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------
>+---------------+-------------+-----------------+
>1 row in set (0.06 sec)
></pre>
>
>
>The output here is enormous and somewhat hard to read. However, we'll simplify
>things by stating that the User field is guest, and that we have displayed here
>information about the guest user. Now that we have our information, we can get
>rid of the guest user. This is done with the <c>DELETE command</c>. The
>format of the <c>DELETE</c> command for our usage is as follows:
>
>
><pre caption="DELETE format">
>DELETE FROM table WHERE field='value';
></pre>
>
>
>You may notice that <c>DELETE</c> is somewhat similiar to the <c>SELECT</c>
>statement in its format. In this case, the field will be User, and the value
>guest. This will delete the record in the user table where the user is guest,
>successfully deleting our guest user account. Let's go ahead and do that:
>
>
><pre caption="Deleting the guest account">
>mysql&gt; DELETE FROM user WHERE User='guest';
>Query OK, 1 row affected (0.07 sec)
><comment>FLUSH PRIVILEGES is needed to update user permissions</comment>
>mysql&gt; FLUSH PRIVILEGES;
>Query OK, 0 rows affected (0.00 sec)
></pre>
>
>
>It seems to have worked ok. Let's test by logging out and attempting to login
>as our guest user:
>
>
><pre caption="Attempting login as the guest user">
>mysql&gt; quit
>Bye
>$ mysql -u guest -h localhost -p
>Enter password:
>ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)
></pre>
>
>
>Our user is now successfully deleted!
>
>
></body>
></section>
><section>
><title>Conclusion</title>
><body>
>
>
>This ends the MySQL introductory tutorial. I hope this gives you a better
>understanding of the basics behind MySQL and getting a database setup. Please
>email me at <mail>chriswhite@gentoo.org</mail> if you have any comments.
>
>
></body>
></section>
></chapter>
>
></guide>

Actions: View

Attachments on bug 98704: 63171 | 63240 | 63279 | 66558 | 66718