Security

Unified Auditing in Oracle 23ai consolidates audit records into a single trail, managed via the DBMS_AUDIT_MGMT package. Let’s evaluate each option with extensive detail: A. Only viewed audit records can be purged from Unified Audits. False. There’s no concept of “viewed” audit records restricting purging. Unified Audit records ( stored in UNIFIED_AUDIT_TRAIL) can be purged based on time, policy, or m anual intervention, regardless of whether they’ve been viewed. This option misrepresents audit mana gement capabilities. Mechanics:Purging is controlled by retention policies or explicit commands, not view status. For example, records older than a set retention period (e.g., 90 days via DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY) are eligible for pu rging. Why Incorrect:No Oracle documentation ties purging to viewing, making this a fabricated limitation. B . Use DBMS_AUDIT_MGMT.CREATE_PURGE_JOB as a privileged user tosc hedule an automatic purge job. True. This procedure creates a scheduled job to automatical ly purge audit records based on a retention policy or custom criteria. It’s a standard method for ongoing audit trail maintenance, requiring privileges like AUDIT_ADMIN. Mechanics:Example: BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, JOB_FREQUENCY => 'DAILY ', JOB_STATUS => DBMS_AUDIT_MGMT.JOB_ENABLED); END;. This schedules daily p urges of old records, using the retention period set by SET_AUDIT_TRAIL_PROPERTY. Practical Use:Ideal for production environments to prevent the audit trail from growing indefinitely (e.g., avoiding tablespace exhaustion in SYSAUX). Edge Case:If no retention period is set, the job purges nothing until configured, highlighti ng the need for prior setup. C . Only the owner of a Unified Audit Policy can purge audit records by resetting the policy. False. Audit policies don’t have “owners” in the traditional sense; they’re created by users with AUDIT_ADMIN and managed globally. Resetting or disabling a p olicy (e.g., NOAUDIT POLICY my_policy) stops auditing but doesn’t purge existing records. Purging is a separate operation via DBMS_AUDIT_MGMT. Why Incorrect:This conflates policy management with a udit trail cleanup, which are distinct in Oracle. D . Use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL as a privileged user to manually purge audit records. True. This procedure manually purges all Unified Audit records up to the current timestamp (or a specified time), requiring AUDIT_ADMIN privileges. It’s a one -time cleanup tool. Mechanics:Example: BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRA IL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => FALSE); END;. This clears the entire trail unless restricted by a timestamp. Practical Use:Useful for immediate space reclamation or post-incident cleanup, unlike scheduled jobs. Edge Case:If the audit trail is large, this may require significant undo space and time, potentially impacting performance. E. Use DBMS_AUDIT_MGMT.DELETE_AUDIT_RECORDS('POLICY_NAME') as a privileged user to manually purge audit records of a specified Unified Policy. False. No such procedure exists in DBMS_AUDIT_MGMT. The package offers CLEAN_AUDIT_TRAIL and CREATE_PURGE_JOB, but nothing targets a specific policy’s records by name in this format. You can filter records in UNIFIED_AUDIT_TRAIL by policy (e.g., SELECT * WHERE UNIFIED_AUDIT_POLICIES = 'POLICY_NAME'), but purging is all- or -nothing or time-based, not policy- specific via a single command. Why Incorrect:This appears to be a fabricated or misinterpreted option, possibly confusing audit policy management with trail purging.

Unified Auditing is enabled at database creation or migration in 23ai. Let’s analyze: A . By querying V$OPTION for parameter Unified Auditing. True. SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; returns TRUE if enabled. It’s the definitive way to confirm Unified Auditing is active at the database level. Mechanics:V$OPTION reflects compiled- in features; TRUE indicates the binary was linked with Unified Auditing (uniauflt=on during relink). Practical Use:Quick, reliable check post-migration or upgrade. B . By using the LSINVENTORY Command to query the Oracle Database Software Library. False. LSINVENTORY (from OPatch) lists installed software components, not runtime features like auditing mode. C . By querying the DBA_UNIFIED_AUDIT_OPTION view. False. This view doesn’t exist; DBA_AUDIT_POLICIES or UNIFIED_AUDIT_TRAIL show policies and records but not migration status. D . By executing DBMS_AUDIT_MGMT PL/SQL package in Verify mode. False. No “Verify mode” exists in DBMS_AUDIT_MGMT; it manages audit trails, not migration validation.

User authentication in Oracle 23ai determines how users (especially administr ative ones) connect to the database. Let’s analyze each option with extensive detail: A. Operating System authentication may be used for system-privileged administrative users. True. OS authentication allows users mapped to OS account s (e.g., ops$oracle) to connect without a password, often used for administrative users like SYS or SYSTEM. Th is is configured by creating an externally authenticated user (e.g., CREATE USER "OPS$ORA CLE" IDENTIFIED EXTERNALLY) and relies on the OS to verify identity. Mechanics:When a user logs in via sqlplus/as sysdba, Oracle checks the OS user against the dba group (Unix) or ORA_DBA (Windows). If matched, no password is needed, leveraging OS security. Practical Use:Common for DBAs managing local instances, red ucing password management overhead. Edge Case:Requires REMOTE_LOGIN_PASSWORDFILE=NONE for exclusive OS auth, but this isn’t mandatory if a password file exists alongside. Historical Note:Introduced in early Oracle versions, this remains a robust option in 23ai for local admin access. B. Password authentication must be used for system-privileged administrative users. False. “Must” is incorrect; password authentication (e.g., sqlplus sys/password) is an option, not a requirement. OS authentication or password file authentica tion can also be used for users like SYS. This option overstates the necessity of password-ba sed login. Why Incorrect:Oracle’s flexibility allows multiple methods, contradicting the absolute phrasing here. C. Password File authentication is supported for any type of database user. False. Password file authentication is restricted to users wi th SYSDBA, SYSOPER, or similar system privileges (e.g., SYSBACKUP). Regular users (e.g., HR) can’t use the password file (orapw); they rely on database authentication (passwords stored in the DB) or ext ernal methods. Mechanics:The password file stores hashed credentials for privileged users, checked during remote AS SYSDBA logins. Why Incorrect:Extending this to “any user” ignores Oracle’s security model limiting password file usage. D. REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE to permit password changes for system-privileged administrative users. False. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE allows a ded icated password file for one instance, enabling password changes via ALTER USER SYS IDENTIFIED BY newpass. However, SHARED mode also permits changes for SYS, though not for other users added to the file. The “must” overstates the requirement; it’s sufficient, not ne cessary. Mechanics:EXCLUSIVE locks the file to one DB, while SHARED allows multiple DBs to use it, with restrictions on non-SYS users. E. Password File authentication must be used for system-privileged administrative users. True. For remote administrative access (e.g., sqlplus sys/pass word@orcl as sysdba), a password file is mandatory when REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE or S HARED. Local OS authentication is an alternative, but for network-based admin tasks, the password file is required, making this statement true in that context. Mechanics:Set via orapwd (e.g., orapwd file=orapworcl password=oracle entries=10), enabling remote SYSDBA logins. Edge Case:If REMOTE_LOGIN_PASSWORDFILE=NONE, only OS auth works locally, but this isn’t the default or typical setup.

Roles in Oracle manage privileges efficiently. Let’s dive into each option: A . Roles must be password protected. False. Roles can be password- protected (e.g., CREATE ROLE mgr IDENTIFIED BY secret), but it’ s optional. Non-protected roles (default) are enabled autom atically if granted, requiring no password. Mechanics:Password-protected roles need SET ROLE mgr IDENTIFIED BY secret, enhancing security for sensitive privileges. B . Roles may be granted to other roles. True. Roles can form hierarchies (e.g., GRANT clerk TO mgr), allowing nested privilege management. Mechanics:A user with mgr inherits clerk privileges indirectly. Revoking clerk from mgr cascades appropriately. Practical Use:Simplifies complex privilege structures in large organizations. C . The SET ROLE statement can enable one or more r oles for a session. True. SET ROLE role1, role2; activates specified roles for the session, assuming they’re granted and not password-protected (or password is provided). Mechanics:Enabled roles grant their privileges immedia tely within the session scope. D . Object privileges may not be granted to roles. False. Object privileges (e.g., GRANT SELECT ON emp TO clerk) are a primary use of roles, making this statement incorrect. Why Incorrect:Roles are designed for this purpose, contrad icting the option. E . All roles granted to a user are set on default when the user logs in. False. Only roles marked as DEFAULT ROLE (via ALTER USER ... DEFAULT ROLE role1) are ena bled at login. Non-default roles require SET ROLE. Mechanics:Check via SELECT * FROM DBA_ROLE_PRIVS WHERE DEFAULT_ROLE='YES'. F . The SET ROLE statement can disable one or more roles for a session. True. SET ROLE NONE disables all roles, or SET ROLE role1 implicitly disables others not listed, providing granular control. Practical Use:Useful for testing or restricting privileges temporarily.

A .False. Revoking from USR1 doesn’t cascade due to WITH ADMIN OPTION. B .True. Revoking from USR2 cascades to USR3 because USR2 granted it. C .False. DBA can revoke the full privilege, not just ADMIN OPTION. D .False. USR1 can’t revoke from USR3 directly; only the grantor (USR2) can. E .True. WITH ADMIN OPTION breaks the revoke chain from USR1 onward.

A .False. CREATE SESSION doesn’t grant DML rights. B .True. CREATE SESSION allows login. C .True. WITH ADMIN OPTION allows revoking from others HR granted it to. D .True. WITH ADMIN OPTION permits granting with the same option. E .False. Users can’t revoke their own privileges. F .False. DDL requires additional privileges (e.g., CREATE TABLE ).

A .True. Auditing tracks suspicious actions. B .False. That’s monitoring, not auditing. C .False. Auditing detects, doesn’t prevent. D .False. Corruption is a reliability issue. E .True. Ensures privileged user compliance.

A .False. Limited to captured privileges, not all schema objects. B .False. SYS is excluded from capture. C .True. Captures indirect role privileges used. D .True. Captures direct role privileges used. E .False. Focus is on roles, not self-owned objects broadly.

A .True. Auditing tracks privilege use, enforcing least privilege. B .True. Revoking unnecessary PL/SQL access limits capabi lities. C .True. ACLs restrict network access precisely. D .False. TRUE relaxes dictionary access, violating least privilege. E .False. TRUE allows risky OS auth, weakening security.

A .True. DUAL is public; any user with SELECT can query it. B .True. One row, one VARCHAR2 column (DUMMY). C .False. Fixed at one row. D .False. Not restricted to SYS. E .False. One row, one column only. F .False. Can return expressions, not just constant s.

A .False. Schema owners need EXECUTE on DBMS_FGA. B .False. Not limited to SYSDBA. C .True. AUDIT_ADMIN includes FGA creation rights. D .False. No such role exists. E .True. DBMS_FGA.ADD_POLICY requires this privileg e.

A .True. Captures unused self-owned object privileges. B .False. SYS is excluded from capture. C .False. No exclusion option for admin users. D .False. Direct role grants aren’t separately analyzed. E .True. Tracks indirect role privileges used. F .True. Identifies used self-owned privileges.