Description

pajusin

opened on Mar 5, 2020

Issue body actions

Script check_sys_grants checks only user_sys_privs. Problem is that on some oracle configurations sys privs can be also configured via roles (view ROLE_SYS_PRIVS).

Original code for getting sys privileges (from user_sys_privs)
minus (select privilege from user_sys_privs union all select replace(privilege,' ANY ') privilege from user_sys_privs)

My proposal is to union also view ROLE_SYS_PRIVS, because on my configuration there are some privileges which are provided via role.

minus (select privilege from user_sys_privs union all select replace(privilege,' ANY ') privilege from user_sys_privs union all select privilege from role_sys_privs )

Source views for SYS privileges:

• user level
  select PRIVILEGE from user_sys_privs
• role level
  select PRIVILEGE from role_sys_privs

l_version=12.2.0.1.0
l_compatibility=12.2.0

Activity

1. pajusin commented on Mar 9, 2020

   

   pajusin

   on Mar 9, 2020

   Author

   More actions

   Update patch proposal including ANY prvilege removal:

    source/check_sys_grants.sql | 9 ++++++++-
    1 file changed, 8 insertions(+), 1 deletion(-)
   
   diff --git a/source/check_sys_grants.sql b/source/check_sys_grants.sql
   index 7b164495..4e3da8ca 100644
   --- a/source/check_sys_grants.sql
   +++ b/source/check_sys_grants.sql
   @@ -22,7 +22,14 @@ begin
        from user_sys_privs
        union all
        select replace(privilege,' ANY') privilege
   -    from user_sys_privs)
   +    from user_sys_privs
   +    union all
   +    select privilege
   +    from role_sys_privs
   +    union all
   +    select replace(privilege,' ANY') privilege
   +    from role_sys_privs
   +    )
      );
      if l_missing_grants is not null then
        raise_application_error(
   
   
2. jgebal commented on Mar 10, 2020

   

   jgebal

   on Mar 10, 2020

   Member

   More actions

   Thank you for reporting this @pajusin

   It's actually great improvement and I've learned something new about data-dictionary.

   The query that we will be using will be also checking if role is active in session.

   The view role_sys_privs shows roles that are inactive and can be inaccurate in that way.
   We will join with session_roles to look at only the active roles before performing the install.
3. jgebal

   self-assigned this

   on Mar 12, 2020
4. jgebal

   added

   bug

   on Mar 12, 2020
5. jgebal

   added this to the 3.1.11 milestone on Mar 12, 2020
6. jgebal

   added a commit that references this issue on Mar 12, 2020

   Added check for sys grants added via role when installing utPLSQL fra…

   This issue will close once commit 4979cf9 is merged into the 'develop' branch.

   4979cf9
7. jgebal

   closed this as completedin #1056on Mar 14, 2020

Issue actions