SQL Accessors

You can use an external SQL database to authorize subscribers. The sqlaccessor.gen file stores the settings needed by the SQLAccessor plug-in to authorize subscribers. SQLAccessor requires three items of information from the database:

• IMSI
• MSISDN
• Authorization String

This section describes the configuration choices that you need to make to configure sqlaccessor.gen or sqlaccessor_jdbc.gen.

The sqlaccessor.gen file stores the settings used by the SQL data accessor plug-in. It is composed of several sections. Section names are enclosed in square brackets.

Note: For information about the configuration items that are common across all SQL plug-ins, see Common Configuration Items.

Note: The databases used must support stored procedures.

Note: Oracle front-end applications are not supported on a Linux platform. The sqlaccessor.so and sqlaccessor.gen files are specific to Oracle plug-ins and must not be installed on a Linux platform. You must instead use the sqlaccessor_jdbc.gen file.

[Settings] Section

The [Settings] section of the sqlaccessor.gen file defines parameters that control the database connection.

[Query] Section

The [Query] section indicates the SQL statement that uses “select” or “update” information in the SQL database.

[Query/DMUSelect] Section

The [Query/DMUSelect] section contains the SQL statement for the DMUSelect query used to retrieve information from the database.

The SQL statement in the [Query/DMUSelect] section has the following format:

SQL=SELECT MSID, MNAuthenticator, MN_HAAAKey, MN_HAKey, CHAPKey, KeysExpireTime, DMUState FROM databasename WHERE UserName = @NAI 

where:

• MSID—Column name of the SQL database column containing the MSID values.
• MNAuthenticator—Column name of the SQL database column containing the MNAuthenticator values.
• MN_HAAAKey—Column name of the SQL database column containing the MN_HAAA_Key values.
• MN_HAKey—Column name of the SQL database column containing the MN_HA_Key values.
• CHAPKey—Column name of the SQL database column containing the CHAP_Key values.
• KeysExpireTime—Column name of the SQL database column containing the KeyUTCExpireTime values.
• DMUState—Column name of the SQL database column containing the MIPUpdateState values.
• databasename—database identifier
• UserName—Column configuration of the SQL database column containing the NAI values

[Query/DMUUpdate] Section

The [Query/DMUUpdate] section contains the SQL statement used to write DMU information to the database.

The SQL statement in this section also maps the database column names to the corresponding variables.

The SQL statement in the [Query/DMUUpdate] section has the following format:

SQL=UPDATE databasename  SET MN_HAAAKey = @MN_HAAA_Key, MN_HAKey = @MN_HA_Key, CHAPKey = @CHAP_Key, KeysExpireTime = @KeyUTCExpireTime/n, DMUState = @MIPUpdateState/n WHERE UserName = @NAI 

where:

• databasename—database identifier
• MN_HAAAKey—Column name of the SQL database column containing the MN_HAAA_Key values.
• MN_HAKey—Column name of the SQL database column containing the MN_HA_Key values.
• CHAPKey—Column name of the SQL database column containing the CHAP_Key values.
• KeysExpireTime—Column name of the SQL database column containing the KeyUTCExpireTime values.
• DMUState—Column name of the SQL database column containing the MIPUpdateState values.
• UserName—Column name of the SQL database column containing the NAI values

[Results] Section

The [Results] section maps the position of a column name in the SELECT SQL statement with the data needed.

The [Results/DMUSelect] section maps the SQL database column names to the variable names specified in the [VariableTypes] section.

Note: The [Query/DMUUpdate] section does not need a corresponding [Results] section because the mapping between the variable names and the column headings takes place in the SQL statement itself.

[Failure] Section

The [Failure] section of the sqlaccessor.gen file can be used to determine the result of the authentication process (accept or reject) when connectivity to all of the configured SQL databases has failed.

Example: SQL Accessor Configuration File

This section provides an example of an Oracle sqlaccessor.gen configuration file.

[Bootstrap]
LibraryName=radsql_accessor_ora.so
Enable=0

[Settings]
MethodName=SQLAccessor
MethodName=SQL Accessor
Connect=username/password@servicename
SQL=SELECT msisdn, imsi, auth_string FROM tablename WHERE subscriber_id=@KeytoRecord
ParameterMarker=?
MaxConcurrent=1
ConcurrentTimeout=30
WaitReconnect=2
MaxWaitReconnect=360
MaxHardErrorRetries=0

[Server]
s1=2
s2=2

[Server/s1]
Connect=admin1/passwd1@mydb1

[Server/s2]
Connect=admin2/passwd2@mydb2

[Server/s3]
Connect=admin3/passwd3@mydb3
LastResort = 1

[Query]
1=DMUSelect
2=DMUUpdate

[Query/DMUSelect]
SQL=SELECT MSID, MNAuthenticator, MN_HAAAKey, MN_HAKey, CHAPKey, KeysExpireTime, DMUState FROM databasename WHERE UserName = @NAI

[Query/DMUUpdate]
SQL=UPDATE databasename SET MN_HAAAKey = @MN_HAAA_Key, MN_HAKey = @MN_HA_Key, CHAPKey = @CHAP_Key, KeysExpireTime = @KeyUTCExpireTime/n, DMUState = @MIPUpdateState/n WHERE UserName = @NAI

[Results]

[Results/DMUSelect]
MSID = 1/48
MNAuthenticator = 2/48
MN_HAAA_Key = 3/48
MN_HA_Key = 4/48
CHAP_Key = 5/48
MIPUpdateState = 7
KeyUTCExpireTime = 6

[Results/DMUUpdate]

[Failure]
Accept=0
Profile=xyz
FullName=Remote User