How to: OutSystems Platform Database Users

Back to Forums

Miguel João

Staff

How to: OutSystems Platform Database Users

Discussion

OS Certified

Authentication

Database

How-to

Hello All

Every now and then we receive questions from our customers, partners and enthusiasts, wondering how the Agile Platform accesses the main database, with which users, priviledges and security levels. Most of the times, these questions arise from security concerns, or security policies in place, to control the database RDBMS access by applications and application users.

On that note, let me try to answer some of the most common questions on the subject:

What mode of database authentication does the Agile Platform support?

The only database authentication mode currently supported is the RDBMS user (either SQL Server or Oracle local user). We use 3 RDBMS users to access and manage the applications data model, plus 1 RDBMS user to access the session data model. The accounts are local RDBMS accounts and there’s no current support for domain (windows) accounts to be used instead.

What database users are used and how to create them?

There are in total 4 RDBMS users used by OutSystems Applications and Agile Platform: the hubadmin, the hubruntime, the hublog and the hubstate. Depending on the RDBMS engine used, the users must be created by the Configuration Tool, or manually using provided PL/SQL statements.

For the SQL Server RDBMS, the users must be created using the Configuration Tool alone, with the Grant Permissions button for each user. During this step, a SQL Server login dialog will popup, requiring to input a SQL Server administrative user, which has privileges to create the configured users. This administrative user, typically the sa user, is used once by the Configuration Tool to bootstrap (create and grant privileges) the configured users, and it will never be used again.
For the Oracle RDBMS, the users must be created using a provided PL/SQL script, which upon customization to set the correct username and user's password, should be run directly on the Oracle RDBMS using a PL/SQL client tool (like SQL Plus or SQL Developer), using a login user with enough privileges to bootstrap the users. Typically, the sys local account is used in this step. This super user account will never be required again.

What privileges does these users have?

As already refered, the Agile Platform users 4 RDBMS users.

For SQL Server RDBMS:

HubAdmin has db_accessadmin, db_datareader, db_datawriter, db_ddladmin and db_securityadmin roles on the OutSystems catalog. This user has access to the OutSystems catalog to create and alter the database managed data model (all OutSystems eSpace Entities and the OutSystems system Entities), and to select, insert, update and delete rows on the managed data model. Note that Foreign Entities (entities defined through extensions) are not managed by OutSystems, so this user will most likely not have these privileges on foreign entities. This user is used by the Deployment Controller and Deployment services only.
HubRuntime has only db_datareader and db_datawriter roles over the managed data model on the OutSystems database. This user is used by all OutSystems applications to access that data in the managed data model through the queries built by the developers, and by all OutSystems Services for control and management purposes.
HubLog is used by the OutSystems Log Service, and the OutSystems Applications, and it has db_datareader, db_ddladmin roles, plus it belongs to the HubLogRole database role, which is created with the Configuration Tool during the Create/Upgrade Database step (button), and it includes:
- insert privileges to all OutSystems log table (OSLOG_*_*)
- execution privileges to logging related store procedures, that are also created by the Create/Upgrade Database step
Hubstate has db_datareader and db_datawrite roles over the ASPState session data model only. This user is used by all OutSystems Applications to get session variables from the centralized session data model.

For Oracle RDBMS:

HubAdmin has CREATE VIEW, CREATE TABLE, CREATE and ALTER SESSION, CREATE SEQUENCE, CREATE PROCEDURE and CREATE TRIGGER privileges over it's own schema. This user is used to create and alter the database managed data model (all OutSystems eSpace Entities and the OutSystems system Entities), and to select, insert, update and delete rows on the managed data model. Note that Foreign Entities (entities defined through extensions) are not managed by OutSystems, so this user will most likely not have these privileges on foreign entities. This user is used by the Deployment Controller and Deployment services only.
HubRuntime has CREATE SESSION privilege and select, insert, update and delete privileges over all outsystems data model on the HUBADMIN schema. This user is used by all OutSystems applications to access that data in the managed data model through the queries built by the developers, and by all OutSystems Services for control and management purposes.
HubLog is used by the OutSystems Log Service, and the OutSystems Applications, and it has CREATE SESSION, plus is has select, insert, update, and delete privileges over key outsystems datamodel on the HUBADMIN schema.
Hubstate has CREATE VIEW, CREATE TABLE, CREATE and ALTER SESSION, CREATE SEQUENCE, CREATE PROCEDURE and CREATE TRIGGER privileges over it's own schema. This user is used by all OutSystems Applications to get session variables from the centralized session data model.

Can these users have different names beyond the default ones?

The Agile Platform has a single location to configure the database connection, where the database server and instance/catalog/schema is configured, as well as the database users. This place is the Configuration Tool. So you can set whatever names you want in the Configuration Tool for the database users, as long as you keep it to valid username strings (alfanumeric strings). It's highly recommended to set different user names for each role, a some descriptive names as well. Examples: hubadmin_dev, hubruntime_dev and hublog_dev.

How are the database user's passwords stored?

The passwords are manually defined in the Configuration Tool Database and Session tabs, for each of the users. It can have the complexity value chosen by the person configuring the Platform Server. The passwords are saved along with the username, database server/instance/name, in a connection string encrypted using a 2-way encryption to which only OutSystems R&D has knowledge of. This encrypted hash is saved on the machine.config file of the .NET Framework on servers with Platform Server installed, and in the server.hsconf configuration file in the Agile Platform installation directory.

How does the Grant Permissions button work?

The Grant Permissions button is only available for the SQL Server RDBMS, mainly because it bootstraps the users directly from the Configuration Tool, where as with the Oracle RDBMS, this bootstrap is done via a provided PL/SQL. The behavior of this button follows the logic:

Check if the user exists, if not, creates it on the configured SQL Server instance, with the defined username and password.
Then, grants the necessary privileges over the configured SQL Server instance and catalog, according to the correspondent role (hubadmin, hubruntime or hublog).

How to change a database user password?

Independently of the RDBMS used, the Configuration Tool will not automatically change the database user's password, for security reasons. So, if one needs to change the database user's password, one must do it in two places: directly on the database, using the RDBMS user management tools, and then change it on the Configuration Tool, by setting the new password. When performing this operation, the Test Connection button will test the login, and allows you to check if the password is correctly configured. So if you change the user's password on the Configuration Tool only, and them press the Grant Permissions button, it won't change the database user's password on the RDBMS side, because the user already exists and it's not recreated with a new password.

Notes for the SQL Server RDBMS database Configuration

The SQL Server database users are created using the Configuration Tool Database tab for the hubadmin, hubruntime and hublog users with the correspondent Grant Permissions buttons, and the Session tab for the hubstate user with the Configure Session Database button.
The OutSystems catalog must be created manually in the SQL Server, and then the data model within will be created/upgrade by the Configuration Tool in the Database tab with the Create/Upgrade Database button.
The ASPState session data model is (re)created using the Session tab with the Configure Session Database button. Both the Grant Permissions and the Configure Session Database buttons require a Sysadmin SQL Server user with privileges to create the users, and to (re)create the session data model. This is only required on the Configuration Tool and this Sysadmin user is not saved anywhere, or used by the applications or services.

Notes for the Oracle RDBMS database Configuration

The Oracle instance is setup manually before getting to the Configuration Tool.
The Oracle database tablespaces and users for the main database and the session state database, must be created using provided PL/SQL scripts, prior to get to the Configuration Tool. The Configuration Tool will not automatically create database users.

Finally, please refer to the links below for additional documentation on the Agile Platform, which also holds much of the information compiled in this forum topic.

Hope this information is helpful in understand how the Agile Platform accesses the database.

Cheers

Miguel João

03 Feb 2010

Pedro Menezes

Hello,

Is this still valid for Outsystems 8.0? Specifically to change the users hubadmin, hubruntime, hublog and hubstate password in SQL Server we just go directly to database and change it and then change it on the Configuration Tool by setting the new password? Then "Test Connection button" followed maybe but not needed by, "Grant Permissions" button? This will make changes in all the Outsystems (applications, platform etc..?) ...I would like to know to make database migration more easy...this would guarantee the users database migration part...for the rest I will do backup and restore for the database... Thank you

24 Oct 2016

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines