CiscoSecure ACS Database Structure

This chapter presents information on the CiscoSecure Access Control Server (ACS) database structure. It provides programmers and system administrators with database information they can use to write custom programs that access the CiscoSecure ACS.

The database structure consists of three main entities: group or user profile, accounting information, and internal dictionary data. (The details of the internal dictionary data are not provided here because the information it contains cannot be changed.)

The database structure provides valuable data regarding the location of passwords, attributes, accounting data, and other related information, as shown in the data models in this chapter. This chapter also provides information you need to locate key information so that you can act on it later (modify, sort, redistribute it, and so forth), as your needs determine. For example, you might want to extract information contained in the accounting table and transfer it to a spreadsheet for billing purposes.

This section provides the structure of the database tables stored in a database, such as SQLAnywhere, that came bundled with your CiscoSecure ACS. Other supported databases include Oracle and Sybase SQL Server.

Group or User Profile Data

Because the group profile and user profile share a common set of attributes, a parent entity profile is created to factor out the common set. Group profiles and user profiles exist to store an attribute that could be unique to a particular profile, or to store an attribute that could be accessed frequently.

Password and privilege are treated as separate entities, because they are both multivalued attributes. They form a dependent relationship to the profile. Each profile could have any number (including zero) of instances of each dependent entity.

cs_user_profile table

The data table cs_user_profile stores user profile information. (See Table 20-1 .)

Table 20-1 cs_user_profile
Column	Description
profile_id	Unique identification number for a user profile. The number is a 4-byte signed integer number greater than 0. Primary key is created on this column.
user_name	Username. Variable length character column up to 255 bytes. Unique index is created on this column.
failed_login_count	Current count of failed login attempts for a user.
cycle_number	Sequence number is a 4-byte integer to keep track of updates on a profile. The number starts at 0.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record insert and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

¹Oracle database replication supports restricted time stamp-based conflict resolution for the CiscoSecure ACS. Sybase database replication does not provide native time stamp-based conflict resolution. See "Setting Up Database Replication among CiscoSecure ACSes," for details.

cs_group_profile table

The data table cs_group_profile stores group profile attributes. (See Table 20-2.)

Table 20-2 cs_group_profile
Column	Description
profile_id	Unique identification number for a group profile. The number is a 4-byte signed integer number greater than 0. Primary key is created on this column.
group_name	Group name. Variable length character column up to 255 bytes. Unique index is created on this column.
cycle_number	Sequence number is a 4-byte integer to keep track of updates on a profile. The number starts at 0.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record, insert, and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

cs_profile table

The data table cs_profile stores group and user profile attribute values. This table represents a super entity profile that is used to factor out the common set of attributes in users and groups. (See Table 20-3.)

Table 20-3 cs_profile
Column	Description
profile_id	Unique identification number for a user or a group profile. The number is a 4-byte signed integer number greater than 0. Primary key is created on this column.
member	Member name to which this profile belongs. Variable length character column up to 255 bytes. NULL if no membership is specified.
expire_date	Date after which this profile is expired. NULL if no date is specified.
qual_expire_date	The expiration date qualifier field. Holds extra status information pertaining to the value in the expire_date field (for example, absolute or not). Maximum is 10 characters.
valid_date	Date after which this profile becomes valid. NULL if no date is specified.
qual_valid_date	The valid date qualifier field. Holds extra status information pertaining to the value in the valid_date field (for example, absolute or not). Maximum is 10 characters.
profile_status	Status of this profile. Either enabled or disabled. NULL if nothing is specified.
max_session	Maximum number of login sessions allowed. Currently, this only applies to user profile.
qual_max_session	Max sessions qualifier field. Holds extra status information pertaining to the value in the max_session field (for example, absolute or not). Maximum is 10 characters.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record, insert, and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

cs_profile_blob table

This table stores all the attributes that are not stored as a separate column for a user or a group profile. The attributes are broken into 255-byte pieces. The blob_ordinal column indicates the order of the pieces in case there are multiple pieces for a profile. Notice that there could be multiple entries for a user or a group profile.

Table 20-4 cs_prfile_blob
Column	Description
profile_id	Unique identification number for a user or a group profile. The number is a 4-byte signed integer number greater than 0. Primary key is created on this column and blob_ordinal.
blob ordinal	Ordinal number for the order of blob_data piece for a profile. The number is a 2-byte signed integer and starts at 1.
blob_data	Attribute blob data piece up to 255 bytes.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record insert and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

cs_password table

The data table cs_password stores password information of a user or a group profile. (See Table 20-5.) Note that multiple entries can exist for a user or a group profile.

Table 20-5 cs_password
Column	Description
profile_id	Unique identification number for a user or a group profile. The number is a 4-byte signed integer number greater than 0. Unique composite index is created on profile_id and pwd_type.
pwd_type	Password type. Variable length column up to 32 bytes. Should be specified.
pwd_value	Password itself or external file specification. Variable length column up to 255 bytes. NULL if not specified.
pwd_from_date	Date on which the password becomes valid. NULL if no date is specified.
pwd_until_date	Last date the password is valid. NULL if no date is specified.
pwd_opaque	Any other data specified after password until date on the password attribute value list. Up to 255 bytes. NULL if nothing specified.
pwd_qualifier	The password qualifier field. Holds extra status information pertaining to the password-related values (for example, absolute or not). Maximum is 10 characters.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record, insert, and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

cs_privilege table

The data table cs_privilege stores privilege information of a group or a user profile. (See Table 20-6.) Note that multiple entries can exist for a user or group profile.

Table 20-6 cs_privilege
Column	Description
profile_id	Unique identification number for a user or a group profile. The number is a 4-byte signed integer number greater than 0. Unique composite index is created on profile_id, priv_type, and priv_value.
priv_type	Privilege password type. Variable length column up to 32 bytes. Required.
priv_value	Privilege password or external file specification. Variable length column up to 255 bytes. NULL if not specified.
priv_level	Privilege level. Integer between 0 and 15. Required.
nas_name	NAS name. Variable length column up to 255 bytes. NULL if not specified.
port	Port name. Variable length column up to 255 bytes. NULL if not specified.
remote_address	Remote address. Variable length column up to 255 bytes. NULL if not specified.
priv_from_date	Date on which the privilege password becomes valid. NULL if not specified.
priv_until_date	Last date the privilege password is valid. NULL if not specified.
priv_opaque	Any other data specified after privilege until date on the privilege attribute value list. Up to 255 bytes. NULL if nothing is specified.
priv_qualifier	The privilege qualifier field. Holds extra status information pertaining to the privilege-related values (for example, absolute or not). Maximum is 10 characters.
profile_ts	The date and time stamp, expressed in Universal Time, coordinated (Greenwich Mean Time), to record, insert, and update time on this profile data row. The default is set to 01-01-1970. This column could be used for replication conflict resolution.¹

cs_lock table

The data table cs_lock keeps track of all locks held on group or user profiles. (See Table 20-7.)

Table 20-7 cs_lock
Column	Description
profile_id	Unique identification number for a group or user profile. The number is a 4-byte signed integer number greater than 0. Primary key is created on this column.
profile_type	Profile type. Either group or user.
profile_name	Either username or group name of a locked profile. Variable length column up to 255 bytes.
timestamp	Date and time this lock was last issued.
session_id	ID number of session in which this lock is obtained.
lock_owner	Name of the end user who requested the lock, or NULL if unknown.
lock_type	Either read lock or write lock. The write lock is internally generated.
dbserver_name	Name of the CiscoSecure ACS database server on which this lock was issued.
host_name	Name of the host machine on which the database server is running.

Profile Cache Update Data

If profile caching is enabled on the CiscoSecure ACS, as it usually is, special tables and triggers to update the profile cache might need to be enabled if database replication, or some third party application is implemented that directly modifies the CiscoSecure profile database without using the CiscoSecure dbserver module.

•

The cs_trans_log table—Keeps track of profile data changes which are made outside of the CiscoSecure dbserver module. The information from this table is used by the dbserver to notify the ACS server to invalidate an outdated profile cache.

•

Profile cache update triggers—Can be attached to the cs_user_profile and cs_group_profile tables. When changes are made to those tables by processes operating outside the dbserver module, the triggers write a record of those changes to the cs_trans_log table.

Profile cache update triggers are installed in the process of implementing Oracle or Sybase database replication. If database replication is not implemented, installation is optional, depending upon whether or not profile caching is enabled and whether the customer is modifying the CiscoSecure profile database directly through a third-party application.

cs_trans_log

The entries are inserted into this table by triggers associated with cs_user_profile or cs_group_profile.

Table 20-8 cs_trans_log
Column	Description
t_seq_id	4-byte signed integer which uniquely identifies each transaction. For fast query, primary key is created on this column.
t_profile_id	Profile ID in 4-byte signed integer.
t_profile_type	2-byte signed integer to indicate the type of profile 1: user profile, 2: group profile
t_profile_name	User or group profile name in variable length character column up to 255 bytes.
t_profile_cycle	Profile cycle number in 4-byte signed integer.
t_action	2-byte signed integer to indicate each transaction type 1: CREATE, 2: UPDATE, 3: DELETE, 4: REPLACE
t_timestamp	Time and date when transaction occurred. Used by dbserver to purge old entries. Index is created on this column.

Accounting Data

•

The cs_accounting_log data table—Contains each accounting record ID as it is passed to the database server and stores all the accounting raw data as passed to the database server in its blob_data column. (See Table 20-8.) This includes user accounting data and system accounting data.

•

The cs_user_accounting data table—Contains user accounting information for start and stop time as well as other information extracted from the raw accounting data. This entity maintains start_logid and stop_logid to the actual accounting log records in the accounting_log.

When accounting records in the cs_user_accounting and cs_accounting_log tables are exported and deleted by the `AcctExport' tool, a temporary table - cs_accnt_tmp - is created if there are any active sessions. The temporary table is removed when the export is done.

cs_accounting_log table

This table stores all the accounting raw data as the data is passed to the database server. This includes user accounting data as well as system accounting data. A record is broken into pieces of 255 bytes each and each row stores a single piece.

Table 20-9 cs_accounting_log
Column	Description
log_id	Unique identification number for an accounting raw record. The number is a signed 4-byte integer greater than 0. Primary key is created on this column.
blob_ordinal	Ordinal number for the order of blob_data piece. The number is a 2-byte signed integer and starts at 1.
blob_data	Raw accounting data record.

cs_user_accounting

The data table cs_user_accounting stores accounting information related to start time and stop time of a user session. It also stores log_ids of the raw accounting data in the cs_accounting_log table for start or stop events. (See Table 20-10.)

Table 20-10 cs_user_accounting
Column	Description
record_id	Unique identification number for an accounting raw record. The number is a signed 4-byte integer greater than 0. Primary key is created on this column.
record_status	Status in short integer value: 1. active-user 2. closed-user 3. closed-system shutdown 4. closed-system reload 5. closed-dangling Index is created on this column.
nas_name	NAS name. Variable-length column up to 255 bytes.
user_name	Username. Variable-length column up to 255 bytes.
port	Port name. Variable-length column up to 255 bytes.
Remote ID	The remote user location, which can be either the remote IP address or the caller-ID. Variable length column up to 255 bytes.
start-time	Timestamp for session start.
stop_time	Timestamp for session stop.
task_id	Task ID. Variable-length column up to 255 bytes.
start_logid	Log_id of raw accounting record in cs_accounting_log table for start event.
stop_logid	Log_id of raw accounting record in cs_accounting_log table for stop event.

Dictionary Data

The dictionary tables listed in this section identify the mapping between attributes and database objects. Three of these tables contain information internal to CiscoSecure ACS and are only listed. Two of the tables need to be with database utilities and are described in more detail.

•

cs_profile_attr_dict—Stores attribute information related to attribute labels and physical table locations. This information is internally used by the dbserver module to insert attribute values into proper physical tables or to construct proper profile data out of database tables. Any attributes that do not appear in this entity will be treated as a blob.

•

cs_profile_col_dict—Stores attribute information related to physical columns that store the actual values.

•

cs_user_accounting_attr_dict—Used by the dbserver module to map accounting attributes to physical table and column names and validate size limit of data.

•

cs_id—Used by the dbserver module to generate a unique internal profile ID number for profile and accounting records. This table is modified by administrators implementing database replication. Its structure is described in Table 20-10.

•

cs_deleted_id—Used by the dbserver module to recycle internal profile ID numbers for deleted profiles. This table is modified by administrators implementing database replication. Its structure is described in Table 20-11.

cs_id

The cs_id table is used by the dbserver module to generate unique internal identification numbers for profiles and accounting records. The max_profile number indicates the maximum internal profile id number allowed in the database. This number is used to prevent internal profile id number collision under a replication environment where profiles are created in multiple CiscoSecure databases. See "Setting Up Database Replication among CiscoSecure ACSes," for more details.

cs_deleted_id

Table 20-11 cs_id
Column	Description
Type	Record type: `profile', `accounting_log', `user_accounting', `schema_version' or `max_profile'. Primary key is created on this column.
Id	Current ID number in 4-byte non-negative integer.

The cs_deleted_id table is used by the dbserver module to recycle profile ID numbers for the deleted profiles. When a profile is deleted, its profile id number will be inserted into this table. When a new profile ID is needed and this table is not empty, then a profile ID number from this table will be used for the new profile. See "Setting Up Database Replication among CiscoSecure ACSes," for more details.

CiscoSecure Table Charts

Table 20-12 cs_delete_id
Column	Description
Id	Profile ID number available for recycle. Primary key is created on this column.

Figure 20-1, Figure 20-2, and Figure 20-3 in this section list the physical tables and columns that are created on Oracle-based, Sybase-based, and SQLAnywhere-based CiscoSecure profile databases.

Index names in the charts are preceded by folder icons, the index keys are underlined.

Note One additional index name, cs_profile_member, is included in the cs_profile table, though it is not shown in the following three figures.

Backing Up the SQLAnywhere Database

This section defines how to perform an offline backup, online backup, and restoration of the SQLAnywhere database.

Offline Backup

As an offline solution, the SQLAnywhere database file can be copied to any standard backup media, or to a directory designated for backup files as follows:

Step 2

Copy the database file, csecure.db, to your specified backup media or directory.

The csecure.db file is located under the database directory that you specified when you initially installed the CiscoSecure ACS. For example, if you specified the database directory as /usr/CiscoSecure/database, you would copy /usr/CiscoSecure/database/csecure.db to your designated backup location.

Step 3

Copy the transaction log file, csecure.log, to your specified backup media or directory.

Like the database file, the csecure.log file is located under the database directory that you specified when you initially installed the CiscoSecure ACS.

Step 4

After backing up the transaction log, csecure.log, delete it; otherwise the log file will continue to record transactions and grow indefinitely.

The transaction log is written by SQLAnywhere and is used for recovery purposes. However, because the database is already successfully shut down, the log file can be removed without any consequences.

Online Backup

This section defines how to back up the SQLAnywhere database while the CiscoSecure ACS is up and running by executing the dbbackup command. The environment variables required to run the command can be set once in the user's shell startup file like .profile to avoid setting them repeatedly.

SQL is the default password for the database access account when the database was created. If this password has changed, then provide the new password. The target directory refers to the backup directory where you want to save the database. The -x option truncates the transaction log.

Restoring Your SQLAnywhere Database

This section describes how to restore your SQLAnywhere database from backup media generated by the dbBackup program.

Two files are required for the restore operation: the database file (csecure.db) and the transaction log file (csecure.log). The backup of your SQL Anywhere database contains both of these files. Depending on the extent of damage to your SQL Anywhere database, you perform the restore operation by copying one or both of these files according to the three conditions that follow:

If both files are corrupted, replace them with their copies from the backup media.

If the database file is corrupted (but the transaction log is not), replace the corrupted file with the database file found on the backup media; then enter the following commands to incorporate any changes in the transaction log into the database file:

If the database file is not corrupted and the transaction log is damaged, enter the following commands:

Table Of Contents