Solved

ORA-00439 : feature not enabled : Fine-Grained access control

Posted on 2001-06-28
9
5,181 Views
Last Modified: 2007-11-27
Hi there !!!

I am trying to implement row level security using Fine Grained Access Control. I have created the necessary Contexts, procedures etc. but as soon as I gave the statement EXECUTE DBMS_RLS.ADD_POLICY with the necessary parameters, the following error message appeared :

ORA-00439 : feature not enabled : Fine-Grained access control

What should I do to enable this feature ?

Please give a complete step by step solution as I am a novice at database administration.

Regards,
Rocky.
0
Comment
Question by:rocky_cruise
[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
9 Comments
 
LVL 2

Expert Comment

by:renuraj
ID: 6234650
Hi,

DBMS_RLS is available only with the Enterprise Edition.
Which edition of oracle do u have, is it enterprise edition?

Regards,
0
 

Author Comment

by:rocky_cruise
ID: 6234684
Its a licence copy of Oracle 8i. On the CD cover it is written "Oracle 8i Release 8.1.5 for MS Windows NT" so is it an Enterprise Edition or not ??
0
 
LVL 3

Accepted Solution

by:
mathavra earned 60 total points
ID: 6234911
Log on to Oracle via sqlplus and run the following:

SELECT * FROM V$VERSION

The results should show whether it is a Enterprise Edition or Standard Edition. Post the output here.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:rocky_cruise
ID: 6236650
I tried the command SELECT * FROM V$VERSION and the following was the result :

SQL> select * from v$version;

BANNER
-------------------------------------------------------
Oracle8i Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production


So can I use the Fine Grained Access Control or not ? Is there any way to enable this feature in this version ? Please tell me a way. Its urgent for me.

Regards,
Rocky.
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6236732
It seems like you have Standard Edition. Otherwise, it will mention clearly as Enterprise Edition as below for 8.1.6.

STANDARD EDITION output:
========================

BANNER
--------------------------------------------------
Oracle8i Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

ENTERPRISE EDITION :
====================
BANNER
---------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
******************
PL/SQL Release 8.1.6.3.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.3.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

Hope this helps.
0
 

Author Comment

by:rocky_cruise
ID: 6236785
But in my system it is showing the version as I have shown above. So wont I be able to use fine grained access control ? tell me any way if I can use it.

Regards,
Rocky.
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6236797
Since your Oracle database is standard edition you won't be able to use the feature. Here is the article from Oracle Metalink on that:

Problem Description
-------------------
When trying to use the DBMS_RLS package to enable the 'fine-grained access control' feature you receive the following error:

 Error:  ORA-439
 Text:   feature not enabled: %s
 ---------------------------------------------------------
 Cause:  The specified feature is not enabled.
 Action: Do not attempt to use this feature.

Solution Description
--------------------

 You are using the Standard Edition of Oracle 8i but
 you need the Enterprise Edition to use this functionality.
 To identify which version of the database you are using issue the following statement in sqlplus

 SELECT * FROM V$VERSION

 This will result in an output like this:

   SQL> select * from v$version;

BANNER
--------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0       Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

This output tells that there is a connection with a version
8.1.6.0.0 Enterprise Edition


FYI: If you want to try it out, here is an working example from Metalink.

This article demonstrates how to setup row level security and contexts using fine grained access control.

SCOPE & APPLICATION
For users trying to setup row level security.

SEARCH WORDS
oracle8i fine grained grain finegrained context policy


In order to run this example, the package DBMS_RLS must have previously
created. To create this package, the scripts dbmsrlsa.sql and prvtrlsa.plb
should be run as user sys, and execute privilege granted on DBMS_RLS to SCOTT.
         
CONNECT SCOTT/TIGER
SET SERVEROUTPUT ON
SET ECHO ON
REM
REM Application Context/Fine Grained access demo
REM AHolland 06-Jan-99
REM WARNING WARNING WARNING - read this through first, it drops and creates
REM schema
REM PREQUISITES
REM ===========
REM 1. SCOTT/TIGER
REM 2. SCOTT.EMP
REM 3. SCOTT.VALIDATE_EMP ( CTAS of EMP )
REM
REM This is the basic demonstration of row level security and
REM Contexts. It is based on a Presentation by Mark Vandenbroek
REM Of Oracle Belgium.
REM
REM A context is a general method for storing session level
REM information , similar to being able to store things
REM then access them through userenv.
REM
REM In this example there are three contexts
REM Employee - only allowed to see their own data
REM Manager - only allowed to their and their immediate
REM           Subbordinates data
REM CEO     - Full access
REM
REM This packaged procedure sets up the context for each user as they
REM login ( it is fired by an AFTER LOGIN TRIGGER )
REM
CREATE OR REPLACE PACKAGE empsec AS
   PROCEDURE setempctx;
END empsec;
/
CREATE OR REPLACE PACKAGE BODY empsec AS
PROCEDURE setempctx AS
   the_role VARCHAR2(10);
   me       VARCHAR2(10);
   myename  VARCHAR2(30);
   myempno  NUMBER;
   myboss   NUMBER;
   subs     NUMBER;
BEGIN
   dbms_session.set_context('empctx','role','EMP');
-- By default I am an EMP
   me:= SYS_CONTEXT('userenv','session_user');
--
-- get my information from emp            
--
   SELECT empno, mgr , ename
   INTO   myempno, myboss, myename
   FROM   validate_emp
   WHERE  ename=me;
--
-- If I have any subordinates I am an MGR
--
   SELECT COUNT(*)
   INTO   subs
   FROM   validate_emp
   WHERE  mgr=myempno;
--
-- @ do i really still need this ?
--
    IF NVL(subs,0) > 0 THEN
      dbms_session.set_context('empctx','role','MGR');
    END IF;
--
-- If I do NOT have a manager then I am CEO
--
   IF myboss IS NULL THEN
      dbms_session.set_context('empctx','role','CEO');
   END IF;
EXCEPTION
   -- Necessary because if there are users on your database
   -- Who aren't in emp you will get ORA-00600 [10544]
   -- In beta (BUG 770769) ORA-4088 by production
   WHEN OTHERS THEN NULL;
END setempctx;
END empsec;
/
--
-- Ensure that scott can create any context
--
CONNECT sys/change_on_install
--
GRANT CREATE ANY CONTEXT to SCOTT
/
GRANT CREATE ANY trigger to SCOTT
/
GRANT ADMINISTER DATABASE TRIGGER to SCOTT
/
--
CONNECT SCOTT/TIGER
--
--
-- Register The Namespace
--
CREATE OR REPLACE CONTEXT empctx USING scott.empsec
/
--
-- Set up the context every time a user logs into the database
-- In fact this will only be for database users also in EMP
-- Scott needs create any trigger to do this
--
--
CREATE OR REPLACE TRIGGER AJH_security_trigger
   AFTER LOGON
   ON DATABASE
   CALL scott.empsec.setempctx
/
REM
REM THIS ISN'T REALLY PART OF THE DEMO
REM However lets ensure the is a database user for
REM all the people in EMP
REM *****************************************************
REM BEWARE BEWARE BEWARE
REM This will drop any User with the same name as an emp
REM ename dynamically. If you REALLY have someone called
REM e.g. ADAMS THEY WILL BE dropped and recreated
REM The other consideration is that your EMP shouldn't
REM Have 2 million rows or this will take a long time
REM
REM SET serveroutput on
DECLARE
   l_statement varchar2(2000);
   l_statement2 varchar2(2000);
   l_statement3 varchar2(2000);
   l_dropuser varchar2(20);
   l_creuser varchar2(20);
   -- Everyone who is in emp ( except me ) who has an existing
   -- Oracle login
   CURSOR c1 IS SELECT a.ename
                FROM scott.validate_emp a, dba_users b
                WHERE a.ename = b.username
                AND a.ename !=user;
   -- All EMP's
   CURSOR c2 IS SELECT ename
                FROM scott.validate_emp
                WHERE  ename != user;
BEGIN
   FOR c1rec in c1 LOOP
      dbms_output.put_line('Dropping user '||c1rec.ename);
      l_statement:='DROP USER '||c1rec.ename||' cascade';
      EXECUTE IMMEDIATE l_statement;
   END LOOP;
   FOR c2rec in c2 LOOP
      dbms_output.put_line('Creating user'||c2rec.ename);
      l_statement2 := 'CREATE USER '||c2rec.ename||
                      ' IDENTIFIED BY '||c2rec.ename;
      l_statement3 := 'GRANT CONNECT TO '||c2rec.ename;
      EXECUTE IMMEDIATE l_statement2;
      EXECUTE IMMEDIATE l_statement3;
   END LOOP;
END;
/
REM
REM
REM *********************************************************
REM We will now create the package that is used to
REM control the access. This is executed by the POLICY.
REM The signature is mandatory
REM *********************************************************
REM
CONNECT SCOTT/TIGER
--
CREATE OR REPLACE PACKAGE hr_access AS
   FUNCTION secure_access( obj_schema VARCHAR2, obj_name VARCHAR2 )
      RETURN VARCHAR2;
END hr_access;
/
CREATE OR REPLACE PACKAGE BODY hr_access IS
FUNCTION secure_access( obj_schema VARCHAR2, obj_name VARCHAR2 )
      RETURN VARCHAR2 IS
   l_predicate VARCHAR2(2000);
   myename     VARCHAR2(30);
   myempno     NUMBER;
   CURSOR c1 IS SELECT empno FROM validate_emp WHERE ename = myename;
--
BEGIN
--
-- get the ename from the context
--
   myename := SYS_CONTEXT('userenv','session_user');
   dbms_output.put_line(myename);
--
   OPEN C1;
     FETCH C1 INTO myempno;
   CLOSE C1;
--
   IF SYS_CONTEXT('empctx','role') = 'CEO' THEN
      RETURN '';
      -- I AM GOD! - no extra qualification to the query
   ELSIF  SYS_CONTEXT('empctx','role') = 'MGR' THEN
      l_predicate :=
          'empno='||myempno||
          ' OR EMPNO IN ( SELECT empno FROM validate_emp  WHERE mgr= '||
          myempno|| ')';
          -- Make this a tree walk later
   ELSIF  SYS_CONTEXT('empctx','role') = 'EMP' THEN
      -- I can only see my own data
      l_predicate := 'empno = '||myempno;
   END IF;
   RETURN l_predicate;
END secure_access;
END hr_access;
/
REM create the policy
BEGIN
   DBMS_RLS.DROP_POLICY( 'SCOTT','EMP','EMP_POLICY');
END;
/
BEGIN
   DBMS_RLS.ADD_POLICY(
                       'SCOTT',
                       'EMP',
                       'EMP_POLICY',
                       'SCOTT',
                       'HR_ACCESS.SECURE_ACCESS',
                       'SELECT', true, true
                      );
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7036148
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7052510
Recommended disposition:
    Accept mathavra's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

617 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