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
Solved

How to implement menu based security using Oracle 10g database roles thru Oracle Forms 10g

Posted on 2006-07-07
7
3,038 Views
Last Modified: 2008-01-09
Hi

I suspect this is very similar to this question:

http://www.experts-exchange.com/Databases/Oracle/Q_10242857.html

However, the database and forms have changed since the question and plus I cannot seem to find any SQL scripts in my installation of Oracle 10g Developer Suite.

Please advise.
0
Comment
Question by:freeka
7 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17057786
Check the directory
ORACLE_HOME\tools\db\forms50\frm50sec.sql
0
 

Author Comment

by:freeka
ID: 17058122
Many thanks for this. The description to this is :

Rem
Rem  NAME
Rem    frmsec.sql - FoRMs menu SECurity view
Rem
Rem  DESCRIPTION
Rem    Create Forms 5.0 Menu Security View
Rem
Rem  NOTES
Rem    This script must be run to enable Menu Security features based
Rem    on Oracle7 roles if the frm50bld.sql script has not been run
Rem    (i.e. if Forms are not going to be stored in the database).
Rem
Rem  COPYRIGHT
Rem Copyright (c) 1996, 2005, Oracle. All rights reserved.  
Rem
Rem  MODIFIED     (MM/DD/YY)
Rem    ahousing    01/26/05 - #(4138702) Update copyright date
Rem    enewman     11/22/96 - Update for 50
Rem    jcobb       03/13/95 - Creation
Rem


Which suggests that this is available simply for old applications moving over ? Is there a new way in 10g to skin this cat?
0
 

Author Comment

by:freeka
ID: 17058420
To summarize what I want :

I have an application that is being developed in Oracle Forms 10g with Oracle 10g Database as the back-end:

I wish to design a menu system to the application that is three tier, for example:

Accounts >> Data Entry >> Open Accounts

So, for a new database user, I assign him the Accounts Role, The Data Entry Role, and the Open Accounts role.

Then, for all the menu items in the form menu that I will develop, I will assign the above database roles so that those people that have access to a given role (on the third tier) can see/access the given menu item.

The menu will be designed so that it will also be 3-tier. So on the top level you will have Accounts, then sub-menu of Data-Entry and then a sub-sub-menu of Open Accounts.


Now that I have described what I want the question that will get the 500 points is:

1. Can I do this with Oracle 10g DB and Forms ?

2. What are the steps to perform this task ?


Many thanks


fr33ka

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17062434
Yes you can do this with Oracle 10g DB and FORMS.

Here are some of the steps. I haven't done this in a while, so there might be some additional  steps that may be missed out. But this should give you a general idea of the concept

Step1 - create the database roles either with create role command or using Form Builder for the above 3 roles

Step2- In your form builder , open the menu editor, click on the menu item you are assigning the role to. Click on the Tools-> properties for the menu item  and click on the menu item roles property. Click on More button, and enter the name of a valid role

Step 3. Set the USE SECURITY option to TRUE
0
 
LVL 13

Accepted Solution

by:
riazpk earned 500 total points
ID: 17090417
Well, i usually like to do it myself.

In Form create a new function (you can create this function in database to make reuse if you require):

FUNCTION check_role (p_role_name IN VARCHAR2)
RETURN BOOLEAN IS
      ln_dummy      number(1);
BEGIN
      SELECT 1
      INTO ln_dummy
      FROM user_role_privs
      WHERE GRANTED_ROLE = p_role_name
      AND ROWNUM = 1;
      
      RETURN TRUE;
EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN FALSE;
END;

Now suppose my requirement is like:

If the  user is not having DBA role, don't display "ClickMe" menu item.

I will do like:

TRIGGER:  WHEN-NEW-FORM-INSTANCE

IF NOT check_role('DBA') THEN
      SET_MENU_ITEM_PROPERTY('menu1.clickme',DISPLAYED, property_FALSE);
                --The code to hide the menu items goes here
               --you can also disable the menu items using ENABLED property if you want
END IF;

And i am done!!!

If you want a sample form using this technique, let us know your email address...

HTH

0
 

Author Comment

by:freeka
ID: 17097380
RiazPK

Many thanks for your reply, the sample form would be awesome if you can send it to me :

freeka@gmail.com

I will give it a test run-through before I close this question.


AM
0
 
LVL 13

Expert Comment

by:riazpk
ID: 17116723
Please check your mail
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
SQL Query 34 115
Migration from sql server to oracle 5 18
Need help on decision table structure 7 26
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

790 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