Security: ACS user access to all databases

[tampler]

ISSUE TYPE

• Bug Report
• Improvement Request
• Enhancement Request

COMPONENT NAME

Core, MySQL

CLOUDSTACK VERSION

4.19.0.0-snapshot.20231113

CONFIGURATION

OS / ENVIRONMENT

Ubuntu 22.04

SUMMARY

ACS requires access to all MySQL databases, which creates a huge security hole for a shared MySQL instance.

Trying to limit the scope of ACS access yields in the access denied error (see below).

STEPS TO REPRODUCE

> `sudo cloudstack-setup-databases cloud:cloud@mysql --deploy-as=root`

/usr/share/cloudstack-management/setup/create-database.sql
lines: 64-65
GRANT process ON *.* TO cloud@`localhost`;
GRANT process ON *.* TO cloud@`%`;


// This works with the full access
GRANT ALL ON *.* TO 'root'@'hyp0' WITH GRANT OPTION 

// This yields in an error:
- GRANT ALL ON mysql.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON billing.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON cloud.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON cloud_usage.* TO 'root'@'hyp0' WITH GRANT OPTION

EXPECTED RESULTS

ACS configs own databases only without an error

ACTUAL RESULTS

ACS wants a full access and gives an error when a limited db access is provided

We apologize for below error:                                                         
table:                                                                                                                                                                       
/usr/share/cloudstack-management/setup/create-database.sql                                                                                                                   
                                                                                      
Error:                                                                                
b"ERROR 1045 (28000) at line 64: Access denied for user 'root'@'hyp0' (using password: NO)\n" 

[DaanHoogland]

@tampler , does it say to what 'root'@'hyp0' tries to get access?

in a simulator env:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cloud              |
| cloud_usage        |
| information_schema |
| mysql              |
| performance_schema |
| simulator          |
| sys                |
+--------------------+

I am not sure but I think root needs access to all to install cloud. Though I am not rreally sure about infomation_schema and performance_schema. Can you try to add at least mysql and sys?

[tampler]

Hi @DaanHoogland

This is my setup

My command: mysql -h mysql -u root -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cloud              |
| cloud_usage        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.03 sec)

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| cloud            | %         |
| cloud            | hyp0      |
| root             | hyp0      |
| cloud            | localhost |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)

[tampler]

@tampler , does it say to what 'root'@'hyp0' tries to get access?
It actually says that it wants a GRANT ALL ON *.* TO 'root'@'hyp0' WITH GRANT OPTION access for my MySQL instance instead of a more limited subset:

- GRANT ALL ON mysql.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON billing.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON cloud.* TO 'root'@'hyp0' WITH GRANT OPTION
- GRANT ALL ON cloud_usage.* TO 'root'@'hyp0' WITH GRANT OPTION

So I had to fallback to the *.* notation just to have setup done. However, this is a security hole and ACS shouldn't anyhow access databases beyond its own scope.

[DaanHoogland]

So I had to fallback to the *.* notation just to have setup done. However, this is a security hole and ACS shouldn't anyhow access databases beyond its own scope.

I think it should only need to allow from localhost. And user root is only used during setup so I don't think it is a big issue. Did you try to remove rmote access to root after setup, and did it work?

We can try to find the limits of what should work, but root for sure needs to do (part of) the setup.

[tampler]

Hi @DaanHoogland
Okay let's get deeper into this. I'll dig deeper after solving #8218

[kishankavala]

From mysql documentation:

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process

The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions).

The PROCESS privilege also enables use of the SHOW ENGINE statement, access to the INFORMATION_SCHEMA InnoDB tables (tables with names that begin with INNODB_), and access to the INFORMATION_SCHEMA table.

In ACS, access to information_schema is required during upgrades. There could be other use-cases also where information_schema access is required. PROCESS is an administrative privilege and cannot be granted to specific database. It is global.

Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

So, user cloud requires PROCESS privilege and it has to be *.* (global)

[weizhouapache]

To be clear,

• The script cloudstack-setup-databases use root user to create cloudstack database
• ACS uses cloud user to access and modify the databases cloud and cloud_usage

I do not think the privileges of cloud user is a problem, as @kishankavala mentioned, we could limit the PROCESS privilege to only few other databases (mysql and information_schema).

The root user should have higher privileges, for example, create databases, create procedures in mysql database. However, the script needs to be executed only once during the whole ACS setup (you do not need to execute it on extra management servers and database servers).

Do you mean the privileges of root user is a security issue ?

GRANT ALL ON *.* TO 'root'@'hyp0' WITH GRANT OPTION

I think the issue is because, the cloud and cloud_usage databases are dropped/recreated by the script, so the privileges do not work on the new cloud and cloud_usage databases.
we can add an option to not drop/recreate the cloud and cloud_usage databases if already exist.

[DaanHoogland]

@tampler is this issue answered to our satisfaction?