VOICE Home Page: http://www.os2voice.org
Newsletter Index
< Previous Page | Next Page >
Feature Index
|
VOICE Home Page: http://www.os2voice.org |
March 2004
Newsletter Index
|
| By Wolfgang Draxler © March 2004, Translation: Menno Willemse |
This is the second article in my series. This time, I'll describe how to create several users. Furthermore, we will create WPS objects that take functions out of your hands like for instance creating or deleting a database.
But before that, we will make one change to the database.
It is my opinion that programs and data should be stored on different drives. This makes it much simpler to create a backup. We will now reconfigure MySQL. To do this, we need to stop the server. There are several ways in which to do this:
Simply kill the server. (Not the cleanest solution, but it works.
It's better to use one of the other options.)
Using the object Shutdown server
or using the eCS window. To do this, type in the following command:
[F:\os2_prog\datenbank\mysql\bin]mysqladmin -ppw -u root shutdown
Note: Instead of the directory shown above, you need to be in your own installation directory. At the parameter -p you should replace pw with your own password.
Now, the database should no longer be in the window list.
By default, MySQL stores its data in the directory <installation directory\data>. (In my case: F:\OS2_Prog\Datenbank\MySQL\data) Copy this directory and its subdirectories to a different drive. The best way to do this is either using the WPS or using the xcopy command. I have decided on the directory G:\Work-OS2\MySQL\data, so this is the xcopy command to do it:
xcopy F:\OS2_Prog\Datenbank\MySQL\data\*.* G:\Work-OS2\MySQL\data\*.* /h/o/t/s/e/r/v
Now we need to tell MySQL about this directory on startup.
MySQL's configuration file is called my.cnf and lives in the
directory <boot drive>:\MPTN\ETC (this is determined by the
ETC environment variable). This is what's in that file:
[client]
compress
user=root
[mysqld]
user=mysql
preload-client-dll
server-id=419888
basedir=F:/OS2_Prog/Datenbank/MySQL
datadir=F:/OS2_Prog/Datenbank/MySQL/data
Open the my.cnf file in an editor (such as e.exe) and change the value of the datadir attribute to the new directory. In my case, that would be:
datadir=G:/Work-OS2/MySQL/data
Save the file and close the editor. The file should now look like this:
[client]
compress
user=root
[mysqld]
user=mysql
preload-client-dll
server-id=419888
basedir=F:/OS2_Prog/Datenbank/MySQL
datadir=G:/Work-OS2/MySQL/data
Now you can start the database.
Next, we will add some objects to the WPS.
Those would be:
Add a database
Remove a database
To do this, we will create a user and create the objects mentioned above.
To create a user, you need to start the console.
Now, we need to switch to the database mysql. This is done using the command use.
mysql> 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
Let's see what tables are in the mysql database:
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)
user: This table contains all the users who are allowed to access the server.
db: This table describes database access.
host: Description of the host-based access rights.
tables_priv: User permissions at the single table level.
columns_priv: User permissions at the column level.
func: This table stores user-defined functions. (We don't need this to set a user's privileges).
Now, to create the user WPS, we need the USER table. To get more information on this table, you use the command desc <table>.
mysql> desc user;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Password | char(16) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+---------------+------+-----+---------+-------+
As you see, the desc command shows a list of all the fields in the USER table.
the field type (field: Type)
whether the field is allowed to be empty (null) (field: Null)
whether the field is a primary or alternate key (field: Key)
a value that is entered when the field is empty when it is changed or inserted (field: Default)
extra information (field: Extra)
To create the WPS user, a new record has to be entered into the table. To do this, we use the insert command:
mysql> insert into user
-> (host, user, password, Create_priv, Drop_priv)
-> values
-> ("localhost", "wps", password("wps"), "Y", "Y");
Query OK, 1 row affected (0.04 sec)
Now the record (i.e. the user) has been entered into the database. Because MySQL stores its permissions internally, we need to refresh the permissions.
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
To see if the record is really in the database, we can start a query, using the select command.
mysql> select * from 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 |
+-----------------------------------------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost | root | 28748dc05f058960 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | wps | 6867a41c29783e9f | N | N | N | N | Y | Y | N | N | N | N | N | N | N | N |
+-----------------------------------------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
2 rows in set (0.00 sec)
As you see, we now have two users:
The user root, who has all the permissions, and the new user WPS, who only has permission to create or delete databases.
Normally, if you do not specify a field in an insert command, it will be filled with a NULL value (that is: no value; empty). In the USER table this is not the case because all fields are filled with N. The reason is that the field DEFAULT in the table description contains the value N.
Finally, we are going to create the corresponding WPS objects on the desktop.
To do this, we create two program objects with the following values:
Object: Create Database
Path and file name: F:\OS2_PROG\DATENBANK\MYSQL\BIN\MYSQLADMIN.EXE
Parameters: -uwps -pwps create [New Database:]
Icon: F:\OS2_Prog\Datenbank\MySQL\bin\icons\mySQL-CreateDatabase.ico
Objekt: Drop Database
Path and file name: F:\OS2_PROG\DATENBANK\MYSQL\BIN\MYSQLADMIN.EXE
Parameters: -uwps -pwps drop [Drop Database:]
Icon: F:\OS2_Prog\Datenbank\MySQL\bin\icons\mySQL-DropDatabase.ico
This concludes the second part of the article. In the next part, I am going to concentrate on SQL.
References:
|
Feature Index
editor@os2voice.org
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: http://www.os2voice.org