Solved: Every user can see every role

[drumcircle]

Users can see more senior or different roles than they have been granted.

You can see this plainly in the demo accounts.  Is this is a feature or a bug?

[exa-Uwe]

Hi drumcircle,

this is intentionally so.

Every user can see all existing roles with EXA_ALL_ROLES - not only through this particular SQL Client your screenshot comes from.

Of course that doesn't mean every user can exercise any priviliges contained in these roles.

Best regards

Uwe

[exa-Chris]

Hi, in what Scenario would this create an issue? Rgds Christian

[drumcircle]

One of my clients grants privileges for multi-tenancy by role, so schema permissions need not be assigned to every single user. They actually decided not to use roles at all due to this visibility leakage issue. Is there another approach to multi-tenant security that makes this a non-issue?

[drumcircle]

Could ALL_ROLES visibility be made configurable???

[exa-Nico]

The visibility of EXA_ALL_ROLES is not configurable. To change it would be an entire new improvement request/feature, but to be honest, I'm not sure if it makes sense in this case. Simply knowing the role name and that the role exists does not necessarily make it dangerous. You could make the role names so convoluted that knowing the role name won't give any information as to it's purpose or what access it's granting. 

I would not recommend doing multi-tenancy on users. For one, if you accidentally delete a user who is the owner of a schema, then the schema data is completely gone. Roles protect you from that. It's also much easier to manage permissions if all you have to do is add or remove roles to/from a user. Also, users are also able to see the names of every other user in the database via EXA_ALL_USERS. 

One improvement in version 7.0 would be that we are introducing a new USAGE privilege which will be able to hide all schema information (for example in EXA_SCHEMAS) or in your database browser, so unless you have this privilege, you don't know that the role is the owner of a schema that you are not allowed to see. 

Another option for multi-tenancy would be to create completely separate databases for each tenant, but this is significantly more expensive 😁

Not sure if you've seen this article, but maybe it helps a bit! 

[drumcircle]

You have made the case for why this is a serious issue. Thank you for understanding.

[drumcircle]

[drumcircle · Accepted solution]

Nico came up with a great solution and is either too modest or too busy to post to the forum.  I sleep better tonight.

He created a preprocessor which searches for the tables EXA_ALL_ROLES and EXA_ALL_TABLES within the SQL text sent by the user, and if it detects these strings, then it will check if the user has the SELECT ANY DICTIONARY privilege (which lets users see every statistics/sys table and is also granted to DBA) as a role.

When a user who doesn't have SELECT ANY DICTIONARY runs SELECT * FROM EXA_ALL_ROLES, they get this error message which you can customize.

[Code: 0, SQL State: 43100] While preprocessing SQL with "ALLSCRIPTS"."PP_HIDE_TABLES": 43000:"You must be a DBA or have SELECT ANY DICTIONARY to run this query" caught in script "ALLSCRIPTS"."PP_HIDE_TABLES" at line 18 (Session: 1673753318205978533)

This is an extremely simple script and would need to be expanded for other cases/tables/roles, etc)

--/
CREATE OR REPLACE LUA SCRIPT "ALLSCRIPTS"."PP_HIDE_TABLES" () RETURNS ROWCOUNT AS
    sql_text = sqlparsing.getsqltext()
    sql_text = string.upper(sql_text)
    tokens = sqlparsing.tokenize(sql_text)
    
        disallowed_table_names = {'EXA_ALL_ROLES','EXA_ALL_USERS'}
        for i=1, #disallowed_table_names do
                matched_name = sqlparsing.find(tokens,1,true,true,sqlparsing.iswhitespaceorcomment,disallowed_table_names[i])

                if matched_name ~= nil then
                        string_found = true
                        for i=1, #matched_name do
                                --if a match is found, check if they have SELECT ANY DICTIONARY
                                if sqlparsing.isidentifier(tokens[matched_name[i]]) then
                                        res = query([[SELECT * FROM EXA_ROLE_SYS_PRIVS WHERE PRIVILEGE = 'SELECT ANY DICTIONARY' UNION ALL SELECT * FROM EXA_USER_SYS_PRIVS WHERE PRIVILEGE = 'SELECT ANY DICTIONARY';]])
                                        if #res == 0 then
                                                -- User does not have SELECT ANY DICTIONARY privilege - throws error
                                                output([[error]])
                                                error([[You must be a DBA or have SELECT ANY DICTIONARY to run this query]])
                                        end
                
                                                
                                else
                                        output([[No identifier]])
                                        --sqlparsing.setsqltext(sql_text)       
                                
                                end
                                        
                                
                        end
                        
                
                end
        end
        output(sql_text)
        sqlparsing.setsqltext(sql_text)   
        
/

alter system set sql_preprocessor_script = "ALLSCRIPTS"."PP_HIDE_TABLES";

exa-Nico!  exa-Nico!  exa-Nico!  exa-Nico!  exa-Nico!  exa-Nico! 

[drumcircle]

You're kidding, right?  I've never met a DBA who would want every user to know the logins of every other user or the capabilities exposed via roles.  In an OEM/extranet situation, this is undesirable as shown by the need for obfuscated user names in the Exasol demo system.