FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

AS USER, PASSWORD and ROLE

The optional AS USER, PASSWORD and ROLE clauses allow specification of which user will execute the SQL statement and with which role.The method of user login, and whether a separate connection is opened, depends on the presence and values of the ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE clauses:

  • If ON EXTERNAL is present, a new connection is always opened, and:

    • If at least one of AS USER, PASSWORD and ROLE is present, native authentication is attempted with the given parameter values (locally or remotely, depending on connection_string).No defaults are used for missing parameters

    • If all three are absent, and connection_string contains no hostname, then the new connection is established on the local server with the same user and role as the current connection.The term 'local' means “on the same machine as the server” here.This is not necessarily the location of the client

    • If all three are absent, and connection_string contains a hostname, then trusted authentication is attempted on the remote host (again, 'remote' from the perspective of the server).If this succeeds, the remote operating system will provide the username (usually the operating system account under which the Firebird process runs)

  • If ON EXTERNAL is absent:

    • If at least one of AS USER, PASSWORD and ROLE is present, a new connection to the current database is opened with the supplied parameter values.No defaults are used for missing parameters

    • If all three are absent, the statement is executed within the current connection

Note

If a parameter value is NULL or “''” (empty string), the entire parameter is considered absent.Additionally, AS USER is considered absent if its value is equal to CURRENT_USER, and ROLE if it is the same as CURRENT_ROLE.

Caveats with EXECUTE STATEMENT

  1. There is no way to validate the syntax of the enclosed statement

  2. There are no dependency checks to discover whether tables or columns have been dropped

  3. Execution is considerably slower than when the same statements are executed directly as PSQL code

  4. Return values are strictly checked for data type to avoid unpredictable type-casting exceptions.For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error

All in all, this feature is meant to be used cautiously, and you should always take the caveats into account.If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

See also

[fblangref50-psql-forexec]