## 苏南生的CSDN博客

Oracle ERP Technology

# Adding Security to your Database Application Using Oracle Application Express 5.0

#### Purpose

This tutorial shows you how to add security to your application using Oracle Application Express.

#### Time to Complete

Approximately 40 minutes.

#### Overview

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in the Oracle Cloud.

In this tutorial, you use Oracle Application Express Release 5.0 to create and run a database application.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
• Logging into Oracle Application Express in a Oracle Database Cloud Service:  Reference the Oracle Help Center for your Oracle Database Cloud Service. To do this, go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
• Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
• Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
• Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

#### What Do You Need?

Before starting this tutorial, you should have:

• Access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Installed Oracle Application Express Release 5.0 into your Oracle Database (for on-premises only).
• Download and unzipped the files.zip file into your working directory.
• Configure the database and the application environment by performing any one of the following:
• Execute the following tutorials in the specified sequence:
• Execute the following environment setup steps in the specified sequence:
• Create an Oracle Application Express user looking at the instructions in Creating New User Accounts in Oracle Application Express Administration Guide.
• Upload and run the deinstall_database_obj.sql to reset the application environment.
• Use the Project_Tasks_Appln_2.exe in your working directory to import the application. Make sure you install the supporting objects.

As mentioned earlier, this application uses Oracle Application Express Authentication. To create new users, you use the functions already available in Oracle Application Express. Application Express 5.0 allows you to create users in bulk.

You create some new users and then in the next topic you restrict access to certain areas of the application to certain people. To do this, perform the following steps:
1. From the Oracle Application Express home page, click the down arrow next to the Administration icon, and selectManage Users and Groups.

Description of this image
2. Click Create User >.

Description of this image
3. Enter Brad.Knight for Username and brad.knight@oracle.com for Email Address, and scroll down further.

Description of this image
4. Ensure the following values are provided, and click Create and Create Another.

 User is a workspace administrator No User is a developer No Password Any password of your choice. In this case, enterqweQWE123! Confirm Password qweQWE123! Require Change of Password on First Use No
Note:  While creating new users, you have a choice to provide access to Team Development. By default, developers get access to Application Builder, SQL Workshop, Websheet Development, and Team Development.

Description of this image
5. Enter Susie.Parker for Username and susie.parker@oracle.com for Email Address, and scroll down further.

Description of this image
6. Ensure the following values are provided, and click Create and Create Another.

 User is a workspace administrator No User is a developer No Password Any password of your choice. In this case, enterqweQWE123! Confirm Password qweQWE123! Require Change of Password on First Use No
Description of this image
7. Enter John.Bell for Username and john.bell@oracle.com for Email Address, and scroll down further.

Description of this image
8. Ensure the following values are provided, and click Create User.

 User is a workspace administrator No User is a developer No Password Any password of your choice. In this case, enterqweQWE123! Confirm Password qweQWE123! Require Change of Password on First Use No
Description of this image
9. The three new users are created. In the next section, you will set up access control to the application. ClickApplication Builder.

Description of this image

Now that you have users defined, you can restrict access to certain portions of the application. In this topic, you allow only certain users to edit tasks. To do this, perform the following steps:

### Add an Access Control Page

To secure the application so that only privileged users can perform certain operations, you create an Access Control Page that is used to define which users can access which part of the application. Perform the following steps:

1. Click Project Tasks Application.

Description of this image
2. Click Create Page >.

Description of this image
3. Click Access Control.

Description of this image
4. Enter for Administration Page Number, and click Next >.

Description of this image
5. Ensure Do not associate this page with a navigation menu entry  is selected for Navigation Preference, and clickNext >.

Description of this image
6. Click Create.
Note: Oracle Application Express creates two internal tables called APEX_ACCESS_SETUP andAPEX_ACCESS_CONTROL along with the Access Control Administration page.

Description of this image
7. The Access Control Administration page is created. Click Save and Run Page.

Description of this image
8. If the Log In screen appears, enter your Oracle Application Express credentials, and click Log In.

Description of this image
9. The Access Control Administration page opens. Notice that the page is divided into two regions called Application Administration and Access Control List. The default setting for the Application Mode is "Full Access to all, access control list is not used". In this tutorial, you want to restrict certain users from accessing certain features of this application.
Select Restricted access. Only users defined in the access control list are allowed for Application Mode, and click Set Application Mode.

Description of this image
10. The Application mode is set. In the next topic, you identify your privileged users. Click Add User in the Access Control List region.

Description of this image

### Identify Privileged Users

In one of the previous sections, you created 3 users: Brad.Knight, John.Bell and Susie.Parker. In this topic, you identify your application's privileged users as follows:

• Brad.Knight is allowed to edit the application but not allowed to change any user access.
• John.Bell can only view the information in the application, and he can not make any changes to the application or user access.
• Susie.Parker is the administrator of the application, and therefore she is allowed to edit the application as well as user access.

Perform the following steps:
1. Enter john.bell for Username, select View for Privilege, and click Add User.

Description of this image
2. Enter brad.knight for Username, select Edit for Privilege, and click Add User.

Description of this image
3. Enter susie.parker for Username, select Administrator for Privilege, and click Apply Changes.

Description of this image
4. Next, you can define which areas of the application are restricted. Click the Application<n> in the developer tool bar.

Description of this image

### Apply Authorization Schemes to Your Application Components

You want to create an authorization scheme, such that:

• The users with View privileges can review the Employee Information but can not change it.
• The users with Edit privileges can make changes to Employee Information but can not make changes to the access control list.
• The users with Administrator privileges can make any changes, including to the access control list.

Perform the following steps:
1. Click Edit Application Properties.

Description of this image
2. Click the Security tab.

Description of this image
3. Select access control - view for Authorization Scheme, and click Apply Changes.

Description of this image
4. Now that you have given access to the application for view privileged users, you can restrict edit privileged users to the Employee Information. Click 2 - Projects.

Description of this image
5. Under Rendering, click the small triangle icon beside Columns.

Description of this image
6. Click PROJECT_ID.

Description of this image
7. In the property editor, under Security, select access control - edit for Authorization Scheme, and click Save.

Description of this image
8. You also want the Create Button to appear only if the user has Edit or Administrator privileges. In the Rendering tab, under Region Buttons, click CREATE.

Description of this image
9. In the property editor, under Security, select access control - edit for Authorization Scheme, and click Save.

Description of this image
10. Even though you restricted the view privileged users from editing the Projects page, they can still access page 3 (Projects Master Detail page) by entering the correct URL in the browser's address bar. To prevent direct access to page 3, enter in the Page Search field, and click Go.

Description of this image
11. Make sure Page 3 is selected in the Rendering tab. In the property editor, under Security, select access control - edit for Authorization Scheme, and click Save and Run Page.

Description of this image
12. Since, previously, you logged in as a user who is not defined in the access control list, you see an error message as shown below. Click Application<n> in the developer toolbar.

Description of this image
13. Since only users with the administrator privileges are allowed to make changes to the access control list, you need to set an authorization scheme for this page. Click 3 - Access Control Administration.

Description of this image
14. In the property editor, under Security, select access control - administrator for Authorization Scheme, and clickSave.

Description of this image
15. Enter 101 in the page search field, and click Go.

Description of this image
16. Click Save and Run Page.

Description of this image

Description of this image
18. Click Manage Projects and Tasks in the Navigation Menu.

Description of this image
19. Notice that the Create button is visible on the Projects page because brad.knight is defined as an edit privileged user. Click the edit icon beside Email Integration.

Description of this image
20. Notice that brad.knight can edit the Projects. Click Log out.

Description of this image

Description of this image
22. Click Manage Projects and Tasks in the Navigation Menu.

Description of this image
23. Notice that the Create button is not visible and edit icon is not displayed beside any project in this page because john.bell is defined as a view privileged user.

Description of this image
24. Now, let us try accessing Page 3 (Projects Master Detail page) by changing the page number in the URL as explained below:

Example url …/f?p=2018:2:2101953412249296357::NO
Change to …/f?p=2018:3:2101953412249296357::NO

Press the Enter key and notice that you receive a message denying you access to the page because you restricted Page 3 to edit privileged users only. Click the Application <n> link in the Developer tool bar.

Description of this image
25. Click 101 - Login Page.

Description of this image
26. Click Save and Run Page.

Description of this image

Description of this image
28. Click Manage Projects and Tasks in the Navigation Menu.

Description of this image
29. Notice that the Create button is visible on the Projects page because susie.parker is defined as an administrator. Click the edit icon beside Email Integration.

Description of this image
30. Notice that susie.parker can edit the Projects.

Description of this image
31. Change the page number in the URL to open the Access Control Administration page as explained below:

Example url …/f?p=2018:3:2101953412249296357::NO
Change to …/f?p=2018:7:2101953412249296357::NO

Press the Enter key and notice that you can access this page because susie.parker is created with administrator privileges. Click Log Out.

Description of this image

In this tutorial, you have learned how to:

• Create Users
• Create Access Control
• Limit access to the users using Access Control
• Set access control to your application components

#### [精]Oracle APEX 5.0 新手教程(一) Form表单

2015-12-26 15:18:40

#### APEX 5.0新手教程

2016年01月29日 5.38MB 下载

#### Oracle Apex 实用笔记系列 0

2014-11-05 10:33:55

#### Oracle APEX 系列文章1：Oracle APEX, 让你秒变全栈开发的黑科技

2018-05-05 23:38:32

2013-06-09 01:37:18

#### Oracle APEX系列（一） 安装

2006-08-29 12:24:00

#### oracle APEX5.1 自动登录

2017-12-12 18:09:43

#### Oracle APEX 安装Oracle REST Data Services (ORDS) 教程

2017-06-29 14:30:35

#### Oracle APEX 系列文章6：Oracle APEX 到底适不适合企业环境？

2018-05-14 14:33:00

#### Oracle Apex 实用笔记系列 6 - 可编辑交互报告 Editable Interactive Report

2015-08-17 09:24:39