Currently, the WSO2 Integration Cloud supports adding multiple database users for a same database, but does not support changing user privileges.
Let's say someone has a requirement of using same database via two different user, one user has full access, where other user should have READ_ONLY access. How we do this in Integration Cloud?
We are planning to add this as feature to change the user permissions, but until that you can do it as I have mentioned below.
Steps:
1. Login Create a database with a user
2. Once you create a database you can see it as below, and you can add another user when clicking on the All users icon
3. There you can create new user or you can attach existing user to the same database
I added two users u_mb_2NNq0tjT and test_2NNq0tjT to the database wso2mb_esbtenant1
My requirement is to give full access to the u_mb_2NNq0tjT user and remove INSERT permission from test_2NNq0tjT user.
4. Login to the mysql.storage.cloud.wso2.com via mysql client as user u_mb_2NNq0tjT and revoke the INSERT permission of test_2NNq0tjT
first login as test_2NNq0tjT and check grants
mysql -u test_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
show grants
+-----------------------------------------------------------------------------------------+
| Grants for test_2NNq0tjT@% |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_2NNq0tjT'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `wso2mb_esbtenant1`.* TO 'test_2NNq0tjT'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------+
login as u_mb_2NNq0tjT and revoke the insert permission
mysql -u u_mb_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
REVOKE INSERT ON wso2mb_esbtenant1.* FROM 'test_2NNq0tjT'@'%';
login again as test_2NNq0tjT and check grants
mysql -u test_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
show grants
+-----------------------------
| Grants for test_2NNq0tjT@%
+-----------------------------
| GRANT USAGE ON *.* TO 'test_2NNq0tjT'@'%' IDENTIFIED BY PASSWORD <secret>
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wso2mb_esbtenant1`.* TO 'test_2NNq0tjT'@'%' WITH GRANT OPTION |
+-----------------------------
2 rows in set (0.24 sec)
With this approach we can change the permissions of another user who is attached to the same database.
To make an read-only user you need to revoke the permissions as follows
REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on `wso2mb_esbtenant1`.* from 'test_2NNq0tjT'@'%';
Please note: after you change the user privileges, do not detach/attach the test_2NNq0tjT user to the same or different database. Then it will set the all privileges automatically.
Let's say someone has a requirement of using same database via two different user, one user has full access, where other user should have READ_ONLY access. How we do this in Integration Cloud?
We are planning to add this as feature to change the user permissions, but until that you can do it as I have mentioned below.
Steps:
1. Login Create a database with a user
2. Once you create a database you can see it as below, and you can add another user when clicking on the All users icon
3. There you can create new user or you can attach existing user to the same database
I added two users u_mb_2NNq0tjT and test_2NNq0tjT to the database wso2mb_esbtenant1
My requirement is to give full access to the u_mb_2NNq0tjT user and remove INSERT permission from test_2NNq0tjT user.
4. Login to the mysql.storage.cloud.wso2.com via mysql client as user u_mb_2NNq0tjT and revoke the INSERT permission of test_2NNq0tjT
first login as test_2NNq0tjT and check grants
mysql -u test_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
show grants
+-----------------------------------------------------------------------------------------+
| Grants for test_2NNq0tjT@% |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_2NNq0tjT'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `wso2mb_esbtenant1`.* TO 'test_2NNq0tjT'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------+
login as u_mb_2NNq0tjT and revoke the insert permission
mysql -u u_mb_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
REVOKE INSERT ON wso2mb_esbtenant1.* FROM 'test_2NNq0tjT'@'%';
login again as test_2NNq0tjT and check grants
mysql -u test_2NNq0tjT -pXXXXX -h mysql.storage.cloud.wso2.com
show grants
+-----------------------------
| Grants for test_2NNq0tjT@%
+-----------------------------
| GRANT USAGE ON *.* TO 'test_2NNq0tjT'@'%' IDENTIFIED BY PASSWORD <secret>
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wso2mb_esbtenant1`.* TO 'test_2NNq0tjT'@'%' WITH GRANT OPTION |
+-----------------------------
2 rows in set (0.24 sec)
With this approach we can change the permissions of another user who is attached to the same database.
To make an read-only user you need to revoke the permissions as follows
REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on `wso2mb_esbtenant1`.* from 'test_2NNq0tjT'@'%';
Please note: after you change the user privileges, do not detach/attach the test_2NNq0tjT user to the same or different database. Then it will set the all privileges automatically.
Comments