Solved

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

Posted on 2001-06-28
9
4,691 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
9 Comments
 
LVL 2

Expert Comment

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

Author Comment

by:rocky_cruise
Comment Utility
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
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 3

Expert Comment

by:mathavra
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Recommended disposition:
    Accept mathavra's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

9 Experts available now in Live!

Get 1:1 Help Now