Create secure views on snowflake

Question

I have a table AAA and I have created a secure view BBB on top of it with definition: create or replace secure view BBB as select * from AAA

I have granted select privilege on BBB to a role RRR.

when I perform select * from BBB using RRR it throws SQL compilation error: Failure during expansion of view 'BBB': SQL compilation error: Object 'AAA' does not exist or not authorized.

Now, when I grant select privilege on AAA to the role RRR,

the same query works fine. Is it how the secure view is suppose to work? Because in this case the table is still accessible and whatever restriction view imposes can be thrown away by accessing the table directly. It does not sound secure at all.

What am I missing here???

Answer 1

In addition to the SELECT privilege on the view, you'll need to grant USAGE on the database and schema containing the view:

grant usage on database <yourdb> to role RRR;
grant usage on schema <yourschema> to role RRR;

As you pointed out, secure views wouldn't make sense if you still had to grant access to the underlying table.

Answer 2

USE ROLE SECURITYADMIN;
--CREATE ROLE ADMIN_ROLE
CREATE ROLE IF NOT EXISTS ADMIN_ROLE;
GRANT USAGE ON WAREHOUSE ADMIN_WH TO ROLE ADMIN_ROLE;
GRANT ROLE ADMIN_ROLE TO ROLE SYSADMIN;

GRANT USAGE ON DATABASE EXISTING_DB TO ADMIN_ROLE;
GRANT USAGE ON SCHEMA EXISTING_DB.EXISTING_SCHEMA TO ADMIN_ROLE;
GRANT SELECT ON TABLE EXISTING_DB.EXISTING_SCHEMA.MYTABLE TO ADMIN_ROLE;

--CREATE DATA ROLES
CREATE ROLE IF NOT EXISTS USER_ROLE;
GRANT USAGE ON WAREHOUSE USER_WH TO ROLE USER_ROLE;

--USE NEW_DB DATABASE
USE DATABASE NEW_DB;
USE ROLE ADMIN_ROLE;
CREATE SCHEMA IF NOT EXISTS NEW_SCHEMA
GRANT USAGE ON DATABASE NEW_DB TO USER_ROLE;
GRANT USAGE ON SCHEMA NEW_DB.NEW_SCHEMA TO USER_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA NEW_DB.NEW_SCHEMA TO USER_ROLE;

CREATE OR REPLACE SECURE VIEW NEW_DB.NEW_SCHEMA.MY_SECURE_VIEW AS SELECT * FROM EXISTING_DB.EXISTING_SCHEMA.MYTABLE

USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE EXISTING_DB TO USER_ROLE;
GRANT USAGE ON SCHEMA EXISTING_DB.EXISTING_SCHEMA TO USER_ROLE;
GRANT SELECT ON TABLE EXISTING_DB.EXISTING_SCHEMA.MYTABLE TO USER_ROLE;

REVOKE USAGE ON DATABASE EXISTING_DB FROM ADMIN_ROLE;
REVOKE USAGE ON SCHEMA EXISTING_DB.EXISTING_SCHEMA FROM ADMIN_ROLE;
REVOKE SELECT ON TABLE EXISTING_DB.EXISTING_SCHEMA.MYTABLE FROM ADMIN_ROLE;

CREATE ROLE IF NOT EXISTS TEAM_ROLE;
GRANT ROLE USER_ROLE TO TEAM_ROLE;

USE ROLE TEAM_ROLE;
SELECT * FROM NEW_DB.NEW_SCHEMA.MY_SECURE_VIEW

my use case is very close to this snippet

if I remove

GRANT SELECT ON TABLE EXISTING_DB.EXISTING_SCHEMA.MYTABLE TO ADMIN_ROLE;

I am unable to create the view if I remove

GRANT SELECT ON TABLE EXISTING_DB.EXISTING_SCHEMA.MYTABLE TO USER_ROLE;

then

SELECT * FROM NEW_DB.NEW_SCHEMA.MY_SECURE_VIEW

fails with the exception

SQL compilation error: Failure during expansion of view 'NEW_DB.NEW_SCHEMA.MY_SECURE_VIEW': SQL compilation error: Object 'EXISTING_DB.EXISTING_SCHEMA.MYTABLE' does not exist or not authorized.

Let me know if the approach has got some basic flaws..