Novell Documentation: NSure Audit - Preparing the MySQL Database

C.3 Preparing the MySQL Database

To prepare the MySQL database, you must create a tablespace and auditusr account on the MySQL server. The tablespace and account enable the Secure Logging Server to log data to MySQL.

Although creating the tablespace is relatively straightforward, determining the hostname in the user account requires some planning. MySQL uses a username@hostname scheme for granting access to databases. If a user tries to log in with a valid user ID but the host name or IP address is incorrect, MySQL denies access to the user. The % wildcard grants access to a user logging in from anywhere except the localhost.

To determine the hostname for your user account, consider the following guidelines:

If Secure Logging Server, iManager, and MySQL are on the same server and Nsure Audit Report is not used from a workstation, consider using auditusr@'localhost' .
If Secure Logging Server and iManager are on the same server, MySQL is on a different server, and Nsure Audit Report is not used at all, consider using auditusr@'serverIP' AND auditusr@'ServerDNSName' .
If Secure Logging Server, MySQL, and iManager are all on different servers, consider using auditusr@'%' .
If you use Nsure Audit Report from a Windows workstation without a static IP address, consider using auditusr@'%' .
In most cases, it is best to create both auditusr@'%' and auditusr@'localhost'. These username@hostname schemes simplify the authentication to MySQL.

To create the tablespace and user account:

On the server hosting the MySQL server software, type the following then specify the password when prompted:
```
mysql -u root -p 
```
This loads MySQL Monitor and attempts to log in as user root. The -p switch instructs MySQL Monitor to request a password. When MySQL Monitor loads, a mysql prompt appears.

Depending on the server platform, you can also launch MySQL Monitor through the Mysql folder.
Create the Nsure Audit tablespace:
```
CREATE DATABASE naudit;
```
NOTE:You do not need to create the database table; the MySQL driver, lgdmsql, automatically creates this table when the logging server first loads the current Channel object configuration in memory. For more information on the table structure, see Section 7.9.1, MySQL Channel Driver.
Create the Nsure Audit (auditusr) user account:
```
GRANT all on naudit.* to auditusr@'%'IDENTIFIED by 'password'; 
```
The user account has access from any IP address or host name. Be sure to replace password with the desired password for the user account.
Define which database to query:
```
\u mysql
```
Confirm the creation of the auditusr account:
```
select host,user from user;
```
In the User column, you should see auditusr next to the appropriate host.