Rectangle 27 0

I just wish to expand on Tony's answer, which by itself is correct. I just want to show you another way, which i think would be easier on a total beginner and omits the creation of tables.

If your application uses Apex as authentication scheme, then your users are managed through the administration of the workspace itself. You can create, edit and delete users, but you can also define groups, and link users to groups. It is possible for you to create several "end user" type of users, and define a couple of groups, like "Executives".

When you have created your group, go to the user you wish to assign this group to, and add the group to the groups of that user

Once you have that set up, you still need the authorization schemes. The fact remains you need some pl/sql knowledge here, but it is possible to keep the coding to a minimum, thanks to some handy api-work. The current_user_in_group does what it says: it checks for the current user if he has said group assigned. With some expanding using some simple IF-structures, you can ramp it up a bit!

Not that i'd totally recommend this method, i find it a bit tedious myself, and you need someone to go into APEX to actually maintain users and their groups, but it could well be that this is acceptable in your environment. You could use it to start out with however. You can very easily switch out authentication schemes, and with altering your authorization schemes so they comply with the new auth scheme, you can easily and quickly adjust this afterward. It depends on your priorities and goals of course.

+1 Great, now I know how to use the built-in groups! I had looked at the apex_util docs before answering, but failed to see that function. Just an observation: your code in expression 1 can be reduced to simply; return apex_util.current_user_in_group(p_group_name=>'Executives');

I implemented your answer here Tom, and it sure is effective at restricting access. In fact, I can't get to the login page anymore. XD How might I fix that? I should note that I applied the authorization scheme at the app level, not the page level. Do I have to turn authorization off at the app level and really go through and limit access to each page individually?

@TraxusIV Wow, sorry for the late response! I think this might be tied to your apex version, and later versions of apex shouldn't do this anymore (I believe). Might be from 4.2 and up. Other than that, yes, you'll need to assign directly. Don't fret though, you can do this relatively easily by going to Utilities > select Cross-page utilities on the right sidebar > grid edit of all pages. You can change the authorization scheme there, easily, for all your pages. Don't forget to save! :)

security - Authorization and user roles in Oracle Apex? - Stack Overfl...

security permissions authorization roles oracle-apex
Rectangle 27 0

You can change the logo by going to:

application properties - user interface - logo

shared components - user interface - user interface properties - logo

You can define the type of logo there. When choosing "Image" you can specify the url to the image in the "Logo" text item. In there you can use substitution syntax to make this a bit more dynamic. Eg, I could use &APP_USER. to construct a link like this:

#IMAGE_PREFIX#custom/&APP_USER._logo.png

The follow-up to this would be: how can I get my user type in there? I'd advise to create an application item, eg AI_USER_TYPE. To make sure there is always a value in it I'd be inclined to specify a post-authentication function for your current authentication scheme (shared components - security - authentication schemes). This'll guarantee the item will be correctly filled in once the user has logged in.

thanks @Tom i'm checking it, and will approach u if found any doubts

I have created two authorization schemes' in my application. One is in general for all users & another is for users who have a particular kind of user_access_level. Is there a way we can show different logo's based on this authorization schemes? If so can u show me how can we do that. Thanks in advance..

@xyz9999 One way to try to do this is by changing the post-authentication code to check the authorization by using apex_util.public_check_authorization. If one returns true, set an application item to a certain string, and use the application item in the logo url. Another completely different way would be to change the page template and put a region in the html where the logo also is. On page 0 you can then assign a region there, and create multiple with authorization checks.

How to change the logo in oracle apex based on user logged into the ap...

oracle-apex
Rectangle 27 0

Oracle APEX has the option to use Oracle SSO for logging in, it only requires some digging until you find it (authorization schemes).

After that I was using this select statement to determine the user role:

SELECT ROLE FROM PEOPLE
WHERE E_MAIL = V('APP_USER')

single sign on - Creating different user roles in Oracle APEX - Stack ...

oracle single-sign-on oracle-apex apex user-roles
Rectangle 27 0

Although APEX has a built-in user management concept called "Groups" I must confess I have never used it, and a quick perusal of the documentation doesn't make it clear to me how you use these to control access (but see Tom's answer here for that).

You will probably need to create user/role tables within your database and use these in conjunction with APEX Authorization Schemes to control access to pages. A single Authorization Scheme of type "PL/SQL Function returning Boolean" could be created with the function body:

return my_auth_pkg.is_authorized (p_user    => :app_user,
                                  p_app_id  => :app_id
                                  p_page_id => :app_page_id);

You would then implement the package to look up the user's privileges and decide whether to return TRUE or FALSE for the application and page id.

Alternatively you could just perform the SQL to check for access directly in the Authorization Scheme:

Where exactly would the above-listed code go?

You would enter it in the "Function Body" property of the APEX Authorization Scheme when you create it.

Ah alright. So that is also where I would create the checks to see if my db table contains the proper information or not?

In a package that you create on the server. You need to know PL/SQL.

security - Authorization and user roles in Oracle Apex? - Stack Overfl...

security permissions authorization roles oracle-apex
Rectangle 27 0

Customized USER Based Content in Oracle APEX

This is how I approached the implementation of the OP and a demonstration of the results. I created two custom user accounts: "RICHARD" and "KAPLAN". After logging into the application pages of this demo, the user will find a report as well as customized text associated to their account information.

This example uses the APEX Standard User Authentication Scheme. This should also work with other custom authorization methods.

I also added a student name so that it would match the login name (identified by the reserved variable/item name: APP_USER). The user login name does not necessarily have to be the same as the student name. Other approaches can be accomplished such as by providing a suitable "translation" column inside the STUDENT table between student name, id and login name.

This page was created to demonstrate how to call session based item values and also schema/table based references.

This section is an HTML region. The page source is plain text with some HTML tags and also a substitution variable for the APP_USER value:

Hello There, &APP_USER. How are you today?<br>
 Your student ID number is: &P12_SID. <br>

 Welcome to your virtual classroom.

A REPORT content type stored in a page region. This list is a report output of information for all students stored in my sample database:

SELECT *
   FROM STUDENT;
SELECT *
   FROM STUDENT
  WHERE NAME = :APP_USER;

Elsewhere in the application, where any query requires an item value to filter by STUDENT.ID, instead refer to the defined page item: P12_SID. In this example, the "SID" (Student ID) value is passed between pages as a HIDDEN FIELD ITEM.

By assigning hidden typed page items to query operations, these assigned data values become reusable throughout a page's content. Enabling the PROTECTED FIELD setting for the hidden page item also prevents users from spoofing other student id values to gain access to another student's records.

You have now seen the STUDENT.NAME and STUDENT.ID value accessed by the application login value.

The following tables were used to develop the output for this report. These other supporting tables came from another Stack Overflow post with oddly similar structures and requirements. Could this schema be part of a class assignment?

STUDENT_ENROLLMENT: This table contains the association of multiple id values that connect a STUDENT record to a CLASS record.

CREATE TABLE  "STUDENT_ENROLLMENT"
    (   "CONFIRMATION_ID" NUMBER(10,0) NOT NULL ENABLE, 
        "STUDENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
        "CLASS_ID" NUMBER(10,0) NOT NULL ENABLE, 
        "ENROLL_DATE" DATE NOT NULL ENABLE, 
        "SEMESTER_ID" NUMBER(10,0) NOT NULL ENABLE, 
    CONSTRAINT "STUDENT_ENROLLMENT_PK" PRIMARY KEY ("CONFIRMATION_ID") ENABLE
    )
 /
 ALTER TABLE  "STUDENT_ENROLLMENT" ADD CONSTRAINT "STUDENT_ENROLLMENT_FK" 
    FOREIGN KEY ("STUDENT_ID")
REFERENCES  "STUDENT" ("ID") ENABLE
 /
 ALTER TABLE  "STUDENT_ENROLLMENT" ADD CONSTRAINT "STUDENT_ENROLLMENT_FK2" 
    FOREIGN KEY ("CLASS_ID")
REFERENCES  "CLASS" ("ID") ENABLE
 /
 ALTER TABLE  "STUDENT_ENROLLMENT" ADD CONSTRAINT "STUDENT_ENROLLMENT_FK3" 
    FOREIGN KEY ("SEMESTER_ID")
REFERENCES  "SEMESTER" ("ID") ENABLE
 /

SEMESTER_SUBJECT: This table connects a given class season-year and the subjects that are offered

CREATE TABLE  "SEMESTER_SUBJECT" 
    (   "ID" NUMBER NOT NULL ENABLE, 
        "SEMESTER_ID" NUMBER NOT NULL ENABLE, 
        "SUBJECT_ID" NUMBER NOT NULL ENABLE, 
        PRIMARY KEY ("ID") ENABLE, 
    CONSTRAINT "SEM_SUB_UQ" UNIQUE ("SEMESTER_ID", "SUBJECT_ID") ENABLE
    )
 /
 ALTER TABLE  "SEMESTER_SUBJECT" ADD FOREIGN KEY ("SEMESTER_ID")
REFERENCES  "SEMESTER" ("ID") ENABLE
 /
 ALTER TABLE  "SEMESTER_SUBJECT" ADD FOREIGN KEY ("SUBJECT_ID")
    REFERENCES  "SUBJECT" ("ID") ENABLE
 /

CLASS: This table contains information about what class (organized by subject) is offered for each semester-year period.

CREATE TABLE  "CLASS" 
    (     "ID" NUMBER NOT NULL ENABLE, 
      "NAME" VARCHAR2(40) NOT NULL ENABLE, 
      "SEMESTER_SUBJECT_ID" NUMBER NOT NULL ENABLE, 
    PRIMARY KEY ("ID") ENABLE
    )
 /
 ALTER TABLE  "CLASS" ADD FOREIGN KEY ("SEMESTER_SUBJECT_ID")  
    REFERENCES  "SEMESTER_SUBJECT" ("ID") ENABLE
 /

SEMESTER: This is a small dimensional field. The combination name of academic season and year resolve to a unique id value. To assist with the potential use of these values in List of Value queries, a SORT_ID value is included since the correct ordering of these values is not alpha-numeric.

CREATE TABLE  "SEMESTER" 
    (     "ID" NUMBER NOT NULL ENABLE, 
      "SORT_ID" NUMBER, 
      "NAME" VARCHAR2(20) NOT NULL ENABLE,
       PRIMARY KEY ("ID") ENABLE
    )
 /

The SQL Query Used to Define the Enrollment Report

select STUDENT.NAME as "STUDENT NAME",
     STUDENT_ENROLLMENT.CONFIRMATION_ID as "CONF ID",
     SEMESTER.NAME as "SEMESTER YEAR",
     SUBJECT.SUBJECT_NAME as SUBJECT,
     CLASS.NAME as "CLASS NAME",
     STUDENT_ENROLLMENT.ENROLL_DATE as "ENROLL DATE" 
  from SUBJECT SUBJECT,
     CLASS CLASS,
     SEMESTER_SUBJECT SEMESTER_SUBJECT,
     SEMESTER SEMESTER,
     STUDENT STUDENT,
     STUDENT_ENROLLMENT STUDENT_ENROLLMENT 
  where SEMESTER_SUBJECT.SEMESTER_ID = SEMESTER.ID
     and SEMESTER_SUBJECT.SUBJECT_ID = SUBJECT.ID
     and SEMESTER_SUBJECT.ID = CLASS.SEMESTER_SUBJECT_ID
     and STUDENT_ENROLLMENT.SEMESTER_ID = SEMESTER_SUBJECT.SEMESTER_ID
     and STUDENT_ENROLLMENT.CLASS_ID = CLASS.ID
     and STUDENT_ENROLLMENT.STUDENT_ID = STUDENT.ID
     and STUDENT_ENROLLMENT.STUDENT_ID = :P2_SID

NOTE: the "Student ID" parameter, P2_SID is populated by the redirect command associated with the navigation button on P12 (the demo start page). P2_SID is also a hidden, protected page item.

Use a navigation link, preferably a BUTTON type (but it doesn't matter).

  • Choose the action of: "REDIRECT" to the page in the application for the enrollment report.
  • Set the TARGET page item for STUDENT_ID to the value set for the item STUDENT_ID derived in the START page. Remember, session attribute APP_USER and page item STUDENT_ID are not the same, but their relation is defined by how the application authentication scheme is set up and also the structure of the STUDENT information table.

It isn't clear what has already been attempted to customize the user experience based on non-user-selectable values. Still, speaking of this solution in a general sense is a useful guide in APEX design and functionality.

If queries and their presentation through report pages are involved, the developer can take a session property, such as a "user name" and apply it into a report SQL query definition, either directly or through the use of "page items".

If there is a need to use these parameters elsewhere within the application, navigation elements such as "region buttons" have the ability to transfer derived parameters from one page to the next.

sql - Oracle Application Express - Forms And Queries - Stack Overflow

sql oracle oracle-apex
Rectangle 27 0

I had to double check what this ACL page was, as I was quite sure I hadn't seen it before in the administration pages of Apex. The ACL page is not a page part of the Apex Administration menu, and is user-created. It is a selectable "page type" when creating a new page through the wizard. During the creation wizard, Apex informs the developer of what it will create in order to support this page:

So, 2 tables are created and 3 authorization schemes. I just want to point out again that these tables are simply tables available to you in the parsing schema of the application, and are no apex metadata tables. Hence, you're free to "do with them what you want". Once the pages and necessary objects have been created you will have to go to the page to find out the exact name of the tables - but chances are big these will be the standard names. Look to the "Processing" part of the page logic and find the Fetch process and MRU process. Inspecting them will show you the correct names.

Thus, it becomes simply a matter of:

INSERT INTO APEX_ACCESS_CONTROL(
ADMIN_USERNAME, ADMIN_PRIVILEGES, SETUP_ID)
VALUES(
'Tom', 'ADMIN', 1
);

Whereby the setup_id is a foreign key going to APEX_ACCESS_SETUP.

You can opt to upload data by providing a sql script with inserts, or upload it through apex with the data workshop, or any other means to get the data into your database.

plsql - How can I programatically add a user to an access control list...

oracle plsql oracle-apex