Is there a way to set a default schema for each user in MySQL and if so how? Where user x would default to schema y and user z would default to schema a.
Every MySQL is shipped with default system schemas/databases. Those are: mysql - is the system database that contains tables that store information required by the MySQL server. information_schema - provides access to database metadata.
Open the MySQL Workbench. Click the Create Schema option. Provide a schema name. Click apply to create the MySQL scheme.
In the Database Administration view upper pane, select the database. Right-click the database and select Set default schema. The Set default schema dialog appears. Select the schema that you want to make the new default schema for the database from the drop-down list and click Ok to save the changes.
The default schema for a user is solely used for object-reference in case the user omits the schema when querying objects. When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema.
There is no default database for user. There is default database for current session.
You can get it using DATABASE() function -
SELECT DATABASE();
And you can set it using USE statement -
USE database1;
You should set it manually - USE db_name
, or in the connection string.
If your user has a local folder e.g. Linux, in your users home folder you could create a .my.cnf file and provide the credentials to access the server there. for example:-
[client] host=localhost user=yourusername password=yourpassword or exclude to force entry database=mygotodb
Mysql would then open this file for each user account read the credentials and open the selected database.
Not sure on Windows, I upgraded from Windows because I needed the whole house not just the windows (aka Linux) a while back.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With