Create Database   «Prev  Next»

Replace the Legacy SQL*Plus Product User Profile - Exercise

Objective: Design database-enforced read-only access instead of relying on the desupported Product User Profile.

Oracle desupported PRODUCT_USER_PROFILE beginning with 19c. Do not run PUPBLD.SQL for this Oracle AI Database 26ai exercise. Use a written least-privilege design for an existing lab PDB, existing application table COIN_APP.ORDERS, and existing ordinary account COIN_READER. Assume the account owns no application tables and the role name is unused.

Instructions

  1. Write statements to create a local role COIN_REPORTING, grant it SELECT on COIN_APP.ORDERS, and grant the role plus CREATE SESSION to COIN_READER. State who is authorized to issue the grants.
  2. Explain why omitting INSERT/UPDATE/DELETE grants is not sufficient if the user already holds those privileges through another role, PUBLIC or direct grants.
  3. Describe positive and negative tests using the ordinary account in the PDB, and explain why SQL*Plus client restrictions cannot substitute for database privileges. Submit SQL and a test plan, with no credentials.

Self-assessment: 5 points

Compare your response with the model solution. This page does not automatically grade free-text answers.

  1. Acknowledges PUP desupport and avoids PUPBLD (1 point).
  2. Correct local role/object privilege statements (1 point).
  3. Account connection grant and grant authority (1 point).
  4. Existing privilege/role audit (1 point).
  5. Ordinary-user positive/negative tests and client distinction (1 point).