Solved

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

Posted on 2006-07-07
7
2,929 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
Comment Utility
Check the directory
ORACLE_HOME\tools\db\forms50\frm50sec.sql
0
 

Author Comment

by:freeka
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Create index on View 27 52
Convert Oracle data into XML document 2 37
Migrate database to ASM disks. 1 26
SQL Retrieve Values 4 41
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now