5. Sandbox

A sandbox is a dedicated schema within an Oracle database. Sandboxes may be used for:

  • Development, prototyping and testing

  • Ad hoc load of files

  • Individual data mining

  • Development of new KPIs or DQIs

  • … and much more

Sandboxes should not be used for regular ETL processes or production reporting. Sandboxes are temporary playgrounds. They should not build a parallel production system even if the lifetime of the sandbox is set to infinite.

5.1. Private Sandboxes

Each user in an Oracle Database is owner of a schema. This schema can be used as a private sandbox if the user received quotas and a permanent default tablespace for it. It is not possible to provide access privileges to other users on a private sandbox.

Only users of type HU or GU may receive quotas on their schemas. TU users are not allowed to have private sandboxes. Quotas assigned to users of type TU will be reset to 0.

Quotas can be provided at creation time of the user using procedure p_rep_oper.create_user. They can be changed later using procedure p_rep_oper.alter_user. A default quota will be provided if it is set to NULL using these procedures. Unlimited quota is provided per default to users of type HU while this default is 0 for users of type GU. The actual default value is visible in table ADM_PARAMS, the parameters are named adm_hu_def_quota for users of type HU and adm_gu_def_quota for users of type GU.

5.2. Project Sandboxes

A project sandbox is an Oracle Database schema that may be shared between users. The user who owns this schema is called the sandbox owner. It is not possible to use SQL for granting privileges on project sandbox objects to other users. Privileges have to be granted using the p_sbx_admin and p_sbx_master “grant_…” procedures.

5.2.1. Access to project sandboxes

A new database schema is created together with a project sandbox. This user (schema) is called ‘sandbox owner’ as it owns all objects of the sandbox. It is classified as user SCURTY type ‘SBX’. This schema is expired and a direct login to it is not possible. Access to the schema is provided via proxy authentication for sandbox masters and via object groups for sandbox users (see below).

There are four different organizational roles regarding project sandboxes:

  • The Sandbox requestor is a person who orders a sandbox and who is responsible for it. This person will receive notifications for example at sandbox creation or expiration and has to bear the cost for running the sandbox. The sandbox requestor may become one of the sandbox masters (read/write access).

  • The sandbox administrator (holding role R_SCURTY_SANDBOX_ADMIN) creates and administrates a sandbox and provides access rights on it.

  • Sandbox masters are persons who get read/write access to a sandbox. Sandbox masters connect to the sandbox with the sandbox schema owner using proxy user authentication. They are seen as the sandbox owner by the database but the identity behind the connection is preserved. They are allowed to create, alter or drop objects inside the sandbox. These objects are created in the sandbox owner’s schema. They may define sensitive columns inside the sandbox and they may grant the execute privilege on procedures, functions or packages to other users (see package p_sbx_master).

  • Sandbox users are persons who get read only or read/write access to the sandbox. They can not create, alter or drop objects inside the sandbox. Sandbox users connect using their own account and receive access rights through database roles. At Sandbox creation (using procedure p_sbx_admin.create_sbx) an object group with the name of the sandbox is automatically created. This object group contains all objects created in the sandbox and is used to access the objects from outside the sandbox by sandbox users. Use procedure p_sbx_admin.grant_master_access to grant master and procedure p_sbx_admin.grant_user_access to grant user access.

5.2.1.1. Sandbox master

Sandbox masters are viewed as the sandbox owner by the database regarding access privileges but they do not need to know the password of the sandbox owner. Sandbox masters connect to the sandbox with their own password using proxy authentication to the sandbox owner. The database is aware of the identity of the human user e.g. regarding application contexts and auditing but provides full access to the sandbox owner’s schema.

A human user that is granted sandbox master is implicitly configured as a proxy user to the sandbox owner. Even when proxy-logged in as sandbox master the roles and VPD rules of the HU are retained and apply completely unchanged.

An example connect string for the human user ‘WKLINGER’ connecting to sandbox schema ‘SBX_MIGR’ is wklinger[sbx_migr]/password@localhost:1521/pdb. The schema the sandbox uses and that needs to be provided when connecting as a sandbox master is visible in column SBX_OWNER in table SBX_REPO_STATUS and in view SBX_STATUS.

One or many sandbox masters may be defined. Sandbox masters have to be of type HU (human user), no other user type is allowed. Sandbox masters are allowed to create all kinds of objects except scheduler jobs inside a sandbox. To be able to create scheduler jobs a custom role has to be granted to the sandbox master using procedure p_sbx_admin.grant_custom_role.

Sensitive columns and tenant security on new objects must be configured separately. Search rules and definitions that were created outside the sandbox, e.g. using the p_rep_designer package may also cover sandbox objects, depending on the rule definition.

5.2.1.2. Sandbox user

Sandbox users may receive read only or read/write access to the sandbox objects. Sensitive column access may optionally be provided to them.

Sandbox users have to be of type HU (human user) or GU (generic user). Their access to the sandbox is provided through the object group that is automatically created with a new sandbox.

5.2.2. Restricted Sandboxes

Sandboxes may be marked as restricted (which is the default when creating a sandbox). This means that all sandbox masters will only get the intersection of access privileges held by sandbox masters on the sandbox objects. In other words, if all sandbox masters are allowed to see all objects in the sandbox and another sandbox master is added who is only allowed to see two of the objects, all sandbox masters will further only see the two objects (least privilege principle) . If a sandbox is declared as non-restricted sandbox masters can access exactly the same objects, columns and tenants from inside the sandbox schema as if they were outside the sandbox.

5.2.3. Sandbox lifetime

A sandbox has to be assigned a lifetime, but this lifetime may be set to infinite. If a lifetime expires, the sandbox will be locked and after a grace period it will dropped. The grace period (number of days) is visible in table ADM_PARAMS, parameter sbx_grace_period.

Email notifications will be sent to the sandbox requestor and the single-point-of-contact for SCURTY sandboxes (the email address is taken from parameter sbx_spoc_mail in table ADM_PARAMS) at the following events:

  • When the sandbox is ready for use

  • Before expiration (two emails, see below for the points in time)

  • After expiration

  • After the sandbox is dropped

  • After the lifetime of a locked sandbox is changed to unlock it again

The two emails before expiration will be sent a number of days before the expiration date. These numbers are visible in table ADM_PARAMS, parameter sbx_eol_warn_1 and sbx_eol_warn_2. The default is 10 and 3 days.