Database Users And Permission Manager

Database Access is available for sites on USD 50 and above plans

Available Permissions

There are three types of permissions that can be assigned to database users:

1. Read Only: The user can view data but cannot modify it. They have access to all tables and columns in the database for reading purposes only.

2. Read Write: The user can both view and modify data. They have full access to all tables and columns in the database for reading and writing.

3. Granular: This permission mode allows for fine-grained control over a user's access. You can grant access to specific tables and, if needed, even restrict access to specific columns within those tables.

Manage Database Users

Go to Site Dashboard > Actions > Manage database users

From here, you can view all the existing users of your site's database and manage their permissions and access settings.

Create Database User

Click on Add User button to open the modal for creating database users.

1. Read Only Mode :

   
   

   
   

2. Read Write Mode :

   
   

   
   

3. Granular Mode :

   When you begin configuring Granular Access, the system will load your database's table schema. This process may take anywhere from 30 seconds to 5 minutes, depending on the number of tables in your database.

   You can set the Read Only or Read Write mode for each table individually. Additionally, if needed, you can restrict access to specific columns within a table by selecting the columns you want to grant access to.

   
   

   
   

   
   

Edit Database User

If you want to modify the permissions for a specific database user, you can easily do so by selecting the Configure User option in the row menu. This will allow you to adjust the user’s access levels and permissions as needed.

You can make the changes to the user's permissions and save them. Once saved, it may take some time for the updates to take effect.

Please note that if the database user previously had Read Only or Read Write global privileges, any changes you make to their permissions will not take effect immediately. These changes will only apply to new connections created after the update.

View Database Credential

Click on the three dots in the row and select View Credential to view the details for that specific user’s credentials.

After selecting View Credential, a modal will appear displaying all the necessary information to connect to your site database.

Connect to the Database

Analytics or Business Intelligence tool

Copy the credentials shown to your analytics tool.

We only allow TLS connections. If you're having issues while connecting then please ensure that the analytics tool is using SSL/TLS and our certificate chain.pem for *.frappe.cloud (Signed by Let's Encrypt).

MariaDB command line client

Copy the command to your console and paste the password when prompted.

You will need to install mariadb-client or mysql-client on your machine.

Delete Database User

Click on the three dots in the row and select Delete User to remove the database user.

When you click to delete a user, a confirmation modal will appear. Once you confirm the deletion, the selected database user will be deleted.

FAQ

Cannot connect to database from PowerBI

If connecting with PowerBI doesn't work, you might be using MySQL Connector. We use MariaDB for our databases. Please use Mariadb connector with PowerBI for the same. You can find docs for the same here.

Error when running mysql query

Sometimes you may get errors such as

lost connection to server during query

In such cases, the application with which you're connecting to the database may not be showing the exact error mysql server is throwing. If you run into any errors when running queries, it's best to try the same query over mysql client in the command line.

With the error thrown there, you'll be able to tell what exactly went wrong, and if anything is needed from our end, you can take a screenshot of the same and raise support ticket with it.

Timezone doesn't match the timezone of the site

The system-time-zone mariadb variable will not be set to the timezone you see under System Settings. This is simply because that is not how timezone is implemented in Frappe. This is not an issue.

Connecting with Looker Studio

You need to enable Use SSL to connect to the database. The certificate you can obtain by vising the host in your browser. Eg: https://n2-mumbai.frappe.cloud
You can follow this stack overflow answer for the same.

If you're prompted to copy/paste certificates, you can open the file you downloaded in any text editor (eg: notepad). It will be in the format as shown

------BEGIN CERTIFICATE ------
<LONG KEY>
------END CERTIFICATE ------

and copy/paste in the fields provided.

If it asks for a Client Certificate and Client SSH key, you can leave them blank. It is usually only needed for purposes like bank integration, in which case, you can raise a support ticket.