Warehouse SQL Granular Security: 10 practice questions

Question 1 of 10

An engineer wants to give a new colleague `SELECT` on one warehouse table. The colleague has no database user in the warehouse yet. What should the engineer run?

1. AA `CREATE USER` statement, and then the `GRANT` statement
2. BA `CREATE USER` statement, which also grants `SELECT`
3. CNothing in SQL, because database users cannot get table permissions
4. DThe `GRANT` statement, and Fabric creates the database user

Question 2 of 10

A Power BI semantic model reads a warehouse in Direct Lake mode. Column-level security is then defined with T-SQL on one of its tables. What happens to the Power BI queries on that table?

1. AThey switch to Import mode and refresh the table
2. BThey fall back to Direct Query mode
3. CThey fail until the model uses Import mode
4. DThey stay in Direct Lake mode and skip the restriction

Question 3 of 10

A `Customers` table has the columns CustomerID, FirstName, LastName, and CreditCard. An admin runs `GRANT SELECT ON Customers(CustomerID, FirstName, LastName) TO [Charlie@contoso.com];`. Charlie has no other permission on the table and runs `SELECT * FROM Customers;`. What is the result?

1. AThe three granted columns, and no CreditCard column
2. BAn error that `SELECT` was denied on the CreditCard column
3. CAll four columns, with CreditCard returned as NULL
4. DAll four columns, with CreditCard displayed as a masked value

Question 4 of 10

A schema-bound function `Security.tvf_securitypredicate` returns a result when its argument equals `USER_NAME()`. Which statement makes it filter the rows of `sales.Orders` by the SalesRep column?

1. A`ALTER TABLE sales.Orders ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep) WITH (STATE = ON);`
2. B`CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep) ON sales.Orders WITH (STATE = OFF);`
3. C`GRANT SELECT ON sales.Orders(SalesRep) TO Security.tvf_securitypredicate WITH (STATE = ON);`
4. D`CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep) ON sales.Orders WITH (STATE = ON);`

Question 5 of 10

An enabled policy on `sales.Orders` uses a predicate with the condition `@SalesRep = USER_NAME()`, where `@SalesRep` receives the SalesRep column. A workspace Admin whose name is in no SalesRep value queries the table. What does the Admin get?

1. AAll rows, but with the SalesRep column hidden
2. BAn error that the policy denies access
3. CAn empty result, with no error
4. DAll rows, because an Admin is the dbo user

Question 6 of 10

The predicate function of an enabled security policy needs a new condition. `ALTER FUNCTION` on it returns an error. Which sequence makes the change?

1. ACreate a second policy on the table that uses a new function
2. BDrop the policy, alter the function, recreate the policy
3. CDrop the function, create it again, and keep the policy as it is
4. DDisable the policy, alter the function, enable the policy again

Question 7 of 10

Analysts query a lakehouse table through the SQL analytics endpoint, and data scientists read the same table in Spark notebooks. Both groups are workspace Viewers and should see the rows of their own country and no others. The rule should be defined once. Which design meets the requirements?

1. AA row rule in a OneLake security role, with the endpoint in delegated identity mode
2. BA SQL security policy on the endpoint, with the endpoint in user identity mode
3. CA row rule in a OneLake security role, with the endpoint in user identity mode
4. DA SQL security policy on the endpoint, with the endpoint in delegated identity mode

Question 8 of 10

In a warehouse, the SQL role `SalesReaders` has `SELECT` on `sales.Orders` and on `sales.Customers`. One member, Kim, must stop reading `sales.Orders` but must keep reading `sales.Customers`. The other members must keep both tables. Which statement does this?

1. A`REVOKE SELECT ON sales.Orders FROM SalesReaders;`
2. B`DENY SELECT ON sales.Orders TO [kim@contoso.com];`
3. C`REVOKE SELECT ON sales.Orders FROM [kim@contoso.com];`
4. D`ALTER ROLE SalesReaders DROP MEMBER [kim@contoso.com];`

Question 9 of 10

A warehouse is shared with Lee with no additional permissions. Some time ago an admin ran `DENY SELECT ON SCHEMA::hr TO [lee@contoso.com];`. Lee now needs to read `hr.Holidays` and no other table of the `hr` schema. The admin runs `GRANT SELECT ON hr.Holidays TO [lee@contoso.com];`, but Lee still cannot read the table. What should the admin do?

1. ARevoke the `DENY` on the schema, and keep the `GRANT` on the table
2. BAdd Lee to a SQL role that has `SELECT` on the `hr.Holidays` table
3. CRun `GRANT SELECT` on the `hr` schema for Lee, which removes the `DENY`
4. DRun the `GRANT` on the table again, adding `WITH GRANT OPTION`

Question 10 of 10

A warehouse has the SQL role `SalesReaders`, which has `SELECT` on `sales.Orders`. Amy already has a database user in the warehouse. Which statement lets Amy read the table through the role?

1. A`CREATE ROLE SalesReaders FOR [amy@contoso.com];`
2. B`GRANT SalesReaders ON sales.Orders TO [amy@contoso.com];`
3. C`ALTER ROLE SalesReaders ADD MEMBER [amy@contoso.com];`
4. D`ALTER ROLE SalesReaders WITH NAME = [amy@contoso.com];`