Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Menu Security - Set Up

Posted on 2004-09-03
Medium Priority
Last Modified: 2007-12-19
I was reading "Advanced Forms and Reports", a section about menu security and tried to implement it but I am running into some problems.  The following are the steps that I took:

@frm90sec.sql  -- Creates the roles
@frm90grt.sql -- Grants roles to user 'USER'
create role CS_INV_POWER_USER

Then I went into forms builder and opened up my menu
  - In the property pallette of the menu I set use security to "YES"  -- this is for the menu module in general
  - Then I added CS_INV_POWER_USER to the MODULE ROLES  -- agian the main menu module
Then I went to one of the items in the Menu Module
  -  Set one item to CS_INV_POWER_USER
  - I left the Display Without Privilige set to YES

When I run the form - logged into USER - the menu at the top only has "Window" in it, anyone have any ideas how fix my problem?
Question by:Intern
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Expert Comment

ID: 11979488
Yes, first I got the same problem as you did and then could solve it as under (I am on Forms 4.5, maybe the view name has changed).

1. A view FRM45_ENABLED_ROLES is needed for which the create view script is given by ORacle.  Please read this provided by Forms Developer Guide

The Oracle Forms menu security feature depends on the view FRM45_ENABLED_ROLES.  This view is automatically created as part of the Forms 4.5 table scripts that are run during installation if you choose the "Create database tables" option.  If you did not choose that option, you or your DBA must logon as SYSTEM and run the SQL script FRM45SEC.SQL found in the Oracle Forms SQL directory.

Menu modules do not need to be stored in the database.  The FRM45_ENABLED_ROLES view is the only object that must exist in the database for menu security to function properly.  

2. I set the default role of the user by giving the command

ALTER USER username default role rolename;

3. In the Menu Module properties, I set Menu Security to Yes and put the role name in the Menu Module Roles

4. In the menu items, I have given  the Menu Item Roles in the root level menu and the item to which the menu item belongs for the item to be displayed.
So if Masters  has 2 choices 1. Dept Master and 2. Employee Master and you need to display Employee Master, then you have to give the Menu Item Roles for both Masters and Employee Master

5.  This should get you going

Expert Comment

ID: 11979531
While on the topic, let me tell you that this is what Oracle says

The Menu Role property is included for backward compatibility only.  Its use is not recommended in current applications.

You can use menu item built-ins like SET_MENU_ITEM_PROPERTY to implement menu security in an elegant manner

Author Comment

ID: 11996885

Thanks for the input - I do have a follow up question though.  Regarding your second post, if I want to use the "correct" security implementation then I would do what?

1) In the very first form that is called in the application - Find the user name and the roles associated with that name
2) use the roles to disable or enable items on the menu
        - using a procedure in the form to get the role and hide items?

I am not sure exactly how to implement this - it seems like the old way of using the default menu security is easier to do, and cleaner.  Anyway if you could give me your thoughts on how you would do this if you were using forms 9 I would greatly appreciate it.

Accepted Solution

sapnam earned 2000 total points
ID: 11998936
The problem with the roles approach is in the maintenance.  For changing a security setting, you have to change the menu module and that is the change in source code.  Not a very good concept, I feel.

What I do for implementing menu security is as under :

1. I have a table which stores my application user details. These are not the Oracle user details but the application user details. So if I have a Payroll Application and there are 2 users, SAPNAM and INTERN, there will be 2 records here, one for SAPNAM and one for INTERN.  This master can store details like user id, name, password etc.

2. I have a table which stores menu details.  The menu items are given ids which are heirarchial and this master stores all menu ids and their descriptions.  For e.g. if the menu contains 2 main items - Masters and Reports and Masters has Employee Master and Dept Master and Reports has Employee Report and Dept Report, I will give my menu ids in the menu module as under :
A          Main  Menu
A01      Masters
A0101  Employee Master
A0102  Dept Master
A02     Reports
A0201 Employee Report
A0202 Dept Report

I create the above records in my Menu Master.

3. Then I have a Menu Access Denied Master, which stores the menu items to which any user has been denied access.  So if SAPNAM is denied access to Employee Master, there will be a record for User id SAPNAM and Menu Id A0101 in this master.

4. So when the user logs in, this Menu Access Denied Master is checked and if records exist there, the SET_MENU_ITEM built in is used to make the item disabled or not to appear at all.

5. The advantage of giving heirarchial menu ids is that if you want to deny access to a entire set of items , say Masters, you can have one record in the Menu  Access Denied Master and deny the access for all items which are having menu id like that menu id


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question