Mir Sayeed Hassan – Oracle Blog

Brief explanation about the above parameter used in profile.

PASSWORD_LIFE_TIME 30 – Password is valid for 30 days
PASSWORD_GRACE_TIME 10 – User has 10 days to change it after expiry PASSWORD_REUSE_TIME 1800 – Old passwords can’t be reused for 1800 days PASSWORD_REUSE_MAX UNLIMITED – No restriction on reuse count FAILED_LOGIN_ATTEMPTS 3 – Account locks after 3 failed login attempts PASSWORD_LOCK_TIME 5/1440 – Account stays locked for 5 minutes PASSWORD_VERIFY_FUNCTION verify_function – Enforces password rules (complexity check)

The packages are important for revoking permission from the PUBLIC by using the SYS login in database

• UTL_SMTP This will allows user to send emails.

• UTL_TCP This will allows user to send data from database to any TCP/IP service listening on a specific port.

• UTL_HTTP This will allows user to send data via HTML form to a malicious website.

• UTL_FILE This will allows user to have access to OS files.

  PASSWORD

  Use this query to identify all the PUBLIC Granted packages from the database.

  SQL> SELECT table_name FROM dba_tab_privs WHERE owner='SYS' AND privilege = 'EXECUTE' AND grantee='PUBLIC' ;

  Methods of Authentication

• Authentication methods are used to verify user identity and can be classified as:

  Basic Authentication

     •  Password Authentication – Uses a username and password for access. Advanced Authentication

• Token-Based Authentication – Uses OTPs, smart cards, or authentication apps.

Biometric Authentication – Uses unique physical traits such as fingerprint, iris, or facial structure.

The database AUDIT :

How to audit a database.

Set the below parameter in database spfile/pfile.
AUDIT_TRAIL = NONE or DB or OS

The database Audit data can be stored in a database in the form of (SYS.AUD$) table or in an OS file residing in default location AUDIT_FILE_DEST=$ORACLE_HOME/rdbms/audit.

To audit the database, there are 4 method of auditing

• Statement auditing

• System Privilege Auditing

• Object auditing

Statement Auditing

• AuditTable; Audit create, drop, truncate table

• Audit table by mir Audit; create, drop, truncate table by user mir

• Audit table by mir whenever successful; only when action is successful

• Audit table by mir whenever unsuccessful; only when action is successful

Statement Auditing

SQL> AuditTable; 
Audit create, drop, truncate table

SQL> Audit table by mir Audit; 
create, drop, truncate table by user mir

SQL> Audit table by mir whenever successful; 
only when action is successful

SQL> Audit table by mir whenever unsuccessful; 
only when action is successful

How to disable auditing.

SQL> noaudit table; 

What is being audited

SQL> SELECT audit_option, failure, success, user_name FROM dba_stmt_audit_opts ORDER BY audit_option, user_name;

AUDIT_OPTION      FAILURE     SUCCESS      USER_NAME 
———————————————————————————————————----------------——

How to examine the audit trail

SQL> SELECT FROM WHERE username, timestamp, action_name dba_audit_trail username = ‘MIR’;

MIR       06-May-2026 18:43:52       LOGOFF
MIR       06-May-2026 18:44:19       LOGON
MIR       06-May-2026 18:46:01       CREATE TABLE

Note:

LOGON MEANS AUDIT SESSION 
LOGOFF MEANS AUDIT SESSION
CREATE TABLE MEANS AUDIT TABLE

System Privilege Auditing

SQL> Audit delete anytable;

LOGON LOGOFF LOGON CREATE TABLE

SQ.L> Audit delete any table by MIR by session;

Note: This will generate less entries in dba_audit_trail

Query to get what is being audited

SQL> Select privilege, user_name from dba_priv_audit_opts order by privilege, user_name;

PRIVILEGE                USER_NAME
-----------------------------------
ALTER PROFILE
DELETE ANY TABLE           MIR 
ALTER USER                 MIR

Note:

user updates 1000,000 records in a table belonging to another user, you only gather one audit record).

⁃  If the BY SESSION clause is not specified, the default is BY ACCESS.

⁃  Consider using the BY SESSION clause to limit the performance & storage impact of system privilege auditing.

Object Auditing

The Object auditing tracks actions performed on schema objects such as tables, views, sequence, synonyms and procedures. Unlike statement or system privilege auditing, it cannot be limited to specific users—it applies to all users or none.

SQL> AUDIT select ON mir.emp_sal;
select will be tracked for all users on emp_sal table

SQL> AUDIT select ON mir.emp_sal BY ACCESS WHENEVER SUCCESSFUL; 

Here one audit entry for each trigging statement

SQL> AUDIT select ON mir.emp_sal BY SESSION WHENEVER NOT SUCCESSFUL;