G-1140

🆓

Warning

Avoid granting object privileges directly to connect users.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 60 mins
Other
Database Support: OracleDB (v12.2.0 +)

Reason

Granting object privileges directly to connect users can lead to redundancies or inconsistencies for connect users requiring the same or similar object privileges. Grant object privileges to database roles instead to simplify the management of privileges.

Example

Non-Compliant Example

grant read on dbl_configs_v to dbl_backend;
grant read on dbl_config_rules_v to dbl_backend;

Compliant Solution - ★★★★★

grant dbl_backend_role to dbl_backend;
grant read on dbl_configs_v to dbl_backend_role;
grant read on dbl_config_rules_v to dbl_backend_role;

Tests

Test SQL query

select grantee || '.' || owner || '.' || table_name || '.' || privilege as identifier,
       'Connect user ' || grantee || ' has ' || privilege || ' rights on ' || owner || '.' || table_name || '.' as message
  from dba_tab_privs
 where grantee in (#ConnectUsers#)
 order by identifier

Test results

Identifier	Message	Migration
DBL_BACKEND.DBL_OWNER.DBL_CONFIG_RULES_V.READ	Connect user DBL_BACKEND has READ rights on DBL_OWNER.DBL_CONFIG_RULES_V.	-
DBL_BACKEND.DBL_OWNER.DBL_CONFIGS_V.READ	Connect user DBL_BACKEND has READ rights on DBL_OWNER.DBL_CONFIGS_V.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
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

References

related to PinkDB Feature 2
The connect user has access to API objects only. API object permissions do not need to be granted via roles, but it is good practice to do so.