DBSecurity: REVOKE ROLE, credential propagation, + Overview 1) Corrects a CLI/Executor overwrite problem and removes workaround code in PrivMgr. Launchpad bug #1371176. 2) REVOKE ROLE now lists referencing and referenced objects when a revoke request is refused due to dependencies. 3) REVOKE ROLE now reports that the specified grant cannot be found when grantor has not granted the role to the user. Previously the misleading error "Not Authorized" was issued, which as confusing when the user was DB__ROOT. The same change was made for REVOKE COMPONENT PRIVILEGE. A similar change will be made in the future for revoking object privileges. 4) REVOKE ROLE now considers grants to PUBLIC before concluding a revoke would require a dependent object to be dropped. 5) User credential are now propagated to the the compiler process. Launchpad bug 1373112.
Externals If the priv/role, grantee, grantor tuple does not exist, REVOKE ROLE/REVOKE COMPONENT PRIVILEGE now reports error 1018: Grant of role or privilege <name> from <grantor> to <grantee> not found, revoke request ignored.
When REVOKE ROLE detects a dependent object, error message 1364 now reports the referencing and the referenced object.
Cannot revoke role <role-name>. Object <referencing-object> depends on privileges on object <referenced-object>.
Details for user credential propagation:
The propagate user credentials code has only been partially implemented. The existing code sends the user ID to the first compiler process. Other compiler processes started would not get the connected user ID instead the DB__ROOT user ID became the user by default. Therefore, privilege checks are succeeding when they should fail.
User credentials consist of an integer user ID and a username. The existing code only passed the user ID. The compiler process would then do a metadata look-up to get the username. If we kept this model, then we would get into an infinite loop:
When the compiler process received the user ID, it did a metadata read to get the associated username. After reading the metadata, both the username and user ID was set in context globals. The metadata lookup code will start another arkcmp process for the compilation request. The compilation would then start a compiler process. That compiler process would start another compiler process, etc.
The solution is to send both the username and user ID to the compiler process. Both values are known at the time the compiler process is started. This alleviates the need for a database look-up when the compiler process starts. To do this a new session attribute was created - SESSION_DATABASE_USER. This session attribute sends both the user ID and username to the compiler process during startup processing.
Once we were able to start a compiler process and store a user ID other than DB__ROOT in the Context globals, another similar infinite loop occurred during privilege checking. For example, a showddl command starts a compiler process when extracting privilege information. The compiler calls checkPrivileges to make sure the current user has privileges. The checkPrivileges statement makes a metadata request that requires a compilation. This starts up another compiler process. This compiler process is sent the metadata request. When compiling the metadata request in the new compiler process, checkPrivileges is called which starts a compiler process, …
This worked previously because the user passed was DB__ROOT, and the code in checkPrivileges is short circuited and the metadata call is avoided.
A fix to set the parserflag (INTERNAL_QUERY_FROM_EXEUTIL) before the metadata request was performed. This fix requires that the file "SqlParserGlobalsCmn.h" be included in additional files. Including this file needs to be done with care. In order to get everything to compile, we changed where this file was included in several places.
Once all these changes were made, the envvar: DBUSER_DEBUG now works. If set, then good details about how users are sent to different processes is displayed.