Hi,
I wanted to use native queries so allowed sql queries on permission tab in Admin menu.
There are tables should not been seen from the group, so I disallowed table level permission as below.
and then I found the sql queries permission for the group set disallow again.
How can I control table level permission with allow for sql queries.
Please help with any idea.
Thanks.
Hi @daniel.lee
Metabase currently cannot parse SQL, so there's no way of enforcing the permissions defined in SQL.
You would have to create another database connection with limited privileges.
While we are working on ways to implement this, it will only be available in the Enterprise Edition:
https://github.com/metabase/metabase/issues/10525
Thanks, @flamber
According to your explanation, as of now If I want to use native queries to make question in metabase, then the group always has all access for all tables in the database as well.
And then, as I understand it is not possible scenario that a user can make sql queries with "A" table but cannot with "B" table.
If I make another database connection, Is it possible with permission setting in metabase?
@daniel.lee You have to define privileges on the database user, which you then use for setting a database in Metabase, so Metabase has no way of accessing anything not defined in the database user.
In other word. Your database is the server, Metabase is just a client. Restrict the privileges on the database.
Do you mean once I restrict user's permission for specific table on the database which is connecting metabase, then the user also cannot see the table and cannot make native queries with the table in metabase?
@daniel.lee Correct. That's how privileges works on databases. If you restrict/grant access to specific tables, then whatever client you're using (in this case Metabase) will only be able to see what has been granted.
The thing makes me confuse is I already removed user's permission from table on the database, and I checked the user cannot read the table from the database, but the user still can read the table in metabase.
FYI, what I'm testing is to know if a table is not in "our data" for restriction of access, still possible to be used in native queries in metabase.
@daniel.lee Which database are you querying? If MySQL, then you need to flush the privileges for them to take effect.
Some databases only apply privileges to new connection, but Metabase (like many other clients) keep connections open, so you will need to restart the client or kill the connections on the database.
This has nothing to do with Metabase, so check the manual of your database.
I didn't set the workaround first, so It is not clear but I guess it is based on MySQL according to diagnostic info.
(Please understand our organization is divided so complicated.)
If so, I need help from DBA to flush the database privileges.
{
"browser-info": {
"language": "ko-KR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_242-b08",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_242",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.242-b08",
"os.name": "Linux",
"os.version": "3.10.0-957.21.3.el7.x86_64",
"user.language": "ko",
"user.timezone": "Asia/Seoul"
},
"metabase-info": {
"databases": [
"presto"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.31-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.36.6",
"date": "2020-09-17",
"branch": "master",
"hash": "e81939a"
},
"settings": {
"report-timezone": "Asia/Seoul"
}
}
}@daniel.lee I think there are some misunderstandings here, so I would recommend that you consult your DBA.
You set privileges on your database (that's Presto in your case). Then the client (that's Metabase in your case) can only access what has been granted.
It has absolutely nothing to do with settings in Metabase (or the Metabase application database, which is MySQL in your case).
By the way, you're using an old version of Metabase. Latest release is 0.40.2: https://github.com/metabase/metabase/releases/latest
Ok, I think I should do this from set privileges on Presto again.
By the way, is it possible in metabase?
@daniel.lee I don't understand, but if you allow SQL access for users, then the only restrictions that apply are whatever the privileges are on the database.
In other words. You cannot use the Metabase > Admin > Permissions to restrict which tables a user can query in SQL.
https://www.metabase.com/learn/permissions/data-permissions#data-permissions-cheat-sheet
Hope that's clear enough.
Thanks for your kind help, I understand If I allow SQL access for any group in metabase, restrict permission from database is only way to block to access some table in metabase.
The link you posted also gave me nice help.
