GeezerVison is showing github.com — Protect data in utPLSQL tables · Issue #922 · utPLSQL/utPLSQL
Protect data in utPLSQL tables #922DescriptionDescription
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 Activityjgebal commented on 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 commented on Jun 15, 2019 I have started looking on some of that. Reacted by Jacek Gębal and Philipp Salvisberg jgebal commented on Jun 15, 2019 We could have a generic test to confirm that none of utPLSQL tables are accessible directly to non elevated users. Reacted by Philipp Salvisberg and Lukasz Wasylow jgebal commented on 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 commented on 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 commented on 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 commented on 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';Reacted by Lukasz Wasylow and Jacek Gębal
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|