Snowflake Is Role In Session Context Functions

What does is_role_in_session() do?

Returns TRUE if the user’s current role in the session (i.e. the role returned by CURRENT_ROLE) inherits the privileges of the specified role. The specified role can be the current role or any role lower in the role hierarchy.

Returns FALSE if the specified role is either higher in the role hierarchy of the current role or is not in the role hierarchy at all.

SQL Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

 -- Syntax
select is_role_in_session();  -- end up with error (it needs a paramter)
select is_role_in_session;    -- since it needs a parameter, we have to use the (), else it ends with error
    
-- role hierarchy 
-- accountadmin >> securityadmin/sysadmin >> useradmin >> public
    
use role securityadmin;
select is_role_in_session('securityadmin'); -- this is equal to current_role()
    
-- with lower role in hiearchy
select is_role_in_session('securityadmin'),is_role_in_session('useradmin'),is_role_in_session('public'); 
        
-- upper case and lower case 
-- how does it work
select is_role_in_session('SECURITYADMIN'), is_role_in_session('securityadmin');
    


-- what if role does not exist or incorrect role name
-- in both case, you would get false value
-- so be careful
    
select is_role_in_session('accountadmin'), is_role_in_session('acountadmin');
    
-- Since this data comes from cloud service layer, you don't need any running compute
select current_warehouse();
select is_role_in_session('SECURITYADMIN');-- the warehouse is still suspended.
    
 -- What kind of function is_role_in_session() scalar/tabular?
show functions like 'is_role_in%'; 
    
-- it is built-in function, is_ansi = true, and scalar function.
--any user profile can run the above show function. 

-- assigned to a variable
set useradmin_role_exist = is_role_in_session('useradmin');
select $useradmin_role_exist;
    
set (role_useradmin_exist,role_accountadmin_exist) = (is_role_in_session('useradmin'),is_role_in_session('accountadmin')); 
select $role_useradmin_exist,$role_accountadmin_exist;
    
-- CAUTION - make sure the open and close bracket at both side of equal operator matches, else compilation error
    
-- Lets undestand the snowflake default role hierarchy 
-- https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#role-hierarchy-and-privilege-inheritance
    
-- select role public
use role sysadmin;
select  is_role_in_session('public'),
        is_role_in_session('useradmin'),
        is_role_in_session('securityadmin'),
        is_role_in_session('sysadmin'),
        is_role_in_session('accountadmin');
          
-- 2nd example of customer role 
    
use role accountadmin;
select  is_role_in_session('business_analyst'),
            is_role_in_session('dev_ops_admin'),
            is_role_in_session('elt_developer'),
            is_role_in_session('project_admin'),
            is_role_in_session('sysadmin'),
            is_role_in_session('accountadmin');