When you are using Oracle Database 19c in a company the people with DBA accounts have a lot of power. They can change the security settings manage the data and control the database.
Because of this it is very important to check and audit the DBA roles. This is necessary for following the rules making sure everything is safe and keeping the database running
This article will show you how to find the DBA users check their privileges and set up auditing controls in Oracle 19c.
1. Identifying DBA Accounts
The first thing to do when checking the security is to make a list of all the users who have DBA privileges.
SELECT grantee, granted_role FROM dba_role_privs WHERE granted_role = 'DBA';
This query will return all the accounts that currently have DBA privileges
2. Checking SYSDBA and SYSOPER Privileges
There are higher levels of access than the DBA role, which are SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG and SYSKM privileges.
SELECT * FROM v$pwfile_users;
This view will show you the users who are allowed to access the database through the password file.
3. Verifying Direct System Privileges
Some users may not have the DBA role. They can still have a lot of power.
SELECT grantee, privilege FROM dba_sys_privs
WHERE privilege IN ('CREATE USER','ALTER USER','DROP USER','GRANT ANY PRIVILEGE','ALTER SYSTEM') ORDER BY grantee;
This will help you find the users who have a lot of power but are not part of the DBA group.
4. Checking Role Inheritance (Nested Privileges)
The DBA privileges can be passed down through roles.
SELECT * FROM dba_role_privs WHERE grantee IN (SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA');
This will help you find the ways that users can get DBA privileges.
5. Auditing DBA Role Usage
To track the use of DBA privileges you need to enable the Unified Auditing policies.
Create audit policy for DBA role usage
AUDIT POLICY audit_dba_role ROLES DBA;
Enable the policy
AUDIT POLICY audit_dba_role;
6. Monitoring DBA Activity
Once the auditing is enabled you can track the actions of the DBA users using:
SELECT event_timestamp,dbusername,action_name,return_code,object_schema object_name FROM unified_audit_trail
WHERE dbusername IN (SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA') ORDER BY event_timestamp DESC;
This will show you everything the DBA users are doing.
7. Detecting DBA Behavior
Failed privileged actions
SELECT * FROM unified_audit_trail WHERE return_code <> 0 AND action_name IN ('ALTER SYSTEM', 'CREATE USER', 'DROP USER');
Privilege escalation attempts
SELECT * FROM unified_audit_trail WHERE action_name IN ('GRANT', 'REVOKE') ORDER BY event_timestamp DESC;
8. Best Practices for DBA Security
To make the database more secure:
- Limit the number of users who have DBA access to those who need it
- Do not let multiple people use the same DBA account
- Regularly check the `DBA_ROLE_PRIVS`
- Always enable Unified Auditing
- Send the audit logs to a system, for security
- Keep the DBA and security roles
- Change the passwords of the SYS and SYSTEM accounts often
- Watch for attempts to escalate privileges in time
Checking the DBA roles in Oracle 19c is very important to keep the database safe and prevent actions.
By using the queries to check privileges and the Unified Auditing policies companies can see everything the DBA users are doing and make their database more secure.
So it is very important to check the DBA roles and audit them to keep the Oracle Database 19c safe.
Google+
Facebook
