This page needs a newer browser to run its tools. You can still read everything here.

PL/SQL & T-SQL (Oracle and SQL Server) Module 13

Dynamic SQL and security in PL/SQL interview questions

Interview questions on dynamic SQL and security in PL/SQL: warm-ups, then what each harder question tests and how a good answer starts.

7 questions: 2 warm-ups and 5 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

Why is a bind variable safer than a value concatenated into dynamic SQL?

Answered in the lesson Dynamic SQL in PL/SQL without injection
Mid

What changes when a procedure is created AUTHID CURRENT_USER?

Answered in the lesson Definer's rights, invoker's rights and roles

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A report procedure takes p_sort_col from a web page and runs 'SELECT ... FROM accounts ORDER BY ' || p_sort_col. A teammate proposes wrapping p_sort_col in DBMS_ASSERT.SIMPLE_SQL_NAME. Is that enough, and how would you write the procedure?
What it tests

Whether the candidate knows a column name cannot be bound, puts an allowlist in code before any DBMS_ASSERT call, quotes the mapped name with the right case, and keeps the sort direction out of the text.

First step

It is not enough: SIMPLE_SQL_NAME checks the form of a name, not whether it is a column the report may sort by. Map the caller's key to a column name the code holds, in a CASE or a lookup, and refuse any other key before building any text.
Follow-ups
- Why can the column not be passed in USING like the other values?
- What does ENQUOTE_NAME add once the name comes from your own allowlist?
- How do you take the sort direction without accepting ASC or DESC as text?
- How would you test the procedure?
From the lesson Dynamic SQL in PL/SQL without injection
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

A search procedure takes four optional filters, city, kind, minimum balance and maximum balance, and concatenates the ones that are not NULL into a WHERE clause. How would you rewrite it so that every value is bound, and which PL/SQL tool fits?
What it tests

Whether the candidate keeps values out of the text when the shape varies, knows a USING list is fixed at compile time, and can use DBMS_SQL with BIND_VARIABLE and TO_REFCURSOR or weigh a static alternative.

First step

Keep building the WHERE clause, but append only fixed text with one placeholder per filter, such as AND c.city = :city. The number of placeholders now varies with the call, so bind them by name with DBMS_SQL, or keep one fixed statement with a NULL test for each filter.
Follow-ups
- Why can EXECUTE IMMEDIATE or OPEN FOR not bind a varying number of placeholders?
- How does the caller receive the rows from the DBMS_SQL version?
- What does a static statement with a NULL test per filter cost?
- How many different statement texts can four optional filters produce?
From the lesson Dynamic SQL in PL/SQL without injection
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A utility procedure in schema TOOLS is created AUTHID CURRENT_USER and selects from a table named settings. It works when TOOLS runs it but fails with ORA-00942 for every other user who holds EXECUTE on it. Explain why, and give two fixes, saying when each is right.
What it tests

Whether the candidate knows how invoker's rights resolve names and check privileges, reads ORA-00942 as either a missing table or a missing privilege, and can choose between a qualified name with grants and definer's rights.

First step

Under AUTHID CURRENT_USER the unqualified name settings resolves in each caller's own schema, where no such table exists, so every caller except TOOLS gets ORA-00942. Decide first whether callers should share TOOLS's table or each use their own.
Follow-ups
- Why does qualifying the name as tools.settings still fail for the callers?
- When is switching the procedure to AUTHID DEFINER the better fix?
- What does Oracle check before an invoker's-rights unit runs at all?
From the lesson Definer's rights, invoker's rights and roles
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to staff

A definer's-rights package compiles in development, where its owner holds SELECT on a table directly. In test, the same privilege was granted to the owner through a role, and the package body does not compile. A colleague then rewrites the query as dynamic SQL, which compiles but fails at run time. Explain both failures and the right fix.
What it tests

Whether the candidate knows that roles do not count for static SQL at compile time or inside definer's-rights units, and can choose between a direct grant and a role granted to the package.

First step

Both failures have one cause: privileges that arrive through a role do not count for a definer's-rights unit. The static query is checked at compile time without roles, and the dynamic one at run time with only PUBLIC enabled.
Follow-ups
- Why did development and test behave differently with the same code?
- When is GRANT role TO PACKAGE the right fix, and when is it not enough?
- What conditions must hold for a role to be granted to a package?
From the lesson Definer's rights, invoker's rights and roles
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Design the database accounts for a small banking application on the practice tables. The application server must read balances and make deposits, analysts must query every table, and developers must deploy code. Who owns what, and what does each account hold?
What it tests

Whether the candidate separates data, API and application schemas, grants EXECUTE instead of table privileges, uses direct grants for the API owner, a schema privilege for analysts and DB_DEVELOPER_ROLE for developers.

First step

Separate ownership from use: one schema owns the tables, one owns a definer's-rights package that is the only way to change them, and the application's account holds CREATE SESSION and EXECUTE on that package, nothing else.
Follow-ups
- Why must the grants from the data schema to the API schema be direct?
- How do analysts read every table, including future ones, without SELECT ANY TABLE?
- How would you check the layout has not drifted after a year of changes?
From the lesson Definer's rights, invoker's rights and roles
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track

Warm-up questions

Why is a bind variable safer than a value concatenated into dynamic SQL?

What changes when a procedure is created AUTHID CURRENT_USER?

Questions with model answers

A report procedure takes p_sort_col from a web page and runs 'SELECT ... FROM accounts ORDER BY ' || p_sort_col. A teammate proposes wrapping p_sort_col in DBMS_ASSERT.SIMPLE_SQL_NAME. Is that enough, and how would you write the procedure?

A search procedure takes four optional filters, city, kind, minimum balance and maximum balance, and concatenates the ones that are not NULL into a WHERE clause. How would you rewrite it so that every value is bound, and which PL/SQL tool fits?

A utility procedure in schema TOOLS is created AUTHID CURRENT_USER and selects from a table named settings. It works when TOOLS runs it but fails with ORA-00942 for every other user who holds EXECUTE on it. Explain why, and give two fixes, saying when each is right.

Design the database accounts for a small banking application on the practice tables. The application server must read balances and make deposits, analysts must query every table, and developers must deploy code. Who owns what, and what does each account hold?