rocky_cruise
asked on
ORA-00439 : feature not enabled : Fine-Grained access control
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.
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.
ASKER
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 ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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.
Regards,
Rocky.
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('
-- By default I am an EMP
me:= SYS_CONTEXT('userenv','ses
--
-- 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('
END IF;
--
-- If I do NOT have a manager then I am CEO
--
IF myboss IS NULL THEN
dbms_session.set_context('
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('Drop
l_statement:='DROP USER '||c1rec.ename||' cascade';
EXECUTE IMMEDIATE l_statement;
END LOOP;
FOR c2rec in c2 LOOP
dbms_output.put_line('Crea
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','ses
dbms_output.put_line(myena
--
OPEN C1;
FETCH C1 INTO myempno;
CLOSE C1;
--
IF SYS_CONTEXT('empctx','role
RETURN '';
-- I AM GOD! - no extra qualification to the query
ELSIF SYS_CONTEXT('empctx','role
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
-- 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;
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('
-- By default I am an EMP
me:= SYS_CONTEXT('userenv','ses
--
-- 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('
END IF;
--
-- If I do NOT have a manager then I am CEO
--
IF myboss IS NULL THEN
dbms_session.set_context('
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('Drop
l_statement:='DROP USER '||c1rec.ename||' cascade';
EXECUTE IMMEDIATE l_statement;
END LOOP;
FOR c2rec in c2 LOOP
dbms_output.put_line('Crea
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','ses
dbms_output.put_line(myena
--
OPEN C1;
FETCH C1 INTO myempno;
CLOSE C1;
--
IF SYS_CONTEXT('empctx','role
RETURN '';
-- I AM GOD! - no extra qualification to the query
ELSIF SYS_CONTEXT('empctx','role
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
-- 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;
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.
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.
Recommended disposition:
Accept mathavra's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
Accept mathavra's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
DBMS_RLS is available only with the Enterprise Edition.
Which edition of oracle do u have, is it enterprise edition?
Regards,