Chapter 6 Licensing Adaptive Server Features

Configuring Adaptive Server for UNIX Platforms

Chapter 7 Adding Optional Functionality to Adaptive Server

Chapter 7

Adding Optional Functionality to Adaptive Server

This chapter provides instructions for adding the following optional functionality for Adaptive Server:

Sample databases - you can use French, German, Japanese, and U.S. English databases to practice most examples given in the Transact-SQL User's Guide.
Auditing - you can detect access of the system and misuse of resources by using auditing to track security-related system activity in an audit trail.

After you have installed the Sybase products on your system, see the product documentation for configuration and administration issues.

Adding sample databases

This section describes how to install the U.S. English and international language sample databases. For installation instructions specific to each sample database, see:

Installing pubs2 and pubs3

The sample databases pubs2 and pubs3 contain information about a fictional business. You can use this information to learn about Adaptive Server without affecting essential data.

Table 7-1 lists the scripts that you can use to install the sample databases.

Sample database scripts
Script	Description
installpubs2	Installs the pubs2 sample database. pubs2 contains data that represents a publishing operation. Use this database to test your server connections and to learn Transact-SQL. Most of the examples in the Adaptive Server documentation query the pubs2 database.
installpubs3	Installs the pubs3 sample database. This updated version of pubs2 uses referential integrity. In addition, its tables are slightly different than the tables used in pubs2. Where noted, the Adaptive Server documentation uses the pubs3 database in its examples.
installpix2	Installs the image data that is used with the pubs2 database. The master device size should be at least 30MB to install the full pubs2 database, including the image data. Run the installpix2 script after you run installpubs2.

Default devices for sample databases

The $SYBASE/scripts directory contains scripts for installing the us_english sample database, foreign language sample databases, and the image data associated with the U.S. English pubs2 sample database.

Each sample database requires 3MB on your database device. By default, the sample database installation scripts install the sample databases on the device that is designated as the default database device, which is the master device.

If you have not used sp_diskdefault to change the status of the master device or to specify another default device, the scripts install the sample databases on the master device. Sybase does not recommend this because it uses valuable space that is needed for system tables.

To avoid installing sample databases on the master device, do one of the following:

Use sp_diskdefault to specify a default device other than the master device. For information on sp_diskdefault, see the Adaptive Server Reference Manual.
Modify each sample database installation script to specify a different device.

Running the database scripts

To run the installpubs2, installpix2, and installpubs3 scripts:

Start Adaptive Server.
Determine the type (raw partition, logical volume, operating system file, and so on) and location of the device for pubs2 and pubs3. You will need to provide this information later.
Make a copy of the origin al installpubs2 and installpubs3 scripts. Be sure you can access the copies in case you have problems with the edited scripts.
Use a text editor to edit the script, if necessary, to specify a default device other than the master device, or use sp_diskdefault.
Go to the scripts directory in your Sybase installation directory ($SYBASE/$SYBASE_ASE), and use the following syntax to run the script:
```
isql -Usa -P -Sserver_name -iscript_name 
```
where server_name represents the destination server for the database and script_name is the full path to and file name of the script to run.
For example, to install pubs2 on a server named VIOLIN, enter:
```
isql -Usa -P -SVIOLIN 
-i $SYBASE/$SYBASE_ASE/scripts/installpubs2
```
To install the image data associated with pubs2, run:
```
isql  -Usa -Ppassword -Sservername\
 <$SYBASE/ASE-12_0/scripts/installpix2
```
The image data requires extra space--there are six pictures; two each in the PICT, TIFF, and Sun raster file formats. Run the images script only if you want to use or test the image datatype. Sybase does not supply any tools for displaying image data. You must use appropriate screen graphics tools to display the images after you have extracted them from the database.

Installing interpubs

interpubs is a database similar to pubs2 that contains French and German data. This data contains 8-bit characters and is available for use at Adaptive Server installations that use the ISO 8859-1 (iso_1) or Roman8 (for HP-UX) character set. To display the French and German data correctly, you must set up your terminal to display 8-bit characters.

To install interpubs:

Verify that iso_1 or Roman8 is installed as the default character set or as an additional character set.
Determine the type (raw partition, logical volume, operating system file, and so on) and location of the device for the interpubs database. You will need to provide this information later.
Make a copy of the original installintpubs script. Be sure you can access this copy, in case you experience problems with the edited script.
Use a text editor to edit the script, if necessary, to specify a default device other than the master device, or use sp_diskdefault.
Execute the script, using the -J flag to ensure that the database is installed with the correct character set:
For HP:
```
isql -Usa -Ppassword -Sservername -Jroman8 \
 <$SYBASE/$SYBASE_ASE/scripts/roman8/installintpubs
```
For Digital UNIX, IBM, and Sun Solaris:
```
isql -Usa -Ppassword -Sservername -Jiso_1 \
 <$SYBASE/$SYBASE_ASE/scripts/iso_1/installintpubs
```
For more information on the -J option in isql, see the Utility Programs for UNIX Platforms.

Installing jpubs

If you installed the Japanese Language Module with your Adaptive Server, $SYBASE/scripts contains the installjpubs script for installing the jpubs database. jpubs is similar to pubs2 except it contains Japanese data. installjpubs uses either the EUC-JIS (eucjis) or the Shift-JIS (sjis) character set.

To ensure that your system can display Japanese data correctly:

On HP-UX, verify that you are running HP Native Language I/O .
On Sun Solaris, verify that you are running SunJLE.

To install the jpubs database:

Set your terminal to display 8-bit characters.
Verify that the EUC-JIS or Shift-JIS character set (depending on which characters you want for jpubs) is installed as Adaptive Server's default character set or as an additional character set.
Determine the type (raw partition, logical volume, operating system file, and so on) and location of the device for the jpubs database. You will need to provide this information later.
Make a copy of the original installjpubs script. Be sure that you can access this copy, in case you experience problems with the edited script.
Use a text editor to edit the script, if necessary, to specify a default device other than the master device, or use sp_diskdefault. See "Default devices for sample databases" .
Execute the installjpubs script, using the -J flag to ensure that the database is installed with the correct character set:
```
isql  -Usa -Ppassword -Sservername -Jeucjis \
 < $SYBASE/$SYBASE_ASE/scripts/eucjis/installjpubs
```
or:
```
isql -Usa -Ppassword  -Sservername -Jsjis \
 < $SYBASE/$SYBASE_ASE/scripts/sjis/installjpubs
```
For more information about the -J option in isql, see Utility Programs for UNIX Platforms.
Since you may want to refresh or make new copies of the sample databases, back up the original and edited versions of the installation scripts and store the copies in a safe place.

Maintaining the sample databases

The sample databases contain a "guest" user that allows access to the database by any authorized Adaptive Server user. The "guest" user has a wide range of privileges, including permissions to select, insert, update, and delete user tables. For more information about the "guest" user and "guest" permissions, see the System Administration Guide.

If possible, and if space allows, give each new user a clean copy of the sample databases so that she or he is not confused by other users' changes.

If space is a problem, you can instruct the user to issue the begin transaction command before updating a sample database. After the user has finished updating one of the sample databases, he or she can issue the rollback transaction command to undo the changes.

Adding auditing

Auditing is an important part of security in a database management system. Security-related system activity is recorded in an audit trail, which can be used to detect penetration of the system and misuse of resources. By examining the audit trail, the System Security Officer can inspect patterns of access to objects in databases and can monitor the activity of specific users. Audit records can be traced to specific users, enabling the audit system to act as a deterrent to users who are attempting to misuse the system.

A System Security Officer manages the audit system and is the only user who can start and stop auditing, set up auditing options, and process audit data.

Audit system devices and databases

The audit system includes several components. The main components are:

The sybsecurity device and the sybsecurity database, which stores audit information
The audit trail, which is composed of several audit devices and tables that you determine at configuration time
The syslogs transaction log device, which stores transaction logs

The sybsecurity device and database

The sybsecurity device stores the sybsecurity database. The sybsecurity database is created as part of the auditing configuration process. It contains all the system tables in the model database as well as a system table for keeping track of server-wide auditing options and system tables for the audit trail.

Tables and devices for the audit trail

Adaptive Server stores the audit trail in system tables, named sysaudits_01 through sysaudits_08. For example, if you have two audit tables, they are named sysaudits_01 and sysaudits_02. At any given time, only one of the audit tables is current. Adaptive Server writes all audit data to the current audit table. A System Security Officer can use sp_configure to set or change which audit table is current.

When you configure Adaptive Server for auditing, you determine the number of audit tables for your installation. You can specify up to eight system tables (sysaudits_01 through sysaudits_08). Plan to use at least two or three system tables for the audit trail and to put each system table on its own device, separate from the master device. If you do this, you can use a threshold procedure that archives the current audit table automatically, before it fills up and switches to a new, empty table for subsequent audit records.

Device for syslogs systems table

When you configure for auditing, you must specify a separate device for the syslogs system table, which contains the transaction log. The syslogs table, which exists in every database, contains a log of transactions that are executed in the database.

Overview of audit installation

There are two methods for installing auditing for the first time in Adaptive Server:

Use the installsecurity script. For information, see the System Administration Guide.
Use the auditinit utility. Tasks that you must perform before installing auditing and instructions on using the auditinit utility follow.

Pre-installation tasks

Determine the location of the raw devices for the sybsecurity, syslogs, and sysaudits table devices. You will need to provide this information later.

Sybase recommends that you:

Configure your system with the minimum number of auditing devices you require--you must configure at least three devices. You can add more auditing devices later with sp_addaudittable. For information, see the Adaptive Server Reference Manual.
Install auditing tables and devices in a one-to-one ratio. Tables that share the same device will share the same upper threshold limit. These tables cannot be used sequentially when a device fills up, because they both reside on the same device.
Install each auditing table on its own device. This enables you to set up a smoothly running auditing system with no loss of auditing records. With two auditing tables, when one fills up, you can switch to the other. With a third auditing table, if one device fails, the System Security Officer can install a new threshold procedure that changes the device rotation to skip the broken device until the device is repaired.
Make the device larger than the table. When you use only three auditing tables and devices, the size of the table and the size of the device can be similar, because you can obtain more auditing capacity by adding more auditing tables and devices (up to eight). When you are working toward the upper table and device limit (six to eight), you may want to make the device considerably larger than the table. Then, you can expand the table size later towards the upper size of the device when a larger auditing capacity is desired, and few or no device additions are available.

Installing auditing

To configure Adaptive Server for auditing:

Start auditinit at the UNIX prompt:

$SYBASE/$SYBASE_ASE/install/auditinit

auditinit displays the following menu:

AUDITINIT
1. Release directory:  /usr/u/sybase 
2.  Configure a Server product

Select Configure a Server Product.
Select Adaptive Server.
Select Configure an Existing Sybase Server.
Select the server to configure.
Provide the SA Password for the server you selected.

From the Sybase Server Configuration screen, select Configure Auditing.

As you proceed through the menus in auditinit, you can change any default values that appear. As you finish each menu, press CTRL+A to accept the defaults or changed values and move to the next menu.

CONFIGURE  AUDITING 
1. Configure auditing:  no 
2.  Add a device for audit table(s) 
3. Add a device for the  audit database transaction log 
4. Delete a device entry 
5.  Change a device entry 
 
List of devices for the  audit tables: 
Logical name    Physical  name    Segment name    Table  name    Size 
 
Device  for the audit datbase transaction log: 
Logical name    Physical  name    Segment name    Table  name    Size

From the Configure Auditing screen, select Configure Auditing.
auditinit redisplays the Configure Auditing menu with the value "yes" displayed for Configure Auditing.

Creating a device for an audit table

To create a device for an audit table:

From the Configure Auditing screen, select Add a Device for Audit Table(s).

auditinit displays the following menu:

ADD/CHANGE A NEW DEVICE FOR AUDITING 
1.  sybsecurity physical device name: 
2. Logical name of the  device: 
3. Size of the device (Meg): 
4. Device  size for auditing:

Select Sybsecurity Physical Device Name.
Enter the full path of the physical device (raw partition) that you located in "Pre-installation tasks " .
```
Enter the physical name of the device to use  for the audit database (default is " "): 
 
/dev/path_to_partition
```
where path_to_partition is the path to the raw partition for the device.
If you specify an operating system file, the following warning appears:
```
WARNING: '/secret1/sybase_dr/install/aud1.dat' is  a regular file which is not recommended for a Server device.
```

Press Return to acknowledge the warning.

auditinit redisplays the Add/Change a New Device for Auditing menu, which displays the physical name of the device:

ADD/CHANGE  A NEW DEVICE FOR AUDITING 
1. sybsecurity physical device name:  /secret1/sybase_dr/install/aud1.dat 
2.  Logical name of the device: 
3. Size of the device: 
4.  Device size for auditing:

Proceed through the remaining items on this menu.
The Size of the Device value must be equal to or greater than the Device Size for Auditing value. The Device Size for Auditing must be equal to the device size. If you are following Sybase auditing guidelines, you do not need to change the value displayed in Device Size for Auditing.

Press CTRL+A to accept the settings. auditinit returns to the Configure Auditing menu and displays the device you have created.

CONFIGURE AUDITING 
 1.  Configure auditing:  yes
 2.   Add a device for audit table(s)
 3.  Add a device for  the audit database transaction log 
 4.  Delete a device  entry 
 5.  Change a device entry 
 
List  of devices for the audit tables:
Logical name   Physical  name    Segment name         Table  name    Size

6.Audit_01' secret1/sybase_dr/install/aud1.dat' sysaudits_01 5

To add multiple audit devices, repeat steps 1-6.

You can add as many as eight devices. Sybase recommends adding three or more audit table devices.

After adding a device, auditinit returns to the Configure Auditing menu and displays all the devices you have created.

CONFIGURE AUDITING 
1.  Configure auditing:  yes 
2. Add a device  for audit table(s) 
3. Add a device for the audit database  transaction log 
4. Delete a device entry 
5.  Change a device entry 
 
List of devices for the  audit tables: 
Logical name    Physical  name    Segment name         Table name     Size
 
6.  Audit_01'   /secret1/sybase_dr/install/aud1.dat' sysaudits_01   5 
7.  Audit_02'   /secret1/sybase_dr/install/aud2.dat' sysaudits_02   5

Creating a device for the audit database transaction log

To create a device for the audit database transaction log:

From the Configure Auditing menu, select Add a Device for the Audit Database Transaction Log.

auditinit displays the Add/Change a New Device for Auditing menu.

ADD/CHANGE  A NEW DEVICE FOR AUDITING 
1.  sybsecurity physical device  name:  
2.  Logical name of the device: 
3.  Size  of the new device (Meg): 
4.  Device size for auditing:

Select Sybsecurity Physical Device Name.
auditinit prompts for the physical name and supplies you with a default, if available:
```
Enter the physical name of the device to use  for the sybsecurity database (default is''): 
 /dev/path_to_partition
```
where path_to_partition is the path to the raw partition for the device.
Enter the full path name of a physical device.
If you enter an operating system file name, the following warning appears:
```
WARNING: '/secret1/sybase_dr/install/audlog' is  a regular file, which is not recommended for a Server device. 
```

Press Return to acknowledge this warning.

auditinit displays the Add/Change a New Device for Auditing menu and the value you selected for the physical name of the device.

ADD/CHANGE A NEW DEVICE FOR AUDITING 
1.sybsecurity  physical device name: 
       /secret1/sybase_dr/install/auditlog.dat
2.Logical  name of the device: 
3.Size of the device: 
4.Device  size for auditing:

Proceed through the remaining items on this menu. As you do so, be aware of the following:
- Sybase recommends a minimum size of 2MB for the size of the transaction log.
- auditinit displays the size in both Size of the Device and in Device Size for Auditing in the Add/Change a New Device for Auditing menu.
- The Device Size for Auditing default value is equal to the size of the device, based on the assumption that you may want to devote the entire device to log for the auditing task. If you want to use only a subset of the device, you can edit the Size of the Device value.

Press Ctrl-a to accept the settings displayed in the Add/Change a New Device for Auditing menu.

auditinit returns to the Configure Auditing menu and displays all the devices you have created.

CONFIGURE AUDITING 
1. Configure auditing:  yes 
2.  Add a device for audit table(s) 
3. Add a device for the  audit database transaction log 
4. Delete a device entry 
5.  Change a device entry 
 
List of devices for the  audit tables: 
Logical name    Physical  name    Segment name         Table name     Size

6. Audit_01'   /secret1/sybase_  dr/install/aud1.dat' sysaudits_01   5 
7.  Audit_02'   /secret1/sybase_  dr/install/aud2.dat' sysaudits_02   5 
8.  auditlog    /secret1/.../auditlog.dat logsegment   syslogs        2

When you are ready to execute the audit configuration, press Ctrl-a. auditinit returns you to the Sybase Server Configuration screen.

Press Ctrl-a again. auditinit prompts with:

Execute the Sybase Server Configuration  now?

Enter "y" (yes).

auditinit executes the tasks to install auditing. When the installation completes successfully, the following messages are displayed:

Running task:  install auditing capabilities.
....................Done
Auditing  capability installed.
Task succeeded: install auditing  capabilities.
Configuration completed successfully.
Press <return> to  continue.

Enabling auditing

After auditing is installed, no auditing occurs until a System Security Officer enables auditing with sp_configure. For more information, see the System Administration Guide.

Deleting a device entry

To delete a device entry:

Select Delete a Device Entry from the Configure Auditing menu.
Enter the number of the device to delete.
Press RETURN.

Changing a device entry

To change a device entry:

Select Change a Device Entry from the Configure Auditing menu.

Enter the number of the device to change.

auditinit displays the Add/Change a New Device for Auditing menu with information on the device you selected:

ADD/CHANGE A NEW  DEVICE FOR AUDITING 
1. sybsecurity physical device name:
       /secret1/sybase_dr/install/audlog
2.  Logical name of the device: aud.log 
3. size of the new  device (Meg): 5
4. Device size for auditing:5

Select each remaining entry you want to change.
Press CTRL+A to save the new entries.


	Expand Search