|
|
This appendix 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.
This appendix includes the following sections:
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.)
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.
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.
A lock entity keeps track of locks on group or user profiles.
The data table cs_user_profile stores user profile information. See Table F-1.
| 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 log in 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. |
The data table cs_group_profile stores group profile attributes. See Table F-2.
| 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. |
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 F-3
| 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). 10 characters maximum |
| 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).10 characters maximum |
| 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). 10 characters maximum. |
| 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. |
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.
| Column | Description |
| profile_id | Unique identification number for a user or a group profile. The number is 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 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. |
The data table cs_password stores password information of a user or a group profile. See Table F-5. Note that multiple entries can exist for a user or a group 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. 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 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. |
The data table cs_privilege stores privilege information of a group or a user profile. See Table F-6. Note that multiple entries can exist for a user or group 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. 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. |
The data table cs_lock keeps track of all locks held on group or user profiles. See Table F-7.
| 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 ACS database server on which this lock was issued. |
| host_name | Name of the host machine on which the database server is running. |
If profile caching is enabled on the ACS, as it usually is, special tables and triggers to update the profile cache may 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 entries are inserted into this table by triggers associated with cs_user_profile or cs_group_profile.
| 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_timestam p | Time and Date when transaction occurred. Used by dbserver to purge old entries. Index is created on this column. |
Two data tables exist for storing accounting data:
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.
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 255-byte pieces and each row stores a single piece.
| 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. |
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 F-10.
| 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:
Index is created on this column. | ||||||||||
| nas_name | NAS name. Variable length column up to 255 bytes. | ||||||||||
| user_name | User name. Variable length column up to 255 bytes. | ||||||||||
| port | Port name. Variable length column up to 255 bytes. | ||||||||||
| address | Protocol address information. 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. |
The dictionary tables listed in this section identify the mapping between attributes and database objects. Three of the tables listed below 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.
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 the appendix "Setting up Database Replication among CiscoSecure ACSes" for more details.
| 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 the appendix "Setting up Database Replication among CiscoSecure ACSes," for more details.
| Column | Description |
|---|---|
| Id | Profile ID number available for recycle. Primary key is created on this column. |
Figure F-1, Figure F-2, and Figure F-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.
This section defines how to perform an offline backup, online backup, and restoration of the SQLAnywhere database.
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 1 Shut down the database by entering the following command:
# /etc/rc2.d/K80CiscoSecure
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.
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.
Step 1 Set the proper environment variable as follows:
(a) Set SQLANY to CiscoSecure install directory /SYBSsa50
(b) Set SATMP to $SQLANY/tmp.
Step 2 Add $SQLANY/lib to the LD_LIBRARY_PATH environment variable.
Step 3 Add $SQLANY/bin to the PATH environment variable.
Step 4 Execute the dbbackup command as shown in the following example:
dbbackup -c "ENG=csecure;UID=DBA;PWD=SQL" -x target directory
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.
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:
1 ) If both files are corrupted, replace them with their copies from the backup media.
2 ) 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:
cd name of database directory
dbeng50 csecure.db -a csecure.log
3 ) If the database file is not corrupted and the transaction log is damaged, enter the following commands:
cd name of database directory
dbeng50 csecure.db -f
|
|