4D v13.4
On SQL Authentication Database Method
- 4D Language Reference
-
- SQL
-
- Overview of SQL Commands
- On SQL Authentication Database Method
- Begin SQL
- End SQL
- Get current data source
- GET DATA SOURCE LIST
- Is field value Null
- QUERY BY SQL
- SET FIELD VALUE NULL
- SQL CANCEL LOAD
- SQL End selection
- SQL EXECUTE
- SQL EXECUTE SCRIPT
- SQL EXPORT DATABASE
- SQL EXPORT SELECTION
- SQL GET LAST ERROR
- SQL GET OPTION
- SQL LOAD RECORD
- SQL LOGIN
- SQL LOGOUT
- SQL SET OPTION
- SQL SET PARAMETER
- START SQL SERVER
- STOP SQL SERVER
- USE EXTERNAL DATABASE
- USE INTERNAL DATABASE
On SQL Authentication Database Method
The On SQL Authentication Database Method can be used to filter requests sent to the integrated SQL server of 4D. This filtering can be based on the name and password as well as the (optional) IP address of the user. The developer can use their own table of users or that of the 4D users to evaluate the connection identifiers. Once the connection is authenticated, the CHANGE CURRENT USER command must be called in order to control access of requests within the 4D database.
When it exists, the On SQL Authentication Database Method is called automatically by 4D or 4D Server on each external connection to the SQL server. The internal system for managing 4D users is therefore not activated. The connection is only accepted if the database method returns True in $0 and if the CHANGE CURRENT USER command has been executed successfully. If one of these conditions is not met, the request is refused.
Note: The statement SQL LOGIN(SQL_INTERNAL;$user;$password) does not call the On SQL Authentication Database Method since it is an internal connection in this case.
The database method receives up to three parameters of the Text type, passed by 4D ($1, $2 and $3), and returns a Boolean, $0. Here is a description of these parameters:
| Parameters | Type | Description |
| $1 | Text | User name |
| $2 | Text | Password |
| $3 | Text | (optional) IP address of client at origin of the request |
| $0 | Boolean | True = request accepted, False = request refused |
You must declare these parameters as follows:
The password ($2) is received as standard text.
You must check the identifiers of the SQL connection in the On SQL Authentication Database Method. For example, you can check the name and password using a custom table of users. If the identifiers are valid, pass True in $0 to accept the connection. Otherwise, pass False in $0; in this case, the connection is refused.
Note: If the On SQL Authentication Database Method does not exist, the connection is evaluated using the integrated user management system of 4D (if it is activated, in other words, if a password has been assigned to the Designer). If this system is not activated, users are connected with Designer access rights (free access).
If you have passed True in $0, you must then successfully call the CHANGE CURRENT USER command in the On SQL Authentication Database Method in order for the request to be accepted and for 4D to open an SQL session for the user.
The use of the CHANGE CURRENT USER command can be used to implement a virtual authentication system which has the double advantage of allowing the control of connection actions and of hiding the connection identifiers from the outside in the 4D SQL session.
This example of the On SQL Authentication Database Method checks whether the connection request comes from the internal network, validates the identifiers and then assigns access rights to the "sql_user" user for the SQL session.
C_TEXT($1;$2;$3)
C_BOOLEAN($0)
`$1: user
`$2: password
`{$3: IP address of client}
ON ERR CALL("SQL_error")
If(checkInternalIP($3))
`The checkInternalIP method checks whether the IP address is internal
If($1="victor") & ($2="hugo")
CHANGE CURRENT USER("sql_user";"")
If(OK=1)
$0:=True
Else
$0:=False
End if
Else
$0:=False
End if
Else
$0:=False
End if
Product: 4D
Theme: SQL
