Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Protect data in utPLSQL tables #922

Closed
PhilippSalvisberg opened this issue May 11, 2019 · 7 comments · Fixed by #954

Comments

@PhilippSalvisberg
Copy link
Member

commented May 11, 2019

Description

The following utPLSQL tables are exposed directly:

Table Name Comment
DBMSPCC_BLOCKS select, insert, update, delete to public
DBMSPCC_RUNS select, insert, update, delete to public
DBMSPCC_UNITS select, insert, update, delete to public
UT_ANNOTATION_CACHE select to public
UT_ANNOTATION_CACHE_INFO select to public
UT_COMPOUND_DATA_DIFF_TMP select, insert, update, delete to public
UT_COMPOUND_DATA_TMP select, insert, update, delete to public
UT_SUITE_CACHE select to public
UT_SUITE_CACHE_AFTER_ALL select to public
UT_SUITE_CACHE_AFTER_EACH select to public
UT_SUITE_CACHE_AFTER_TEST select to public
UT_SUITE_CACHE_BEFORE_ALL select to public
UT_SUITE_CACHE_BEFORE_EACH select to public
UT_SUITE_CACHE_BEFORE_TEST select to public
UT_SUITE_CACHE_PACKAGE select to public
UT_SUITE_CACHE_SCHEMA select to public
UT_SUITE_CACHE_TRHOWS select to public
UT_SUITE_CACHE_WARNINGS select to public

In a shared environment these tables may contain sensitive data. At least some dictionary data is exposed to all users in the database which are otherwise not available. Hence the data must be protected.

Direct access to these tables must be revoked (as already done e.g. for PLSQL_PROFILER_DATA or UT_DBMS_OUTPUT_CACHE).

Furthermore the access to data of these tables must be granted to the owner of the tests and test runs only.

utPLSQL Version

v3.1.7.2935-develop

@jgebal

This comment has been minimized.

Copy link
Member

commented May 11, 2019

We will have to introduce a set of internal APIs so that the tables can lny be accessed internally by utPLSQL framework code.

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jun 15, 2019

I have started looking on some of that.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jun 15, 2019

We could have a generic test to confirm that none of utPLSQL tables are accessible directly to non elevated users.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jun 15, 2019

This is going to be a challenge with some table as ee need to join with all_objects.
We would probably need to introduce views to show only odata vesible to user.
We would also need to have pipelined functions in definer-rights packages to enable direct access to framework tables from private invoker rights packages.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jun 15, 2019

The idea is:

  • If suite schema owner or elevated user use pipelined function.
  • else - use views that join to all_objects
@lwasylow

This comment has been minimized.

Copy link
Member

commented Jun 15, 2019

Not all tables that are exposed as public are used in authid packages. Some are only reference in definer which means that taking away the public grant should take care of things. For example annotation cache i believes if memory server me right only in annotation manager which is internal and definer anywya

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member Author

commented Jun 15, 2019

The test could be based on the following query, which should return 0 rows. It is based on the query on my blog post to check compliance to SmartDB properties.

WITH
   -- roles as recursive structure
   role_base AS (
      -- roles without parent (=roots)
      SELECT r.role, NULL AS parent_role
        FROM dba_roles r
       WHERE r.role NOT IN (
                SELECT p.granted_role
                  FROM role_role_privs p
             )
      UNION ALL
      -- roles with parent (=children)
      SELECT granted_role AS role, role AS parent_role
        FROM role_role_privs
   ),
   -- roles tree, calculate role_path for every hierarchy level
   role_tree AS (
      SELECT role,
             parent_role,
             sys_connect_by_path(ROLE, '/') AS role_path
        FROM role_base
      CONNECT BY PRIOR role = parent_role
   ),
   -- roles graph, child added to all ancestors including self
   -- allows simple join to parent_role to find all descendants
   role_graph AS (
      SELECT DISTINCT
             role,
             regexp_substr(role_path, '(/)(\w+)', 1, 1, 'i', 2) AS parent_role
        FROM role_tree
   ),
   -- application users in scope of the analysis
   -- other users are treated as if they were not installed
   app_user AS (
      SELECT username
        FROM dba_users
       WHERE username IN ('UT3', 'UT3_LATEST_RELEASE')
   ),
   obj_priv AS (
      -- objects granted directly to users
      SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
        FROM dba_tab_privs p
        JOIN app_user u ON u.username = p.grantee
       WHERE p.owner IN (
                SELECT u2.username
                  FROM app_user u2
             )
      UNION
      -- objects granted to users via roles
      SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
        FROM dba_role_privs r
        JOIN app_user u ON u.username = r.grantee
        JOIN role_graph g ON g.parent_role = r.granted_role
        JOIN dba_tab_privs p ON p.grantee = g.role
       WHERE p.owner IN (
                SELECT u2.username
                  FROM app_user u2
             )
      -- objects granted to PUBLIC
      UNION
      SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
        FROM dba_tab_privs p
       CROSS JOIN app_user u
       WHERE p.owner IN (
                SELECT u2.username
                  FROM app_user u2
             )
         AND p.grantee = 'PUBLIC'
   )
SELECT * 
  FROM obj_priv
 WHERE object_type = 'TABLE';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.