G-1150
🆓Error
Always limit privileges of schema owners according to principle of least privileges.
Reason
The schema owner of a PinkDB application usually does not need system privileges at runtime. However, during development we want to connect as the schema owner (e.g. via proxy user). For this we need more system privileges, like CREATE TABLE or CREATE PROCEDURE. In any case, we do not need extensive privileges such as SELECT ANY TABLE' or even theDBA' role. Therefore, you should keep the list of allowed system privileges for schema owners to the absolute minimum.
Example
Non-Compliant Example
grant select any table to dbl_owner;
Compliant Solution - ★★★★★
-- grant read access explicitly to required tables/views
Tests
Test SQL query
with
upriv as (
-- system privileges granted to users
select u.username, p.privilege as priv
from dba_sys_privs p
inner join dba_users u
on u.username = p.grantee
union all
-- roles granted to users
select u.username, p.granted_role as priv
from dba_role_privs p
inner join dba_users u
on u.username = p.grantee
),
rpriv as (
-- roles without parent (=roots)
select role as priv, null as parent_priv
from dba_roles
where role not in (
select rrp.granted_role
from role_role_privs rrp
)
union all
-- roles granted to roles
select granted_role as priv,
role as parent_priv
from role_role_privs
union all
-- system privileges granted to roles
select privilege as priv,
grantee as parent_priv
from dba_sys_privs
where grantee in (
select r.role
from dba_roles r
)
),
rtree as (
-- provides priv_path and is_leaf for every role/priv in the hierarchy
select priv,
parent_priv,
sys_connect_by_path(priv, '/') || '/' as priv_path,
case
when connect_by_isleaf = '0' then
0
else
1
end as is_leaf
from rpriv
connect by prior priv = parent_priv
),
rgraph as (
-- provides per priv all its possible parent privs or in other words
-- provides per parent priv all its possible child privs
-- this allows to join with parent_priv to get all privs
select priv,
substr(priv_path, 2, instr(priv_path, '/', 2) - 2) as parent_priv,
is_leaf
from rtree
),
ugraph as (
-- extends the rgraph by users and their privs
-- distinct is required since a priv can be part of multiple roles
select distinct
upriv.username,
rgraph.priv,
cast(rgraph.parent_priv as varchar2(128 byte)) as granted_priv,
case
when rgraph.priv = rgraph.parent_priv then
1
else
0
end as direct_grant,
rgraph.is_leaf
from upriv
join rgraph
on upriv.priv = rgraph.parent_priv
)
-- main
select username || '.' || priv as identifier,
'Schema ' || username || ' has unexpected privilege ' || priv || '.' as message
from ugraph
where is_leaf >= 0
and priv not in (select role from dba_roles)
and priv not in (#AllowedSchemaSystemPrivileges#)
and username in (#SchemaNames#)Test results
| Identifier | Message | Migration |
|---|---|---|
| DBL_OWNER.SELECT ANY TABLE | Schema DBL_OWNER has unexpected privilege SELECT ANY TABLE. | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| AllowedSchemaSystemPrivileges | Comma separated list of allowed system privileges for schema owners. | create session, create table, create procedure, create view, create public synonym, drop public synonym, select any dictionary, debug connect session, debug any procedure, create any context, debug connect any |
| SchemaNames | Comma separated list of database schemas owning the database objects of an application. | dbl_owner |
References
- related to PinkDB Feature 2
The owner of the database objects should also follow the principle of least privilege. Both at runtime and at design time.