SQL Accounting Header (.acc) File

[Contents] [Prev] [Next] [Index] [Report an Error] [No Frames]

Steel Belted Radius Carrier 7.0 Reference Guide > SQL Accounting File > SQL Accounting Header (.acc) File
SQL Accounting Header (.acc) File

The header file used to configure SQL accounting methods must have a .acc extension: for example, radsql.acc. The format of a header file is comparable to that of a Windows INI file: composed of several sections; section names are enclosed in brackets; each section may contain multiple parameter/value pairs.

[Bootstrap] Section

The [Bootstrap] section of the SQL accounting header file (Table 128) specifies information used to load and start the SQL accounting module.
[Bootstrap]
LibraryName=radsql.acc 
Enable=0
InitializationString=
Table 128: *.acc [Bootstrap] Syntax

Parameter

Function

LibraryName

Specifies the name of the SQL accounting module.

Enter radsql_acct_ora.so (for Oracle) or radsql_acct_jdbc.so (for JDBC).

Enable

Specifies whether the SQL accounting method is enabled.

If set to 0, SQL accounting is disabled.

If set to 1, SQL accounting is enabled.

Default value is 0.

InitializationString

Not used.

[Settings] Section

The [Settings] section of the SQL accounting header (Table 129) file defines parameters that control the database connection.

.

Table 129: *.acc [Settings] Syntax

Parameter

Function

ConcurrentTimeout

Specifies the number of seconds a request may wait for execution before it is discarded. Since there may be up to MaxConcurrent SQL statements executing at one time, as new requests arise they must be queued, waiting for other statements to complete.

ConcurrentTimeout may be overridden for any particular statement in the [Type/statement] section for that statement.

NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc). For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.

Connect

Specifies the string that must be passed to the database client engine to establish a connection to the database. This string has (or refers to) information about the name of the database, its location on the network, the password required to access it, and so forth.

The format of the connect string depends on the type of database you use:

Oracle:
Connect=<dB_username>/<dB_password>

JDBC: Connect=DSN=<jdbc:provider:driver:dsn_name_here>;UID=<username_for_dB>;PWD=<password_for_dB>

ConnectDelimiter

(JDBC only) Specifies the character used to separate fields (DSN, UID, PWD) in the connect string.

Default is ; (semicolon). If the JDBC connect string requires use of semicolons as part of a field value, you can use this parameter to specify a different delimiter, such as ^ (caret).

ConnectTimeout

Specifies the number of seconds to wait when attempting to establish the connection to the database before timing out. This value is passed to the client database engine, which may or may not implement the feature.

Driver

(JDBC only) Specifies the third-party JDBC driver to load for accounting. For example:

Driver=com/provider/jdbc/sqlserver/
SQLServerDriver

NOTE: Third-party JDBC drivers must be installed in /radius/jre/lib/ext. Refer to the JDBC driver documentation for information on how to install the JDBC driver and supporting files.

LogLevel

Activates logging for the SQL accounting component and sets the rate at which it writes entries to the server log file (.LOG). The LogLevel may be the number 0, 1, or 2, where 0 is the lowest logging level, 1 is intermediate, and 2 is the most verbose. If the LogLevel that you set in the .acc file is different than the LogLevel in radius.ini, the radius.ini setting determines the rate of logging.

Default value is 2.

The LogLevel is re-read whenever the server receives a HUP signal.

MaxConcurrent

Specifies the maximum number of instances of a single SQL statement that may be executing at one time.

MaxConcurrent may be overridden for any particular statement in the [Type/statement] section for that statement.

NOTE: The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc).

NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.

MaxWaitReconnect

Specifies the maximum number of seconds to wait after successive failures to reconnect after a failure of the database connection.

WaitReconnect specifies the time to wait after failure of the database connection. This value is doubled on each failed attempt to reconnect, up to a maximum of MaxWaitReconnect.

ParameterMarker

Specifies the character or sequence of characters used as the parameter marker in a parameterized SQL query.

Default is ? (question mark).

QueryTimeout

Specifies the number of seconds to wait for the execution of a SQL statement to complete before timing out. This value is passed to the database engine, which may or may not implement the feature.

QueryTimeout may be overridden for any particular statement in the [Type/statement] section for that statement.

UpperCaseName

Specifies whether the user's login name is converted to uppercase characters before using it in the SQL statement execution. Set this entry to 1 to convert the name to uppercase, set it to 0 to use the name exactly as received.

UTC

This entry is set to 0 to show time information in local time, or 1 to show time information in universal time coordinates (UTC).

WaitReconnect

Specifies the number of seconds to wait after a failure of the database connection before trying to connect again.

[Type] Sections

Each entry in the [Type] section of the SQL accounting header file maps an Acct-Status-Type attribute value to a statement name that you may assign arbitrarily. The statement name is then used to look up another section in the header file that describes that statement. The secondary section names are composed as [Type/statement], where statement is the arbitrarily assigned name for the statement.

For example, to perform separate accounting updates for network access device and user activity, you might provide the following [Type] and [Type/statement] sections:
[Type]
1=user
2=user
3=user
7=nas
8=nas
639=nas 
28=nas
[Type/user]
   SQL=INSERT INTO usagelog \
         (Time, NASAddress, SessionID, \
         Type, Name, BytesIn, BytesOut) \
       VALUES \
         (%TransactionTime/t, %NASAddress, \
         @Acct-Session-Id, @Acct-Status-Type, \
         %FullName/40s, @Acct-Input-Octets, \
         @Acct-Output-Octets)
[Type/nas]
    SQL=INSERT INTO  ... 
Note the numeric values used in the preceding [Type] section. The Acct-Status-Type values 1, 2, 3, 7, and 8 have been reserved by the RADIUS accounting standard with names and meanings, as described in Table 130.

Table 130: Acct-Status-Type Values

Acct-Status-Type Value

Name

Meaning

1

Start

A user session has started.

2

Stop

A user session has stopped, request contains final statistics.

3

Interim

A user session is in progress, request contains current statistics.

7

Accounting-On

The network access device has started.

8

Accounting-Off

The network access device is about to shut down.

Additional values for Acct-Status-Type have been defined by network access device vendors for use with their equipment. These vendor-specific values may also be listed in the [Type] section.

[Type/statement] Sections

Table 131 lists the parameters that may be present in a [Type/statement] section of the SQL accounting header file.

Table 131: *.acc [Type|statement] Syntax
Parameter

Function
SQL
Specifies the exact SQL statement used to update the SQL database with accounting information. The SQL statement may be broken over several lines by ending each line with a backslash. The backslash must be preceded by a space character, and followed by a newline. The subsequent lines may be indented for better readability. For example:
SQL=INSERT INTO accounting\
(TransTime, FullName, Authenticator, NASName, \

NASAddress, Type, PacketsIn, PacketsOut) \
VALUES (%TransactionTime/t, %FullName/40s, \

%AuthType/40s, %NASName/40s, %NASAddress, \

%Type, @Acct-Input-Packets/n, \

@Acct-Output-Packets/n)
NOTE: Include the /t (timestamp) data type qualifier with the %TransactionTime argument in SQL statements. If you do not, the %TransactionTime output is formatted as character, with differing results on JDBC and Oracle.
MaxConcurrent

If present, MaxConcurrent overrides the value of MaxConcurrent specified in the [Settings] section for this particular statement.

NOTE: The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc).

NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.

ConcurrentTimeout

If present, ConcurrentTimeout overrides the value of ConcurrentTimeout specified in the [Settings] section for this particular statement.

QueryTimeout

If present, QueryTimeout overrides the value of QueryTimeout specified in the [Settings] section for this particular statement.
[TypeNames] Section

Each entry in the [TypeNames] section of the SQL accounting header file maps an Acct-Status-Type attribute value to a string. If a %Type parameter is present in the corresponding SQL statement, this %Type parameter contains the given string.

If no string is given for a particular Acct-Status-Type, when an accounting request of that type is received, %Type is set to the numeric value of the Acct-Status-Type attribute, formatted as a string.

The syntax for the [TypeNames] section is:
[TypeNames]
TypeID=TypeName
TypeID=TypeName
M
You can include RADIUS standard and vendor-specific accounting packet types; for example:
[TypeNames]
1=Start
2=Stop
3=Interim
7=On
8=Off
639=AscendType 
28=3ComType
Working With Stored Procedures

A stored procedure is a sequence of SQL statements that form a logical unit and perform a particular task. You can use stored procedures to encapsulate a set of queries or operations that can be executed repeatedly on a database server. For example, you can code operations on an employee database, such as password lookup, as stored procedures that can be executed by application code. For more information on stored procedures, see the Steel-Belted Radius Carrier Administration and Configuration Guide.

The SQL example in the previous section can be replaced by a custom stored procedure. This stored procedure might look something like the following:
PROCEDURE myProc
  (
        ttime   in      varchar2,
        nasaddr in      varchar2,
        sessid  in      varchar2,
        ttype   in      varchar2,
        uname   in      varchar2,
        bytein  in      varchar2,
        byteout in      varchar2
  );
END myProc;
CREATE OR REPLACE PACKAGE BODY myPack1 IS
  PROCEDURE myProc
  (
        ttime   in      varchar2,
        nasaddr in      varchar2,
        sessid  in      varchar2,
        ttype   in      varchar2,
        uname   in      varchar2,
        bytein  in      varchar2,
        byteout in      varchar2
  )
  IS
  BEGIN
      INSERT INTO usagelog 
        ( Time, NASAddress, SessionID, Type, Name,
          BytesIn, BytesOut )
      VALUES
        ( ttime, nasaddr, sessid, ttype, uname, bytein,
          byteout );
  END myProc;
END myPack1;
When you invoke the stored procedure, delineate each parameter as an input (!i), output (!o), or input/output (!io) variable.

This stored procedure can be invoked with the following connect string in the radsql.acc file:
SQL=BEGIN myPack1.myProc(%TransactionTime!i,
  %NASAddress!i, @Acct-Session-Id!i, %Type!i,
  %FullName!i, @Acct-Input-Packets!i,
  @Acct-Output-Packets!i); END;
Load Balancing Example

The following excerpt from a .acc example file configures load balancing between two SQL servers (so that the work load is shared nearly equally between two servers). The tradeoff with this technique is that the data is split between two servers and must be reintegrated when processed. For example, the Accounting-START for an end user may be stored on one server and the corresponding Accounting-STOP on the other.
[Server]
s1=2
s2=2
[Server/s1]
Connect=system/*********@thor
[Server/s2]
Connect=system/*********@odin
[Type]
1=User
2=User
3=User
[Type/User]
SQL=INSERT INTO acct1(TransTime, FullName, \
       Authenticator, NASName, NASAddress, Type, \
       PacketsIn, PacketsOut) \
    VALUES (%TransactionTime/t, %FullName/40s, \
       %AuthType/40s, %NASName/40s, %NASAddress, \
       %Type, @Acct-Input-Packets/n, \
       @Acct-Output-Packets/n)

Parameter	Function
LibraryName	Specifies the name of the SQL accounting module. Enter radsql_acct_ora.so (for Oracle) or radsql_acct_jdbc.so (for JDBC).
Enable	Specifies whether the SQL accounting method is enabled. If set to 0, SQL accounting is disabled. If set to 1, SQL accounting is enabled. Default value is 0.
InitializationString	Not used.

Parameter	Function
ConcurrentTimeout	Specifies the number of seconds a request may wait for execution before it is discarded. Since there may be up to MaxConcurrent SQL statements executing at one time, as new requests arise they must be queued, waiting for other statements to complete. ConcurrentTimeout may be overridden for any particular statement in the [Type/`statement`] section for that statement. NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc). For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.
Connect	Specifies the string that must be passed to the database client engine to establish a connection to the database. This string has (or refers to) information about the name of the database, its location on the network, the password required to access it, and so forth. The format of the connect string depends on the type of database you use: Oracle: Connect=<dB_username>/<dB_password> JDBC: Connect=DSN=<jdbc:provider:driver:dsn_name_here>;UID=<username_for_dB>;PWD=<password_for_dB>
ConnectDelimiter	(JDBC only) Specifies the character used to separate fields (DSN, UID, PWD) in the connect string. Default is ; (semicolon). If the JDBC connect string requires use of semicolons as part of a field value, you can use this parameter to specify a different delimiter, such as ^ (caret).
ConnectTimeout	Specifies the number of seconds to wait when attempting to establish the connection to the database before timing out. This value is passed to the client database engine, which may or may not implement the feature.
Driver	(JDBC only) Specifies the third-party JDBC driver to load for accounting. For example: Driver=com/provider/jdbc/sqlserver/ SQLServerDriver NOTE: Third-party JDBC drivers must be installed in /radius/jre/lib/ext. Refer to the JDBC driver documentation for information on how to install the JDBC driver and supporting files.
LogLevel	Activates logging for the SQL accounting component and sets the rate at which it writes entries to the server log file (.LOG). The LogLevel may be the number 0, 1, or 2, where 0 is the lowest logging level, 1 is intermediate, and 2 is the most verbose. If the LogLevel that you set in the .acc file is different than the LogLevel in radius.ini, the radius.ini setting determines the rate of logging. Default value is 2. The LogLevel is re-read whenever the server receives a HUP signal.
MaxConcurrent	Specifies the maximum number of instances of a single SQL statement that may be executing at one time. MaxConcurrent may be overridden for any particular statement in the [Type/`statement`] section for that statement. NOTE: The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc). NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.
MaxWaitReconnect	Specifies the maximum number of seconds to wait after successive failures to reconnect after a failure of the database connection. WaitReconnect specifies the time to wait after failure of the database connection. This value is doubled on each failed attempt to reconnect, up to a maximum of MaxWaitReconnect.
ParameterMarker	Specifies the character or sequence of characters used as the parameter marker in a parameterized SQL query. Default is ? (question mark).
QueryTimeout	Specifies the number of seconds to wait for the execution of a SQL statement to complete before timing out. This value is passed to the database engine, which may or may not implement the feature. QueryTimeout may be overridden for any particular statement in the [Type/`statement`] section for that statement.
UpperCaseName	Specifies whether the user's login name is converted to uppercase characters before using it in the SQL statement execution. Set this entry to 1 to convert the name to uppercase, set it to 0 to use the name exactly as received.
UTC	This entry is set to 0 to show time information in local time, or 1 to show time information in universal time coordinates (UTC).
WaitReconnect	Specifies the number of seconds to wait after a failure of the database connection before trying to connect again.

Acct-Status-Type Value	Name	Meaning
1	Start	A user session has started.
2	Stop	A user session has stopped, request contains final statistics.
3	Interim	A user session is in progress, request contains current statistics.
7	Accounting-On	The network access device has started.
8	Accounting-Off	The network access device is about to shut down.

Parameter	Function
SQL	Specifies the exact SQL statement used to update the SQL database with accounting information. The SQL statement may be broken over several lines by ending each line with a backslash. The backslash must be preceded by a space character, and followed by a newline. The subsequent lines may be indented for better readability. For example: SQL=INSERT INTO accounting\ (TransTime, FullName, Authenticator, NASName, \ NASAddress, Type, PacketsIn, PacketsOut) \ VALUES (%TransactionTime/t, %FullName/40s, \ %AuthType/40s, %NASName/40s, %NASAddress, \ %Type, @Acct-Input-Packets/n, \ @Acct-Output-Packets/n) NOTE: Include the /t (timestamp) data type qualifier with the %TransactionTime argument in SQL statements. If you do not, the %TransactionTime output is formatted as character, with differing results on JDBC and Oracle.
MaxConcurrent	If present, MaxConcurrent overrides the value of MaxConcurrent specified in the `[Settings]` section for this particular statement. NOTE: The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc). NOTE: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. For more information, see "Overlapped Execution of SQL Statements" in Chapter 18, Configuring SQL Authentication of the Steel-Belted Radius Carrier Administration and Configuration Guide.
ConcurrentTimeout	If present, ConcurrentTimeout overrides the value of ConcurrentTimeout specified in the `[Settings]` section for this particular statement.
QueryTimeout	If present, QueryTimeout overrides the value of QueryTimeout specified in the `[Settings]` section for this particular statement.

[Contents] [Prev] [Next] [Index] [Report an Error] [No Frames]