I am new to Virtualmin and hosting control panel. I just set up a virtual machine installed Virtualmin, I created new virtual server with “Can manage database” setting.
I login to Web-GUI with credential for the virtual machine. At “Edit database” tab, I can create or delete the databases in the database list.
But when I ssh to the host (with credential created when creating new virtual server), I can login and use the command “mysql -uexampleUser -p examplePassword”. After that, I can show all database by “SHOW DATABASES”, but when I created a new database like “CREATE DATABASE test;” i got
ERROR 1044 (42000): Access denied for user ‘exampleUser’@‘localhost’ to database ‘test’
I don’t know why I can create new database by Web-GUI but not on CLI. Can you help me?. Thank so much.
Virtualmin needs to know about newly created database and how to connect to it.
There is a CLI command to create virtual server’s database properly:
$ virtualmin help create-database
create-database.pl
Creates a database for a virtual server
This command creates a new MySQL or PostgreSQL database, and associates it with an existing virtual server. You must supply the "--domain"
parameter to specify the server, "--name" to set the database name, and "--type" followed by either "mysql", "postgres" or some plugin database
type. It would typically be run something like :
create-database.pl --domain foo.com --name foo_phpbb --type mysql
Some database types support additional creation-time options, specified using the "--opt" flag. At the time of writing, those available for MySQL
are :
"--opt charset name" - Sets the character set (like latin2 or euc-jp) for the new database.
And for PostgreSQL, the options are :
"--opt encoding name" - Sets the text encoding (like LATIN2 or EUC_JP) for the new database.
For short, I mean. Can I login to mysql server (after ssh to the machine that Virtualmin installed) by command “mysql -uexampleUser -p examplePassword”. Then, create new database by “CREATE DATABASE test;” ?
Yes, you certainly can! However, Virtualmin will not do anything with a new database. You may need to manually import it under given virtual server on Virtualmin/Edit Databases page, under its Import Database tab – this is why it’s recommended to use virtualmin create-database CLI command to create a new database from command line.
At the same time, Webmin/Servers/MySQL Database Server will be able to display your database right away.
If you meant to create a database by regular user – it would strictly depend on given user’s permissions; this question is very commonly asked and not Virtualmin related.
This exampleUser created when I create new Virtual Server, for eg example.com. When I create this new virtual server, I choose the Custom Administrator username is “exampleUser”.
When check /etc/passwd list, this user appear. If I login this user to virtualmin Web-GUI, I can create new database but not after successfully connec to mysql by “mysql -uexampleUser -p examplePassword”.
Thanks, with you help. Now I can create new databases.
But I noted that, eg when assigning “create table” permission to user vsrv3. The user now can show and use any other’s database.
My goal is allow the user can create new database, but can’t show and use other’s database.
I tried to use database permission page. Eg I allow only user vsrv1 can use database vsrv1. But why user vsrv3 (with Create table permission) can access to database vsrv1?
You shouldnt set it to any unless its really intended to.
A subdomain user can access the DBs from the matching top level domain, from what I noticed.
But it shouldnt be able to access DBs from other domains.
I have had to many support requests. I missed your reply, sorry.
Yes, correct. This how it would work, if used as in an example above. If you want a specific user to only have permissions to specific databases you would need to go to Database Permissions page:
… on the opened permission page, you would need to either modify existing or create a new db permission.
What would be important is to place % at the end of database name, which will provide a given user to have full access to databases ending with debug_ubuntu18_pro* (in my case), including creating a new ones:
…now, when I run CREATE DATABASE debug_ubuntu18_pro_2; or CREATE DATABASE debug_ubuntu18_pro_new; it will work but not CREATE DATABASE debug_ubuntu18_2;
