50 Oracle Database Security Administration Practice Questions: Question Bank 2025

A security administrator wants to reduce risk by ensuring that powerful administrative accounts cannot access application data in a production database unless explicitly granted. Which Oracle capability best supports this separation of duties?

You must encrypt sensitive table columns (for example, national IDs) so that the application can continue using SQL without changes other than DDL. The encrypted values must be stored encrypted on disk. Which feature best meets this requirement?

An auditor asks for evidence of who changed database privileges over the last month. You need a built-in auditing approach that captures privilege and role changes with minimal custom code. What is the best solution?

A developer is granted privileges through a role. They attempt to create a stored procedure that performs a SELECT on a protected table, but compilation fails with insufficient privileges. What is the most likely cause?

A company uses Transparent Data Encryption (TDE) and wants to ensure that encryption keys are not solely stored and managed on the database host. Which approach best meets this requirement?

You are tasked with limiting DBAs from selecting rows in a highly sensitive table while still allowing them to perform database maintenance (backups, patching, performance tuning). Which solution best enforces this preventive control?

A compliance requirement states: "Record access to rows where SALARY exceeds a threshold and include the SQL text and the user context." Which feature is most appropriate?

An application schema owner currently has many direct object privileges granted to different users. You need a best-practice approach to simplify privilege management and support least privilege as the application grows. What should you do?

After enabling TDE and encrypting a tablespace, the database restarts and the application fails with errors indicating it cannot access encrypted data. No changes were made to application credentials. What is the most likely cause and the correct fix?

Your organization requires an audit trail that is difficult for privileged users to tamper with on the same host. You also need to centralize audit records for multiple databases. Which design best satisfies this requirement?

You want to reduce risk by minimizing powerful privileges in the database. Which approach is recommended to avoid granting SYSDBA to application administrators who only need to manage users and objects in one application schema?

A security administrator needs to ensure that sensitive columns are encrypted at rest without changing application SQL statements. Which feature best meets this requirement?

An auditor requests a report of who connected to the database and when, including failed login attempts, to support an investigation. Which auditing approach most directly provides this information?

A company wants to protect sensitive data from privileged users, including DBAs, by enforcing separation of duties between database administration and data access. Which solution best addresses this requirement?

After enabling TDE, the database starts but an application reports ORA-28365 errors when accessing encrypted columns. The wallet location is correct, but the issue occurs after server reboot. What is the most likely cause?

A security team wants to record SELECT statements executed against a specific table only when a particular sensitive column is referenced, and capture the SQL text and client identifier. Which feature should be used?

You are designing access controls so that a role is enabled only when a user connects from an approved application server and sets a specific application context attribute. Which is the best Oracle feature combination?

A compliance requirement states that security administrators must be able to define audit policies, but must not have the ability to alter application data. Which design best supports this separation of duties?

You need to ensure that a subset of columns (for example, national IDs) remain unreadable to DBAs and also remain protected when data is exported or copied to nonproduction. Applications must still search on those values without revealing them. Which solution best fits this requirement?

Your organization uses a centralized key management system and requires that database encryption keys be rotated regularly without exposing key material to DBAs. Which architecture best meets this requirement for TDE-protected data?

You want to centralize and standardize security-related initialization parameters across many databases while still allowing limited local overrides for non-security settings. Which approach is recommended?

A security administrator wants to ensure that database user accounts are locked automatically after repeated failed logins, and that password complexity rules are enforced consistently. What is the best way to implement this?

Your organization requires encryption of backup sets written to disk, with minimal changes to existing backup scripts. Which RMAN-based solution best meets this requirement?

A finance application uses definer-rights PL/SQL packages. Auditors want to know which end users caused sensitive table changes when the package executes with elevated privileges. What is the best solution to capture the real end-user identity in the audit trail?

You encrypted several tablespaces with TDE. After a planned restart, the database mounts but cannot open and reports that it cannot access the encryption key. Which is the most likely cause?

You are tasked with ensuring that highly privileged users cannot select from specific HR tables unless they are using a controlled maintenance procedure, while still allowing them to perform other administrative actions. Which feature best addresses this requirement?

A compliance team asks you to reduce audit noise while ensuring that any changes to audit configuration are always recorded. Which approach is most appropriate?

An application uses a shared schema and requires that rows are visible only to the owning tenant. The security team wants this enforced inside the database so that ad-hoc queries cannot bypass it. Which solution provides row-level isolation with minimal application changes?

A company uses TDE and wants to rotate encryption keys regularly without re-encrypting all data files each time. Which key-management approach supports this goal?

A privileged user claims they did not issue a particular sensitive UPDATE statement, but you suspect the statement was executed through a proxy connection by a middle-tier. You need proof of both the database user and the real client identity responsible. Which audit design best supports non-repudiation in this scenario?

A security administrator wants to reduce the risk of data exfiltration by preventing users from creating database links to unapproved remote databases. Which approach best meets this requirement with minimal impact on application code?

You are implementing Transparent Data Encryption (TDE) and want to ensure that database files remain unreadable if they are copied off the server. Which requirement must be satisfied for TDE-protected tablespaces to be opened after an instance restart?

A database must record who changed security-related settings such as system privileges and user accounts, including the SQL text and the exact time. Which auditing feature is best suited for this requirement?

A team wants to enforce the principle of least privilege for a helpdesk role that only needs to reset passwords and unlock user accounts. Which is the best approach?

A company uses TDE and must rotate encryption keys regularly. They want new data to use a new master key, while existing encrypted data should remain accessible without immediate re-encryption. Which statement best describes how TDE key rotation works?

A compliance requirement states: 'Audit only SELECT statements that access rows in the PAYROLL table where SALARY is greater than 200000, and include the client identifier in the audit record.' Which feature best meets this requirement?

An application uses definer's rights PL/SQL procedures to access sensitive tables. Security reviewers want to ensure end users cannot bypass the API and query the tables directly, while still allowing the procedure to function. What is the recommended design?

A security team needs to ensure that audit records cannot be altered by DBAs and must be retained in a separate repository for reporting. Which approach is most appropriate?

A database uses TDE tablespace encryption. After a planned restart, the database opens but queries against encrypted objects fail with errors indicating that the master key cannot be found. The security team confirms the wallet file exists on disk. What is the most likely cause?

A financial system must prevent even privileged users from reading full credit card numbers, but the application must be able to display only the last 4 digits. The solution must also work for ad-hoc queries by analysts without changing application code. Which solution is most appropriate?

You must quickly verify which database initialization parameters directly impact authentication and authorization behavior (for example, login case sensitivity and remote OS authentication). Which data dictionary view is the best starting point to query parameter names and values?

A security administrator wants to ensure that the data in an encrypted column cannot be decrypted by exporting the table and importing it into another database. Which approach best enforces this requirement?

You enable Unified Auditing and need to capture only unsuccessful logon attempts for compliance reporting while minimizing audit volume. Which is the most appropriate method?

A developer is granted SELECT on a sensitive table through a role. A stored procedure owned by that developer fails with ORA-00942 when it selects from the table, even though the developer can select from the table in an interactive session. What is the correct fix consistent with Oracle security behavior?

A security audit found that several applications connect using shared database accounts, making accountability difficult. You must improve accountability without changing application code to use individual database users. Which solution best meets the requirement?

You need to enforce that certain users can query a table but only see rows for their business unit. The policy must be transparent to applications and centrally managed, and it must apply to any SQL that references the table. Which feature is most appropriate?

After enabling TDE, an application intermittently fails on startup with errors indicating the wallet/keystore cannot be opened. The database is configured to start automatically after server reboot. Which is the most likely root cause and best practice remediation?

You need to meet a requirement that security administrators must be able to prove audit records have not been tampered with and that audit access is strictly controlled. Which design choice most directly supports this requirement?

A user complains they can still read sensitive values from a column after you applied Data Redaction. The redaction policy uses an expression checking SYS_CONTEXT('USERENV','SESSION_USER') and works for most queries. The user is accessing the data through a definer’s rights view owned by a privileged schema. What explains this behavior and what is the correct fix?

Your organization must centralize encryption key management for multiple databases and ensure that database administrators cannot extract key material, while still allowing automated key rotation and auditing of key usage. Which architecture best aligns with this requirement?