The setup script has the capability to define a wide range of database-level objects. Notably, the database objects created by the setup script are internal to the application, remaining invisible and inaccessible to the consumer account directly.
To grant visibility and access to these objects for consumers, application roles come into play. Any application role established within the setup script is automatically bestowed upon the role that owns the instance of the installed application. It’s crucial to note that these roles cannot be revoked.
Users possessing the role that owns the application instance can then extend these application roles to other roles within their organization. For instance, the setup script may define an application role, such as APP_ADMIN, which is granted permission to access objects within the application. Subsequently, users with this role can leverage these objects for administering the application.
Additionally, the setup script can introduce an application role (e.g., USER) to provide consumers with access to the application’s functionality. Furthermore, it can define roles like READ_ONLY, offering restricted access to specific data areas within the application.
The setup script serves a dual purpose, catering to both the initial installation and subsequent upgrades. It is imperative for the script to be composed in an idempotent manner. For instance, object creation can utilize statements such as CREATE .. IF NOT EXISTS or CREATE OR REPLACE.