10 Replies Latest reply on Aug 20, 2008 5:27 PM by 655746

    Authorization  -- Roles

      Hi All,

      We are moving our applicaiton from Oracle Forms to Apex. I am basically a forms developer and I didnt understand the authorization/roles in Apex.

      For eg in our database we have 2 roles app_lookup ( privs - insert,update, delete, select) and app_guest( privs select) . And we use the database authentication for forms. If we have 2 end users Super with role app_lookup and operator with role app_guest, and if I want to implement the database role, how can it be done in Apex.

      End user Super ( with all privs) need to update/delete/insert/select in apex
      End user Operator( with only priv select) need to select particular table/pages in apex.

      Could someone throw lights on how this can be done in Apex.

      thank you

        • 1. Re: Authorization  -- Roles
          Denes Kubicek
          Create an Authorization Scheme similar to this:
             v_group   VARCHAR2 (400) DEFAULT NULL;
             SELECT HTMLDB_UTIL.get_groups_user_belongs_to (:app_user)
               INTO v_group
               FROM DUAL;
             IF INSTR ( v_group, 'TEST_GROUP_1') > 0
                RETURN TRUE;
                RETURN FALSE;
             END IF;
          You can assign an authorization to almost any object. Make sure you check the
          authorization only once per session (if not required to check on each pageview).

          The option to assign an authorization to a page or button you will find in page / button

          Denes Kubicek
          • 2. Re: Authorization  -- Roles
            APEX does not fully support roles yet. Database authentication is a dying trend, so I wouldn't recommend it for any new work, but if you already have a bunch of database users that you need to support, then it's a valid solution for now.

            As far as authorization, you're easiest solution might be to create a table that maps users and "roles", where roles are just a functional role that you define, not an actual database role. Then, create 2 Authorization Schemes, "Read Only" and "Read - Write". Make sure your Read Only scheme also includes your Read-Write users. Apply the read-only authorization scheme to the whole app, then apply the read-write to Insert - Update - Delete pages, as well as the columns on reports that link to them.

            This will be a lot easier if you do not user Tabular Forms, which I suspect will be your first instinct as a forms user.

            Good Luck,
            • 3. Re: Authorization  -- Roles
              Hi Tyler/Denes,

              Thank you. Yes you guessed it correctly. We built a tabular form and checking for the roles. Tyler do you have any sample for the authorization schemes?

              Best Regards
              Rajesh Alex
              • 4. Re: Authorization  -- Roles
                Hi taylor, why do you say database roles is dying concept??
                Thanks LK
                • 5. Re: Authorization  -- Roles

                  Take a look at this section:

                  for an example of an Authorization scheme.

                  I'm probably starting to get on some people's nerves by saying this, but IMHO, Tabular Forms should only be used as a last resort when users need to update a bunch of rows at the same time. My advice to you is to avoid them for the first few months of your APEX development career. Use the Report+Form wizard instead. Forget every design pattern you used in Forms, it was a client-server technology. Of course your SQL, PL/SQL, and general database developer skills are 100% applicable to APEX,.

                  • 6. Re: Why is db Authentication a dying trend?

                    You can go to your control panel and give us your real name, or at least something easier than "user596620".

                    Why do I think Database Authentication is a dying trend?
                    - LDAP directories were designed from the ground-up to store information like Authentication and Authorization data.
                    - Almost every technology out there can use LDAP as an Authentication source.
                    - There are only a few technologies that can use the DB for an authentication source. What if your users don't want to have a separate username / password for their APEX apps than their email account? You're out of luck.
                    - Databases were never designed as user repositories. It's a square peg in a round hole.
                    - Mixing data schemas and user accounts in a database is mess to maintain. It's often difficult to tell them apart. Which ones contain sensitive data, which ones are just users?
                    - There are only a few attributes that you can store in a database "user". If you want to store phone, email, certificate, etc, you have to create your tables for it.
                    - If end users have accounts in a database, it's that much easier for them to connect with third-party tools and start poking around.
                    - There is no concept of delegated administration with a database. How do you give someone the ability to manage all users in a particular group?
                    - Managing roles and privs for thousands of database user accounts is a nightmare. It's much easier in a web environment to assign select / execute privs to the account used by the web application, vs all of the users accessing the application.
                    - Onboarding / off-boarding / auditing accounts scattered throughout a bunch of databases is impossible vs creating / deleting / auditing all accounts and groups (roles) in a single LDAP directory.

                    I'm probably missing a lot of points here, so I may ask someone one the Identity Management side of things to chime-in.

                    • 7. Re: Why is db Authentication a dying trend?
                      Hi taylor, all of your points are good and make sense. but I think apex should have given an option of authentication by apex user or db user? I understand one user schema might be low cost solution for lot of them...

                      There are many ways to control user roles/access. we moved from application level to database level. Now your comments on coming back to application level.. One place or other someone has to control user access...database side or application side.. I know both sides…pros and cons.. If I can effort database side is easy for me.
                      Thanks, LK
                      • 8. Re: Authorization  -- Roles
                        As both an administrator and developer of applications, I am wondering how one would go about maintaining this. In particular, is there a report that I can run to get a listing of all authentication schemes applied to all objects throughout an APEX application? I would like to see pages and then their subcomponents listed after them. This should also include all objects with no authorization schemes applied to them.

                        An example would be someone gives me a new application to deploy. I want to be able to run a report to check that all elements are secure that need to be. Why? Because tomorrow it is in production and the programmer is on vacation in one week.

                        Thanks to all for their comments on this.

                        Thanks, John
                        • 9. Re: Authorization  -- Roles
                          John - Navigate to:

                          Home>Application Builder>Application XXX>Shared Components>Authorization Schemes>Authorization Scheme Utilization

                          ...to see such a report about authorization, not authentication.

                          There are also links on the right side of the page to reports that show pages with/without authorization.

                          • 10. Re: Authorization  -- Roles

                            So I am either being bipolar or thinking to much about phone opt out lists. Here is my analogy. I keep getting phone calls from solicitors. Why? because I never opted out. But then again I never opted in. Lets say that opting out is similar to having Insert, Update, Select and Delete on a table. Opting in would be represented by Select only access. I always want to opt out.

                            By that I mean lets apply the security model for users with Insert, Update, Select, and Delete to all objects. Make this the master security model. Then apply the lesser security model for select type access only to components that need it. As a DBA or application installer, I no longer have to worry about applying the tighter security. I only have to worry about applying the less stringent security.

                            Could this really work? Having two security levels in this way. I doubt it. But thought I would but it out there anyways. How would you store the list of objects or object ids that a particular security level would have access to?

                            Thanks, John