Dozens of client applications send the same multi-step order-creation SQL as ad hoc text, some by concatenating user input into the statements. The DBA wants one reusable implementation, lower injection risk, and the ability to grant execute rights without table rights. Which solution fits?
Select an answer to reveal the explanation.
Short Explanation
A stored procedure is like a service window: callers pass their order through the slot as parameters, and they never get keys to the kitchen. You get one copy of the logic, safer inputs and execute-only permissions in one move. The trap is the view, which can show data but can't create an order.
Full Explanation
A stored procedure is a named routine stored in the database that accepts parameters and can run multiple statements, including inserts and updates, as one unit. Moving the order-creation logic into a procedure gives every application a single implementation to call, so fixes and changes happen in one place. Passing user values as typed parameters rather than concatenating them into SQL text prevents those values from being interpreted as code, which removes the injection path created by string building. Granting EXECUTE on the procedure while withholding direct INSERT and UPDATE rights on the tables limits applications to the approved operation. Engines can also reuse the compiled plan across calls. A view is a stored query used for reading; granting SELECT on it cannot create orders and does nothing about the existing write path. A shared client library still sends SQL text from each application, still requires table permissions, and can drift as copies diverge across repositories. A trigger fires after a client inserts a row, so clients would still need INSERT rights on the table and could still send concatenated SQL. Exam caveat: a procedure that builds dynamic SQL from its own parameters by concatenation reintroduces injection, so parameters must stay parameters inside it as well. Operational check: revoke table rights from a test application account, grant EXECUTE only, and confirm orders can be created through the procedure but not by direct INSERT.