DBA authority is required to set an option for the PUBLIC user ID.

Changing the value of an option for the PUBLIC user ID sets the permanent value of the option for all users who have not SET their own value. An option value cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.

Some options which can only be set for the PUBLIC user take effect immediately for existing connections, even though the changed setting will not be visible to users via the CONNECTION_PROPERTY function. An example of this is the global_database_id option. For this reason, PUBLIC-only options should not be changed while other users are connected to the database.

Adding the TEMPORARY keyword to the SET OPTION statement changes the duration of the change. Ordinarily an option change is permanent. It does not change until it is explicitly changed using the SET OPTION statement.

When the SET TEMPORARY OPTION statement is executed, the new option value takes effect only for the current connection, and only for the duration of the connection. This new setting is held only in server memory and is not reflected in the SYSOPTION catalog view.

When the SET TEMPORARY OPTION is used to set a PUBLIC option, the change is in place for as long as the database is running. When the database is shut down, temporary options for the PUBLIC user ID revert back to their permanent value.

Setting a temporary option for the PUBLIC user ID offers a security advantage. For example, when the login_mode option is enabled, the database relies on the login security of the system on which it is running. Enabling it as a temporary option setting means that a database relying on the security of a Windows domain will not be compromised if the database is shut down and copied to a local computer. In this case, the login_mode option will revert to its permanent value, which could be Standard, a mode where integrated logins are not permitted.

The INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements have an OPTION clause that lets you specify how materialized views are used by the statement and how the query is optimized. This clause can also be used to specify an option setting that takes precedence over any public or temporary option settings that are in effect, for that statement only. You can change the setting of the following options in the OPTION clause:

• isolation_level
• max_query_tasks
• optimization_goal
• optimization_level
• optimization_workload
• user_estimates

• INSERT statement
• UPDATE statement
• DELETE statement
• SELECT statement
• UNION statement
• EXCEPT statement
• INTERSECT statement
• isolation_level option
• max_query_tasks option
• optimization_goal option
• optimization_level option
• optimization_workload option
• user_estimates option