G-1130
🆓Warning
Avoid granting table access to API roles.
Reason
The data in a schema should be protected by an API. An API consists of stored objects and views, but not tables. This allows the underlying table structure to be changed without directly affecting the API and therefore the data consumers.
Example
Non-Compliant Example
create table t (c1 integer); grant read, update on t to dbl_backend_role; grant read on dbl_dbms to public; grant read on dbl_severity_levels to dbl_backend;
Compliant Solution - ★★★★★
create table t (c1 integer); create view v as select * from t; grant read, update on v to dbl_backend_role; grant read on dbl_dbms_v to dbl_backend_role; grant read on dbl_severity_levels_v to dbl_backend_role;
Tests
Test SQL query
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 dba_role_privs p
join dba_roles pr
on pr.role = p.grantee
)
union all
-- roles with parent (=children)
select granted_role as role, grantee as parent_role
from dba_role_privs p
join dba_roles pr
on pr.role = p.grantee
),
-- 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
),
-- user object privileges
obj_priv AS (
-- objects granted directly to users
select p.grantee as username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
from dba_tab_privs p
join dba_users u
on u.username = p.grantee
where p.owner in (#SchemaNames#)
and u.username in (#ConnectUsers#)
union
-- objects granted to users via roles
select u.username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
from dba_role_privs r
join dba_users 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 u.username in (#ConnectUsers#)
and p.owner in (#SchemaNames#)
union
-- objects granted to PUBLIC
select u.username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
from dba_tab_privs p
cross join dba_users u
where u.username in (#ConnectUsers#)
and p.owner in (#SchemaNames#)
and p.grantee = 'PUBLIC'
)
select username || '.' || owner || '.' || object_name || '.' || priv as identifier,
username || ' has ' || priv || ' rights on ' || lower(object_type) || ' ' || owner || '.' || object_name as message
from obj_priv
where object_type = 'TABLE'
order by username, owner, object_name, privTest results
| Identifier | Message | Migration |
|---|---|---|
| DBL_BACKEND.DBL_OWNER.DBL_SEVERITY_LEVELS.READ | DBL_BACKEND has READ rights on table DBL_OWNER.DBL_SEVERITY_LEVELS | - |
| DBL_BACKEND.DBL_OWNER.T.READ | DBL_BACKEND has READ rights on table DBL_OWNER.T | - |
| DBL_BACKEND.DBL_OWNER.DBL_DBMS.READ | DBL_BACKEND has READ rights on table DBL_OWNER.DBL_DBMS | - |
| DBL_BACKEND.DBL_OWNER.T.UPDATE | DBL_BACKEND has UPDATE rights on table DBL_OWNER.T | - |
| DBL_GUI.DBL_OWNER.DBL_DBMS.READ | DBL_GUI has READ rights on table DBL_OWNER.DBL_DBMS | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| SchemaNames | Comma separated list of database schemas owning the database objects of an application. | dbl_owner |
| ConnectUsers | Comma separated list of technical users connecting to the database. These users do not own database objects such as tables. | dbl_gui, dbl_backend |