Beejartha

Categories
Oracle APEX

Oracle APEX QR Code Generator

Hello readers. I see you are interested in learning about the QR Code generation using Oracle APEX. Great news, the APEX team has your back! The team has added a QR Code item component to the APEX development toolset. Please allow me to briefly examine the QR (Quick Response) Code and then move on to discuss the QR Code component basics and then you can use your imagination to build the QR Code you are looking for in your APEX application.

What is a QR Code?

A Quick Response (QR) Code serves as a means to instantly retrieve information. Originally, the code was developed in 1994 by a Japanese company to label vehicle parts. The QR Code is a two dimensional barcode that keeps data in a square grid of black and white pixels. When the square barcode is scanned, the arrangement of pixels is handled to quickly direct to the information. QR Codes today have evolved and are becoming more popular. They can store a variety of data, such as web URLs, social media links, product data, and contact details.

What is a QR Code?

In APEX 23.1, a QR Code page item component was introduced.

What is a QR Code?

Select the type of content that this QR code contains, and a corresponding prefix value is inserted in front of the selected source value to affect how the user QR Code scanner will interact with the content. Available options include:

Plain Text

URL:- The URL will open in a browser.

Phone:- Open the phone app with the input number. The phone number can contain special characters ‘(‘ , ‘)’, ‘+’, ‘-‘, ‘.’, and spaces. The exact format of the phone number delegates to the phone’s locale settings.

Email:- Compose a new email to an email address. The expected format of the email address is a valid email address without leading or trailing spaces.

SMS:- Compose a text message to a number. The phone number can contain special characters ‘(‘ , ‘)’, ‘+’, ‘-‘, ‘.’, and spaces. The exact format of the phone number delegates to the phone’s locale settings.

Geo location:- Open a map application to specific coordinates. The expected format of the input coordinates is: latitude, longitude with no leading or trailing spaces.

Add a QR Code page item

There are several ways to add a page item, however, make sure TYPE IS QR CODE. Select the TYPE from the drop-down list, if necessary.

Next, select the QR Code DATA TYPE option from the dropdown list, if necessary. Lastly, select the Source TYPE option from the drop-down list, if necessary. This option type needs to contain
information that is associated with the QR Code DATA TYPE selected.

NOW RUN PAGE!

Yes, the Oracle APEX team has made it that easy!

Categories
Oracle APEX

Oracle APEX Workspace System Activities Application

Application Overview

I created an application to allow internal company developers and workspace administrators to monitor all applications built in a workspace and maintain users on security-based roles, if enabled for an application. Workspace administrators can also manage workspace users.
Unfortunately, I cannot provide a demonstration of the application since it is about all applications in a workspace that is protected information. However, I can tell you how to get started with some built-in Oracle APEX features and of course, my own creativity of enhancements and improvements to make this a better system application, especially for a productional workspace. If you are interested in my version of the System Activities Administration Application, please feel free to contact us for a private demonstration and we will be happy to provide you our working coping of the application and assist in the implementation in your own workspace for a nominal fee if you like. See Home Page for a screenshot of the
main page.

Getting started with Oracle APEX features

In the Oracle APEX App Builder, click the Create link icon to enable
the app wizard. On the next page enter an application name, such as System Activities Administration. Then click the link Use Create App Wizard. See screenshot below.

The next screenshot II below is the App Wizard. Make sure the application Name is correct and check the 5 features,

  1. Install Progressive Web App,
  2. Access Control
  3. Feedback
  4. Push Notifications
  5. Activity Reporting.

Next click the green Create Application button.

Wait a few seconds and the new application is created.
Now before you run the application let’s make an important change that will allow you to see all the added features after logging into the application.

1) Edit the application in the App Builder
a. Locate the Home page.
i. Edit page to delete page by clicking on the Utilities icon and Delete Page from the pop-up menu.
b. Locate the Administration page (page 10000).
i. Edit the page Identification section properties as shown below. *Make sure the page Alias is home

Now you are ready to run the application and look around! Notice that this does NOT currently serve as a System utility but as an application utility. My Enhancements and Improvements section will convert this to a System utility.

Enhancements and Improvements
  1. Edit application in App Builder
  2. In shared components, edit the Breadcrumb bar, Administration page #10000 change the short name to System Administration. Click Apply Changes.
  3. In shared components, create a list of values.
    • Name – WORKSPACE APPLICATION
    • Database source – Local
    • Source type – SQL query
    • SQL query – select DISTINCT APPLICATION_ID, APPLICATION_NAME from APEX_WORKSPACE_ACTIVITY_LOG L
    • Return – APPLICATION_ID
    • Display – APPLICATION_NAME
    • Default sort – APPLICATION_NAME
  4. Add a select list page item to the follow pages:
    • Page #10000 see Home screenshot below for location.
      • Name – <PAGE#>_APP_ID
      • List of Values section Type – Shared Component
      • List of Values section List of Values – WORKSPACE APPLICATION
      • IF page has a timeframe: Locate existing Change Filters dynamic action and include the new <PAGE#>_APP_ID in item(s) under the When section ELSE Create a Change Filters dynamic action and add the new <PAGE#>_APP_ID in item(s) under the When section; and add true refresh action for all affected regions or types.
      • Add dynamic actions to refresh regions when a dialog is closed that affects certain home page regions. Note: Only regions on the left side are affected
    • Page #10010 – Location next to timeframe.
    • Page #10011 – Location next to timeframe
    • Page #10012 – Location next to timeframe.
    • Page #10013 – Location next to timeframe
    • Page #10014 – Location next to timeframe.
    • Page #10022 – hidden. Make sure to refresh the roles after the existing APPLICATION_ID by converting it to a select list and changing the behavior property to dynamic action that will refresh roles for selected application.
    • Page #10033 – Location top of page.

As you can see from the Home page a couple more interactive report informational pages. I also added a classic report region for a list of ACL users and a button to Manage APEX End users along with a few modal window pages for added administrative functionality. Since the vast majority of pages are modal and all pages are referenced from the Home page, I did not see the need for a navigation menu and removed it from the application.

Home Page afterwards

Password Tip

If end users of APEX applications are not prompted to change their passwords on their first login
to the system and user accounts are using native APEX Authentication, and when logging in and
authenticating, APEX fails to prompt them to change their passwords even if the individual user
account property “Require Change of Password on First Use” is set to “Yes.”
Check to verify the “Account Expiration and Locking” property at either the Instance or
Workspace level is not enabled. When the “Account Expiration and Locking” property at either
the Instance or Workspace is not enabled, password expiration or “Require Change of Password
on First Use” policies are not enforced.
In order to enforce the “Require Change of Password on First Use” property on APEX
Authenticated users, the “Account Expiration and Locking” property must be set to “Enabled”
either at the APEX Instance level, or in your individual workspaces. If set to “Enabled” at the
Instance level, it cannot be overridden at the workspace level. If set to “Disabled” at the Instance
level, Workspace Administrators are free to choose whether to enable it in their individually
managed workspaces.

Check APEX Instance Administration

Manage Instance -> Security -> Workspace Login Control -> Require User Account Expiration
and Locking

Check APEX Workspace Administration

Administration -> Manage Service -> Set Workspace Preferences -> Account Login Control ->
Account Expiration and Locking

Information from Oracle Support

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=112397227969565&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1946117.1&_afrWindowMode=0&_adf.ctrl-state=9led0qaaw_4

Categories
Oracle APEX

Oracle APEX Common Platform Types

If you are like me when I first got involved with Oracle Application Express (APEX) you did some research reading blog post and watching YouTube videos. However, many posts and videos, while correct, can cause many readers/watchers frustration and confusion the screens and or steps are not available to them. The question is WHY? Please allow me to elaborate on this topic based on my observations of the most common three different platform types.

Before I jump into platform types, let’s quickly mention APEX developed by Oracle. Oracle APEX itself is easy to learn for many with a technical web development aptitude as it uses standard web technologies such as CSS, JavaScript, jQuery, and SQL. Oracle APEX was developed by Oracle as a web IDE to develop web customized applications and is free to install on an Oracle database therefore PL/SQL can be referenced. Only the version changes are slightly different since they are frequent. Oracle makes changes and improvements to APEX every six months (twice a year). Version information being used can be found in the APEX workspace IDE that I will explain later in this document. If the article/video that you are using does not specify you might be able to make an educated guess if you can find the date posted.

Now let’s discuss the three most common platforms and differences that I have personally experienced. Various kinds of APEX platforms do have varying options that can affect availability for certain components, for example, you do not have Administrative Services (ADMIN) on the fully autonomous Oracle APEX on the cloud (apex.oracle.com) free subscription, however, a few of the ADMIN features are available in the workspace to a workspace administrator user.

Three common platform types

Fully autonomous Oracle APEX on the cloud (apex.oracle.com) free subscription.

  • Oracle APEX controls the database and APEX versions and is fully responsible for updating. Once an upgrade becomes available, there is a 45-day window during which you can apply for the upgrade at the day and time you choose. If you take no action, Oracle applies the upgrade automatically. For paid service instances, you can elect to get a longer 90-day time window instead of 45 days. NO SYSDBA.

Autonomous Oracle APEX on the Oracle Cloud Infrastructure (OCI) free subscription.

  • Oracle OCI controls the APEX version, and database patches and is responsible for updating. By default, Autonomous Database applies Oracle APEX upgrades as soon as they are released in your region. You can set an option to defer upgrades for major Oracle APEX releases, such as an upgrade from 22.1 to 23.1, for up to 90 days. Note: The latest Oracle APEX Patch Set Bundles are automatically applied on Autonomous Database and cannot be deferred.
    The organizational administrator is responsible for installing the Oracle OCI database supported versions as well as the OCI operating system (OS) and other OCI options. NO SYSDBA.

On-Premises Oracle APEX.

  • The organizational administrator is responsible for installing and updating both the Oracle database, APEX version, and patches.
Fully autonomous Oracle APEX on the cloud (apex.oracle.com) free subscription.

Please pay special attention to the URL as seen in the screenshot below. This reveals that the platform is apex.oracle.com. Note the links directly below the Sign In button. Also notice the APEX Upgrade Notification message below the Oracle APEX sign in section region. These things can differ based on platform type and configuration.

Autonomous Oracle APEX on the Oracle Cloud Infrastructure (OCI) free subscription.

Please pay special attention to the URL as seen in the screenshot below. This reveals that the platform is OCI. Note the links directly below the Sign In button. Specifically, the Administrative Services link at the bottom of the workspace sign-in page. These things can differ based on platform type and configuration.

The Administration Services link means Admin privileges are available for this Oracle APEX database instance. Only the ADMIN username can login and ADMIN user password are required.

On-Premises Oracle APEX.

The platform is installed on the local Oracle database and by default the Administration Services link is present. SYSDBA is available since the organizational database administrator has full responsibility.

Specific Oracle APEX Information

Regardless of the platform, the APEX IDE seems to be consistent with moderate differences depending on the user type or APEX version. To find out specific details about Oracle APEX, Sign In the workspace. Click the ? icon in the top right corner of the APEX IDE. On the pop-up menu, click About.

Oracle APEX IDE Workspace View/Manage Information

Regardless of the platform, the APEX IDE seems to be consistent with moderate differences depending on the user type or APEX version. Only a workspace administrator user type can view/manage workspace details, Sign In the workspace. Click the Admin icon (person with wrench to the right of the search bar) in the top right corner of the APEX IDE. A pop-up menu appears with some options to choose from. Be aware these tasks are specific to ONLY this workspace.

Categories
Oracle Cloud Infrastructure

Oracle Cloud OCI Identity and Access Management (IAM) Security

Oracle Cloud OCI Identity and Access Management(IAM) Security

This blog refers to the Oracle Cloud Infrastructure (OCI) console to implement users, groups, policies, and other components basic security needs. For large organizations or data center Oracle suggests that the security be addressed and maintained by a Security Analyst or Solutions Architect role. As you will soon see as you explore OCI that there are many ways to add and manage components. See Documentation to Use for Cloud Identity for detailed information from Oracle.

Order of Component Creation

Compartments, users, groups, and policies can be created in a variety of orders. However, the security components do depend on each other. For instance, all users, groups, and policies must be created in a compartment. Therefore, if the compartment does not exist then it must be created. When you create other components, you must select a compartment. Do this by clicking on compartment dropdown located mid-way down on the left side of the page and then clicking the compartment name from the list. Once the compartment exists, then a group needs to be created, unless it already exists. Policies are dependent on a compartment and a group. Once the group exists, then a new policy may need to be created, unless it already exists and attach the policy to corresponding group. Lastly, a user is dependent on a compartment and must be assigned to a group with an attached policy.

Oracle Cloud (OCI) Login

Login to the Oracle Cloud Infrastructure OCI web console using your credentials at https://www.oracle.com/cloud/sign-in.html
If you do not know the account name, please contact the organization’s OCI administrator.

Create Multifactor Authentication.
  • Click your Profile on the top right of the page.
  • Select your Username. Or Alternately
  • Click User settings link.

  • Click Enable Multi-Factor Authorization from the user page.
  • Follow the instructions from the pop-up.


When the user signs in they will be prompted for a passcode before logging in.

Create, List or Manage Compartment
  • All users, groups and policies and components are created in compartments. Compartments can have sub-compartments and can be moved and reorganized. Please make sure you are creating the components in the correct compartment. Please be aware that a root tenancy compartment is created and should be used to create OCI users, such as OCI Administrator and Security Analyst or Solutions Architect role using the default root tenancy compartment which already exists.
  • Click the OCI menu located on the top left corner of the page.
  • Click Identity & Security -> Compartments under the Identity section (you can select from the list to manage a compartment (including the root default tenancy) or create a new compartment). Please make sure you are in the correct compartment.

  • Click the Create Compartment button.
    • Type name
    • Type description
    • Select group compartment from the dropdown list (if creating a child compartment).
  • Click Create.
Create, List or Manage User

Please make sure you are in the correct compartment.

  • Click the OCI menu located on the top left corner of the page.
  • Click Identity & Security -> Domains under the Identity section –> Click Default or current domain list box

  • Click the Create User button.
    • Type user’s First Name and Last Name
    • o Select the check box Use the email address as the username to have the user sign in with their email address. Unselect the check box Use the email address as the username to have the user sign in with username and then type in the username.
      • The following characters are allowed:
      • a-z
      • A-Z
      • 0-9
      • Special characters ! @ # $ % ^ & * ( ) _ + = – { } [ ] | \ : ” ‘ ; < > ? / . ,
      • Blank spaces
  • Under Select groups to assign this user to, select the check box for groups.
  • Click Create.

A welcome email is sent to the address you entered for the new user. The new user can follow the account activation instructions in the email to sign in and start using the tenancy.

Reset Password
  • Click the OCI menu located on the top left corner of the page.
  • Click Identity & Security -> Domains under the Identity section –> Click Default or current domain list box

  • Select the Username link from the list.
  • Click Create/Reset Password button.
  • Click again Create/Reset Password button. This will create a new one-time password.
  • Click the Copy link for the auto-generated password from the pop-up.

When the user logs in they will be prompted to change the password. The new password must meet the password requirements.

Create, List or Manage Group

Please make sure you are in the correct compartment.

  • Click the OCI menu located on the top left corner of the page.
  • Click Identity & Security -> Domains under the Identity section –> Click Default or current domain list box

  • Click the Create Group button.
    • Type Name (can’t be modified after it has been created. It is used in the policy syntax).
    • Type Description.
    • Click the Create button.
Create, List or Manage Policy
  • Click the OCI menu located on the top left corner of the page.
  • Click Identity & Security -> Policies under the Identity section (you can select from the list to manage/view an existing policy or create a new policy). Please make sure you are in the correct compartment.

  • Click the Create Policy button. There is a default Policy Builder with predefined templets or click customize (Advanced) if you already have the policy.
    • Type policy statements in the text box (if you selected customize).
    • Click the Create button.

See Common Organization Policies for details about specific policies.
The location is required in the statement. If you want to attach a policy to a compartment, you must be in that compartment when you create the policy. For more information, see Policy Attachment.

Permission Level

The permission level is specified by the verb in the statement. To give another user access to interact with your environments in the Oracle Cloud Console, use one of the following verbs in your policy statements:

  • manage – allows the user to perform all management tasks for an environment, including create and delete (when supported).
  • use – allows the user to update an existing environment; user can’t create or delete an environment.
  • read – allows the user to view all information about the environment.
  • inspect – allows the user to list the environments only; user can’t view the details pages.

For more information on the operations permitted by each of these verbs, see Policy Syntax.

OCI tenancy Read-only Policy

Allow group to read app-listing-environments in tenancy
Allow group to read organizations-subscriptions in tenancy
Allow group to read organizations-assigned-subscriptions in tenancy

Policy Inheritance

See Policies and Compartment Hierarchies. To view information about specifying a compartment that is not a direct child of the compartment you are attaching the policy. To specify the path to the sub-compartment, use the colon (:) as a separator.

Examples

Add an Administrator

Please make sure you are in the root tenancy compartment
The Administrator group already exist.
Create or manage a user and select the new group.

Add a Read-Only tenancy User

Please make sure you are in the root tenancy compartment
Create a new group. Name the group GRP_Read_Tenancy
Create a new policy and attach it to the group as below:

  • Allow group GRP_Read_Tenancy to read app-listing-environments in tenancy
  • Allow group GRP_Read_Tenancy to read organizations-subscriptions in tenancy
  • Allow group GRP_Read_Tenancy to read organizations-assigned-subscriptions in tenancy

Create or manage a user and select the new group.