We’re making our first steps in creating reports with Power BI. I’ve gone through the documentation, for example:
I’m aware that permissions are provided at the database level, not within BigFix (see link to a post below)
Our plan is to create reports in Power BI and publish them to Power BI Online, we already have a gateway and are using this setup with other systems.
Please correct us if we’re understanding these things wrong.
My question is, what kind of permissions are we expected to provide in order to provide read access to the relevant tables?
We already have a service account in Active Directory created and have provided that user “Read” permissions to the database, a fairly standard thing to do. We expected that to be enough but it only provides access to some of the tables due to security policies that BigFix applies to them, according to one of our DB admins. Tables like “datasource_fixlets”, datasource_groups" or “datasource_devices” which are crucial are not readable.
Now we shouldn’t have to talk about providing every user SA access to the DB that needs to work on Power BI reports, including every service account that will refresh reports automatically. That seems like dropping a live grenade onto the “Least required” rule when it comes to granting permissions and is absolutely not how we work.
Do we remove those security policies that BigFix applies? Will that break some other functionality? Or are we expected to provide more access than is generally sensible to?
He adviced me to look into the system itself for a way to grant permissions since these policies are applied but the following post mentions that it’s done directly at the DB level:
Thank you for any information or advice you can provide.
Thank you for assisting. From looking at the permissions listed in the KB article it seems to reference the required permissions for the DB user that creates the Insights database. For example, first mentioned permission, “CREATE DATABASE”, should not be something needed just to read information from it.
We’re only looking for a way to read the data. I’m no expert on database permissions though but from what I’ve seen, that permissions list does not seem right.
You are correct in that authorization / permissions are provided via the DB level.
Your DBA’s are correct in that some tables are locked down Via RLS to the datasource…
“datasource_fixlets”, datasource_groups" or “datasource_devices are some of the tables that have RLS Row Level Security applied.
(You can see this under the security policies listed in the DB in SQL Management Studio)
There is a bypass to exclude the user out of RLS…
The user basically needs to satisfy one of these conditions and they are not going to be restricted by the RLS policy.
or is_rolemember(‘db_owner’) = 1
or user_name() = 'dbo’
or is_member(‘sec_rls_excluded’) = 1
English translation
f the user is a Role member of the DBOwner Role
If the user is named DBO
or if the user is a member of a group or role by the name sec_rls_excluded
You would need to create this role/group (by this name sec_rls_excluded) and add the user to it. From there you need to also assure the user has readership to the tables…
Thats all the typical pieces…
You could do something like this to grant select to a table for the user…
GRANT SELECT ON [schema].[tableName] TO [user];
Or you could do something like this to grant select to all tables in the DB…
GRANT SELECT ON DATABASE::[databaseName] TO [user];
