Solved

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

Posted on 2006-07-07
7
3,060 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
[X]
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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Component is listed with a Protocol more than once 3 47
Oracle - SQL Query with Function 3 66
PL SQL Developer 7 73
Read XML values 8 56
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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…

749 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