roles_database_roles_assignment.sql

select current_user



-- troubleshooting access to the PATIENT_CONTACT table

-- look at the permissions for role DB_ROLE_PROJECT1 ON the consumer side

SHOW GRANTS TO ROLE DB_ROLE_PROJECT1;

-- RUN THIS ON THE PRODUCER SIDE OF THE SHARE
-- TO SHOW THE DETAIL OF WHICH ROW ACCESS POLICIES ARE IN PLACE IN A DATABASE/TABLE
SHOW ROW ACCESS POLICIES
DESC ROW ACCESS POLICY PROJECT1_PHONE_ACCESS_POLICY


-- exploring permission in the account for roles

-- TEST USING A ROLE WHICH DOES NOT EXPLICITLY HAVE ACCESS TO THE SHARE/DATABASE ROLE ACROSS THE SHARE
USE ROLE db_role_project1;
select top 10 * from information_schema.applicable_roles

USE ROLE db_role_project1;
select top 10 * from information_schema.usage_privileges


SELECT COUNT(*) AS COUNTALL, USE_CONCEPT_ID FROM PATIENT_CONTACT
GROUP BY USE_CONCEPT_ID

SHOW GRANTS TO ROLE SNOWFLAKE_LEARNING_ROLE;

-- Check privileges granted to a specific role
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE NAME = 'SNOWFLAKE_LEARNING_ROLE';

-- Check privileges granted to a specific role
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE NAME != 'SNOWFLAKE_LEARNING_ROLE';
and GRANTED_TO = 'ROLE'
-- GRANTEE RECEIVES THE PRIVILEDGE
ORDER BY GRANTEE_NAME


-- Check privileges granted to a specific role
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE 
--
--NAME != 'SNOWFLAKE_LEARNING_ROLE'
--AND GRANTED_TO = 'ACCOUNTADMIN'
GRANTED_TO = 'ROLE'
AND 
GRANTEE_NAME = 'SNOWFLAKE_LEARNING_ROLE'
-- GRANTEE RECEIVES THE PRIVILEDGE
ORDER BY GRANTEE_NAME


-- Check privileges granted to a specific role
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE 
--
--NAME != 'SNOWFLAKE_LEARNING_ROLE'
--AND GRANTED_TO = 'ACCOUNTADMIN'
GRANTED_TO = 'ROLE'
AND 
GRANTEE_NAME = 'DB_ROLE_PROJECT1'
-- GRANTEE RECEIVES THE PRIVILEDGE
ORDER BY GRANTEE_NAME


  
  select top 10 * from 
  SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
  where role = 'SNOWFLAKE_LEARNING_ROLE'

  SELECT top 10 *
FROM SNOWFLAKE.ACCOUNT_USAGE.ROLES
WHERE name = 'SNOWFLAKE_LEARNING_ROLE';

SHOW ROLES;

SHOW GRANTS TO ROLE DB_ROLE_PROJECT1;

SHOW GRANTS TO ROLE SNOWFLAKE_LEARNING_ROLE;

SHOW DATABASE ROLES IN PRODUCER_SHARE_COMPASS


-- ***** EVERY NEW ROLE IN SNOWFLAKE AUTOMATICALLY INHERITS THE PERMISSIONS OF THE PUBLIC ROLE
-- ***** THIS COULD CAUSE PROBLEMS IF THE PUBLIC ROLE HAS HIGH ADMINISTRATIVE PRIVILEGES
-- ALSO THE PUBLIC ROLE BY DEFAULT CAN SEE SHARES    
-- SO WE WILL NEED TO RUN A SCRIPT WHICH RESTRICTS THE PUBLIC ROLE 

-- Check privileges granted to  the public role 
-- we can that the databases 'snowflake' and 'snowflake_learning_db'
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE NAME = 'PUBLIC';


-- checking the permissions on the default free account snowflake sample share (for free sample data in snowflake on signing up)
-- does user public have permissions to that share

-- it is generally a good idea to restrict what the PUBLIC role can access in Snowflake—especially
 -- in environments where security, data governance, and least privilege principles are important


SELECT TOP 10 *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE GRANTED_ON IN ('DATABASE', 'SCHEMA', 'TABLE')
  AND NAME = 'SNOWFLAKE_SAMPLE_DATA'
  -- THE GRANTEE (ie. whom the permission has been granted to) is public
  AND GRANTEE_NAME = 'PUBLIC'

  -- actions for next time 12/08/2025
  -- 1) restrict the scope of the public role
  -- 2) create a new user/role after restricting the public role
  -- 3) assign user (me)  to the new role
  -- 4) test whether the new role can access the share prior to explicitly granting access to the share
  -- 5) test whether the new role once access is granted to the share can also access the row level access policy
  --    hopefully the user will only be able to access the row level access policy if the user is assigned to the database role
  --    which is used in the row level access policy

  -- try revoking the database role from the newly created role
  -- this won't work because the newly created role has permissions from the public role
  -- and the public role has permissions to the database role

  REVOKE DATABASE ROLE DB_ROLE_PROJECT1 FROM ROLE DB_ROLE_PROJECT1;




USE ROLE ACCOUNTADMIN;
ALTER USER GARTHJON2 UNSET DEFAULT_SECONDARY_ROLES;

ALTER USER GARTHJON2 SET DEFAULT_SECONDARY_ROLES = ();

USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE DB_ROLE_PROJECT2 TO ROLE DB_ROLE_PROJECT1;

USE ROLE DB_ROLE_PROJECT1;
SELECT COUNT(*) AS COUNTALL, USE_CONCEPT_ID FROM PATIENT_CONTACT
GROUP BY USE_CONCEPT_ID





USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE DATABASE PRODUCER_SHARE_COMPASS
FROM SHARE CT32643.PROJECT1_SHARE;

USE ROLE ACCOUNTADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE research_role2;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE database_role;
USE ROLE ACCOUNTADMIN;
REVOKE IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS FROM ROLE DB_ROLE_PROJECT1;

-- on checking whether or not the shared database role is active in session
-- in theory it shouldn't be active until i explicitly grant

select IS_DATABASE_ROLE_IN_SESSION('DB_ROLE_PROJECT2');

select current_role()
SELECT IS_DATABASE_ROLE_IN_SESSION('DB_ROLE_PROJECT2');
SELECT CURRENT_SECONDARY_ROLES();
SELECT CURRENT_ROLE();
SHOW GRANTS TO DATABASE ROLE DB_ROLE_PROJECT2;
SHOW GRANTS TO USER GARTHJON2;
SHOW USERS LIKE 'GARTHJON2';
-- check for role nesting
SHOW GRANTS OF ROLE accountadmin;






USE ROLE ACCOUNTADMIN;
ALTER USER GARTHJON2 UNSET DEFAULT_SECONDARY_ROLES;
ALTER USER GARTHJON2 SET DEFAULT_SECONDARY_ROLES = ();


USE ROLE ACCOUNTADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT1 FROM ROLE PUBLIC;



USE ROLE research_role2;
SELECT COUNT(*) AS COUNTALL, USE_CONCEPT_ID FROM PATIENT_CONTACT
GROUP BY USE_CONCEPT_ID


use role accountadmin;
GRANT IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS TO ROLE DB_ROLE_PROJECT1;

use role accountadmin;
REVOKE IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS FROM ROLE DB_ROLE_PROJECT1;

use role accountadmin;
REVOKE IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS FROM ROLE PUBLIC;

use role accountadmin;
CREATE OR REPLACE DATABASE PRODUCER_SHARE_COMPASS
FROM SHARE CT32643.PROJECT1_SHARE;



USE ROLE RESEARCH_ROLE;
SELECT COUNT(*) AS COUNTALL, USE_CONCEPT_ID FROM PATIENT_CONTACT
GROUP BY USE_CONCEPT_ID

USE ROLE ACCOUNTADMIN;
REVOKE USAGE ON DATABASE producer_share_compass FROM ROLE PUBLIC;
REVOKE USAGE ON SCHEMA shared_db.schema_name FROM ROLE PUBLIC;
REVOKE SELECT ON ALL TABLES IN SCHEMA shared_db.schema_name FROM ROLE PUBLIC;


show roles;


SHOW GRANTS TO ROLE DB_ROLE_PROJECT1;

USE ROLE ACCOUNTADMIN;
SHOW GRANTS TO ROLE PUBLIC;

-- THE FOLLOWING COMMAND REMOVES ALL PERMISSIONS/ACCESS TO THE SHARED DATABASE

USE ROLE ACCOUNTADMIN;
REVOKE IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS FROM ROLE DB_ROLE_PROJECT1;

USE ROLE ACCOUNTADMIN;
REVOKE IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS FROM ROLE PUBLIC;


GRANT IMPORTED PRIVILEGES ON DATABASE PRODUCER_SHARE_COMPASS TO ROLE DB_ROLE_PROJECT1;


USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE RESEARCH_ROLE2;
-- get the details of the current user
--select current_user()
-- grant the role to the current user
GRANT ROLE RESEARCH_ROLE2 TO USER GARTHJON2;




REVOKE DATABASE ROLE DB_ROLE_PROJECT1 FROM ROLE DB_ROLE_PROJECT1;

REVOKE DATABASE ROLE DB_ROLE_PROJECT1 FROM ROLE PUBLIC;


SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE 
--
--NAME != 'SNOWFLAKE_LEARNING_ROLE'
--AND GRANTED_TO = 'ACCOUNTADMIN'
GRANTED_TO = 'ROLE'
AND 
GRANTEE_NAME = 'DB_ROLE_PROJECT1'
-- GRANTEE RECEIVES THE PRIVILEDGE
ORDER BY GRANTEE_NAME


SELECT CURRENT_SECONDARY_ROLES();

-- check is database role is active in the current session
SELECT IS_DATABASE_ROLE_IN_SESSION('DB_ROLE_PROJECT2');

-- list all available roles against the current user
show roles;

-- using an account admin revoke permissions from the database role for all possible primary functional roles against the user
use role accountadmin;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE ACCOUNTADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  DB_ROLE_PROJECT1;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  ORGADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  PUBLIC;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  RESEARCH_ROLE;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  RESEARCH_ROLE2;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  SECURITYADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  SNOWFLAKE_LEARNING_ROLE;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  SYSADMIN;
REVOKE DATABASE ROLE DB_ROLE_PROJECT2 FROM ROLE  USERADMIN;