How to perform database O&M by using Bastionhost - Bastionhost

Databases are critical assets, and securing their O&M is a top priority. Bastionhost Enterprise Edition and SM Edition support O&M and audits for ApsaraDB for RDS (MySQL, SQL Server, and PostgreSQL), PolarDB (MySQL, PostgreSQL, and PostgreSQL with Oracle compatibility), and self-managed databases (MySQL, SQL Server, PostgreSQL, and Oracle). This topic uses specific examples to demonstrate how to use Bastionhost for database O&M.

Background

In addition to Windows and Linux servers, enterprises manage a large number of database assets. As core business assets, databases contain sensitive information. Securing database O&M and preventing unauthorized access or non-compliant activities are major concerns for any enterprise.

Database environments are diverse. They include cloud-native databases such as ApsaraDB for RDS, self-managed databases, and various database engines such as MySQL, SQL Server, PostgreSQL, and Oracle. In large enterprises, these databases are often distributed across complex environments that span multiple accounts, VPCs, on-premises IDCs, or heterogeneous clouds. Bastionhost centralizes O&M and control for these hybrid scenarios, which simplifies management for security teams.

Alibaba Cloud Bastionhost Enterprise Edition and SM Edition support centralized management of Windows servers, Linux servers, and databases. They let you define granular permissions and perform post-event audits for various database types, maximizing the security of your database O&M. Bastionhost also provides unified access to assets in hybrid environments, such as those that involve multi-account setups, on-premises IDCs, and heterogeneous clouds, allowing security teams to manage all resources from a single platform.

Bastionhost Enterprise Edition and SM Edition use a reliable dual-engine, active-active architecture. This design ensures high availability and stability for demanding database O&M. For more information, see Comparison of features among different editions of Bastionhost.

O&M workflow

When you use Bastionhost for database O&M, an administrator first manages assets and grants permissions to O&M engineers. The O&M engineers then establish an SSH tunnel to Bastionhost by using a client or the O&M portal. Finally, they log on to the database asset through Bastionhost to perform O&M.

Methods and procedures

Client O&M

Before you begin

You have created a database and a user in Bastionhost, and authorized the user to access the asset and its asset account. For more information, see Create a database, Manage users, and Authorize assets and asset accounts.

The following examples use a MySQL database and a local user.
You have installed a database client that supports SSH tunnels on your local machine. For recommendations on client tools and versions, see Recommended client tools and versions for remote connections.

This guide uses Navicat Premium on Windows and the command line on Linux as examples.

Step 1: Obtain O&M address and token

Log on to the O&M portal. For more information, see Log on to the O&M portal.
In the navigation pane on the left, click Databases.
On the Databases page, find the target database. In the Remote Connection column, set View O&M Token to Logon Method and click Log On.
In the O&M Token dialog box, click Apply for O&M Token. You need to apply for a token only on your first logon.

After a token is granted, the dialog box displays two sections: Client Database General Information (including the database address, account name, and port) and Client SSH Tunnel Configuration (including the O&M token, public and private O&M addresses, Bastionhost O&M ports such as SSH: 60022 and RDP: 63389, and token expiration time). Copy the O&M token and paste it into the password field of your client. To extend the validity period, click Update Token or Renew Token.

Note

When you obtain an O&M token from the O&M portal, if the current database account is not managed by Bastionhost, you must first configure the basic information of the database account in the O&M Token dialog box. For more information about how to create a database account, see Database management.
O&M tokens must be used within their validity periods. An administrator can set the validity period in the Bastionhost console. If O&M approval is enabled, the validity period that is set by the administrator during the approval process is used.
If an administrator allows O&M users to renew their own tokens, the users can renew the tokens before they expire. After a token expires, the user must request a new one. If O&M approval is enabled, users cannot renew tokens. After token settings are changed, you must request a new token or update the existing one for the new settings to take effect.
If a token is valid but the O&M connection fails, the number of concurrent O&M connections may have reached the upper limit. In this case, contact an administrator to upgrade the instance type of your Bastionhost instance or release idle connections. Another possible reason is that an administrator has blocked O&M requests from your source IP address or during the current time period. In this case, contact the administrator to remove the restrictions.
The audit logs record the user who requested the token, not the username or asset account that is entered on the client.

Step 2: Establish an SSH tunnel

The following sections describe how to perform database O&M by using the Navicat Premium tool and a command-line terminal.

Navicat Premium

Open Navicat Premium and create a new MySQL connection.

On the SSH tab, select SSH tunnel, and configure the parameters to log on to the bastion host (the client SSH tunnel configuration parameters obtained in Step 1).

Parameter	Description
Host	The public or private O&M address of the Bastionhost instance, from the Parameters Required for Client-based O&M over SSH Tunnel section in Step 1.
Port	The SSH O&M port of Bastionhost. The default is 60022.
User Name	The username you use to log on to Bastionhost.
Password	The database O&M token. Use the O&M token from the Parameters Required for Client-based O&M over SSH Tunnel section in Step 1.

On the General tab, configure the database connection information.

Set the Port to 3306 and specify a custom Connection Name, such as mysqltest.

Parameter	Description
Host	The database connection address.
User Name	The logon username for the database account.
Password	If the administrator has hosted the database account password in Bastionhost, you can leave the password field empty. If the administrator has not hosted the database account password, enter the logon password for the database account. Note We recommend that you save the password. If you do not save the password, the database O&M tool may require you to enter a password. In this case, you can enter the O&M token.

In Navicat Premium, double-click the newly created database connection. After the connection is successful, you can begin managing the database.

Command line

Open a command-line tool and run the following command:

Command format:

ssh -N -L <localport>:<databaseAddress>:<databasePort> <bastionusername>@<bastionAddress> -p <bastionPort>

Parameters:

Parameter	Description
localport	A custom local port for the tunnel. Make sure it does not conflict with any existing listening ports on your machine.
databaseAddress	The address of the database asset.
databasePort	The port of the database asset.
bastionusername	Your Bastionhost username.
bastionAddress	The O&M address of Bastionhost.
bastionPort	The SSH O&M port of Bastionhost. The default is 60022.

Example: ssh -N -L 33061:rm-******m020h.mysql.rds.aliyuncs.com:3306 bastuser@******-public.bastionhost.aliyuncs.com -p 60022

When prompted for a password, enter the database O&M token and press Enter. The terminal will wait for the connection.

[root@xxx ~]# ssh -N -L 33061:rm-xxx0h.mysql.rds.aliyuncs.com:3306 doc01@uxxx-public.bastionhost.aliyuncs.com -p 60022
The authenticity of host '[uxxx-public.bastionhost.aliyuncs.com]:60022 ([xxx.7.15]:60022)' can't be established.
RSA key fingerprint is SHA256:xxx.
RSA key fingerprint is MD5:9x:xx:xx:xx:b0:56:0b:bb:58:e6:46:ec.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[uxxx.bastionhost.aliyuncs.com]:60022,[xxx.15]:60022' (RSA) to the list of known hosts.
doc01@uxxx-public.bastionhost.aliyuncs.com's password:

Open another terminal and confirm that the custom local port is listening. The output LISTEN indicates that the port is listening.

[root@iZtxxx:jwtg98zqZ ~]# ss -an | grep 33061
tcp    LISTEN   0    128      127.0.0.1:33061          *:*
tcp    LISTEN   0    128      [::1]:33061              [::]:*

Run the following command. After a successful connection, you can now perform O&M.

[root@iZ_xxx jwtg98zqZ lib]# mysql -h 127.0.0.1  -u roxxx -P 33061
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3151064
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>

Command format

mysql -h 127.0.0.1 -u <accountname> -P <localport>

Parameters
- accountname is the logon username of the database.
- localport is the custom local listening port you specified when connecting to Bastionhost.

Web O&M

Bastionhost allows you to perform database O&M through the O&M portal or web console. The following procedure uses the O&M portal as an example.

Before you begin

You have created a database and a user in Bastionhost, and authorized the user to access the asset and its asset account. For more information, see Create a database, Manage users, and Authorize assets and asset accounts.

The following example uses a MySQL database and a local user.

Procedure

Log on to the Bastionhost O&M portal. For more information, see Log on to the O&M portal.
In the left-side navigation pane, click Databases.
Find the target database. In the Remote Connection column, select Web Remote Connection as the logon method and click Log On.

In the logon dialog box that appears, select root for Database Account. You can also select the Set as default method checkbox.

SSO O&M

O&M engineers can use the single sign-on launcher from a web page to automatically invoke a local client and establish a session. This eliminates the need to configure the client separately. This guide uses the Bastionhost O&M portal to demonstrate how to use SSO for MySQL database O&M.

Before you begin

You have created a database and a user in Bastionhost, and authorized the user to access the asset and its asset account. For more information, see Create a database, Manage users, and Authorize assets and asset accounts.

The following examples use a MySQL database and a local user.
You have installed a client that is supported by the single sign-on launcher on your local machine. For a list of supported clients, see Clients supported by the single sign-on launcher.

The following procedure uses DBeaver 23 on Windows as an example.

Step 1: Obtain portal address

Log on to the Bastionhost console. In the top navigation bar, select the region where your Bastionhost instance is located.
In the list of Bastionhost instances, find the target instance and click Manage.
In the left-side navigation pane, click Overview to obtain the O&M portal address.

In the Bastionhost Instance Information panel on the right, you can find the Public O&M Portal Address and Private O&M Portal Address.

Step 2: Install the single sign-on launcher

Enter the O&M portal address in your browser.
On the Alibaba Cloud Bastionhost logon page, enter your Bastionhost username and password, and then click Log On.
On the O&M portal page, click Download Bastionhost Assistant.

This example uses the single sign-on launcher for Windows.
After the download is complete, install the launcher. You do not need to open it after installation.

Step 3: Configure O&M terminal

In the left-side navigation pane of the O&M portal, click Device Settings.
On the MySQL tab, select the DBeaver client and click Save.
In the usmsso.exe dialog box that appears, click Open and follow the on-screen instructions to configure the database client.

Step 4: Connect to the database

In the left-side navigation pane of the O&M portal, click Databases.
Find the target database. In the Remote Connection column, select Local Client Logon from the drop-down list and click Log On.

Audit database O&M

After an O&M engineer accesses a database asset through Bastionhost, all O&M activities are audited. To enable post-event traceability, Bastionhost provides features such as session audit, real-time monitoring, and operation logs.

Log on to the Bastionhost console. In the top navigation bar, select the region where your Bastionhost instance is located.
In the list of Bastionhost instances, find the target instance and click Manage.
In the navigation pane on the left, choose O&M Audit > Session Audit.
On the Session Audit page, view the session records.