Home -> Usenet -> comp.databases.oracle -> Oracle Spatial Oracle Label Security

Oracle Spatial Oracle Label Security

From: Shawn Bisgrove <shawn_bisgrove_at_yahoo.com>
Date: 13 Dec 2001 06:06:47 -0800
Message-ID: <e5926816.0112130606.1e4b29aa_at_posting.google.com> 






    
  
  •   Author: Shawn Bisgrove
  
  •   Oracle Version: 9.0.1.1 Personal
  
  •   Operating System Windows 2000

  
  •   I have just started evaluating Oracle for use on a future project
and have decided to recommend
  
  •   using both the Label and Spatial components.
  
  •   Sorry about the length of this post.  I did make sure that the
example was simple but complete.

    
--

      
  •   I have run into what I believe is a pretty severe bug.  It may not
be, but if it is it hinders
  
  •   the use of Oracle Spatial and Oracle Label Security with R-TREE and
Q-TREE indexing together.
  
  •   Please tell me that I am doing something wrong.   I will cross post
in the Spatial section.
  
  •   Variations on this example can be derived from the base example and
will probably exhibit the
  
  •   same behavior.

  
  •   This example requires a database DARS configured with Label
Security
  
  •   User: system Password: system

  
  •   remove any residual policy information
connect admin/admin_at_dars;
execute sa_sysdba.drop_policy('DARS_RESOURCES');






connect system/system_at_dars;




drop user shawn cascade;


drop user dars cascade;


drop user admin cascade;




create user admin identified by admin;


grant connect, resource, select_catalog_role to admin;



grant connect to shawn identified by shawn;
grant connect to dars identified by dars;
grant resource to dars;



connect dars/dars_at_dars;




CREATE TABLE DATASET (COVERAGE MDSYS.SDO_GEOMETRY,

                      INTERNALIDENTIFIER NUMBER(6) PRIMARY KEY);


create sequence DATASET_INTERNALIDENTIFIER_SEQ;




delete from USER_SDO_GEOM_METADATA;


INSERT INTO USER_SDO_GEOM_METADATA values ('dataset', 'coverage',
mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT('X',-90,90,.001) ,


                                                                MDSYS.SDO_DIM_ELEMENT('Y',-180,180,.001)),  null); 



    
  
  •   OCCURS IN BOTH R-TREE and Q-TREE (for example using R-TREE)
CREATE INDEX DATASET_SPATIAL_IDX ON dars.DATASET(COVERAGE) INDEXTYPE
IS MDSYS.SPATIAL_INDEX;


    
--SELECT SDO_TUNE.QUALITY_DEGRADATION('DARS', 'DATASET_SPATIAL_IDX')

    
    
FROM DUAL;


    
--SELECT SDO_TUNE.RTREE_QUALITY('DARS', 'DATASET_SPATIAL_IDX') FROM

    
    
DUAL;


  
  •   UNCOMMENT FOR Q-TREE EXAMPLE


    
--CREATE INDEX DATASET_SPATIAL_IDX ON dars.DATASET(COVERAGE) INDEXTYPE

    
IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL = 8');


    
--SELECT SDO_TUNE.ESTIMATE_TILING_LEVEL('dars.dataset', 'coverage', 8)

    
from dual;






grant select, insert, update, delete on DATASET to public;
grant select, alter on DATASET_INTERNALIDENTIFIER_SEQ to public;




connect lbacsys/lbacsys_at_dars


GRANT EXECUTE ON SA_COMPONENTS    TO admin WITH GRANT OPTION;
GRANT EXECUTE ON SA_USER_ADMIN    TO admin WITH GRANT OPTION;
GRANT EXECUTE ON sa_user_admin    TO admin WITH GRANT OPTION;
GRANT EXECUTE ON sa_label_admin   TO admin WITH GRANT OPTION;
GRANT EXECUTE ON sa_policy_admin  TO admin WITH GRANT OPTION;
GRANT EXECUTE ON sa_audit_admin   TO admin WITH GRANT OPTION;


GRANT LBAC_DBA TO admin; 


GRANT EXECUTE ON SA_SYSDBA TO admin; 


GRANT EXECUTE ON to_lbac_data_label TO admin;



connect admin/admin_at_dars;


execute SA_SYSDBA.CREATE_POLICY('DARS_RESOURCES', 'ACL');



execute SA_COMPONENTS.CREATE_LEVEL('DARS_RESOURCES', 5000, 'EMP',
'Employee');



execute SA_COMPONENTS.CREATE_GROUP('DARS_RESOURCES', 500, 'MGR',
'Manager', NULL);


execute SA_COMPONENTS.CREATE_GROUP('DARS_RESOURCES', 600, 'WKR1',
'Worker', 'MGR');


execute SA_COMPONENTS.CREATE_GROUP('DARS_RESOURCES', 700, 'WKR2',
'Worker 2', 'MGR');



execute SA_LABEL_ADMIN.CREATE_LABEL('DARS_RESOURCES', 3000,
'EMP::MGR');



execute SA_LABEL_ADMIN.CREATE_LABEL('DARS_RESOURCES', 3100,
'EMP::WKR1');



execute SA_LABEL_ADMIN.CREATE_LABEL('DARS_RESOURCES', 3200,
'EMP::WKR2');



execute SA_POLICY_ADMIN.APPLY_TABLE_POLICY('DARS_RESOURCES', 'DARS',
'DATASET', 'HIDE, READ_CONTROL, LABEL_DEFAULT', NULL, NULL);




--execute SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('DARS_RESOURCES',


'DARS', 'DATASET');




    
  
  •   also occurs if given more than one label and then a default label 
such as 'EMP::WKR1 ,WKR2', 'EMP::WKR1');
execute SA_USER_ADMIN.SET_USER_LABELS('DARS_RESOURCES', 'SHAWN',
'EMP::WKR1');

execute SA_USER_ADMIN.SET_USER_LABELS('DARS_RESOURCES', 'DARS',
'EMP::MGR');







connect shawn/shawn_at_dars;


insert into dars.dataset values (MDSYS.SDO_GEOMETRY(2003,  NULL, 
NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),

MDSYS.SDO_ORDINATE_ARRAY(33.422, -113.4822, 33.5601, -113.2995) ), 

dars.DATASET_INTERNALIDENTIFIER_SEQ.nextval );
commit;


select count(*) from dars.dataset;




connect dars/dars_at_dars;


insert into dars.dataset values (MDSYS.SDO_GEOMETRY(2003,  NULL, 
NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),

MDSYS.SDO_ORDINATE_ARRAY(33.422, -113.4822, 33.5601, -113.2995) ), 

dars.DATASET_INTERNALIDENTIFIER_SEQ.nextval );
commit;


select count(*) from dars.dataset;




prompt first execute as user dars


connect dars/dars_at_dars


-- causes a bug, first hint is label access control and rtree screwing


up??


select internalidentifier from dars.dataset where 
mdsys.sdo_relate(coverage,


        mdsys.SDO_GEOMETRY(2003, NULL, NULL, mdsys.SDO_ELEM_INFO_ARRAY(1,
1003, 3),

	mdsys.SDO_ORDINATE_ARRAY(33, -114, 34, -112)),
	'mask = INSIDE querytype = WINDOW')= 'TRUE' order by


internalidentifier;



connect dars/dars_at_dars


-- does not cause same problem because we are not using the index


select internalidentifier from dars.dataset where 


    sdo_geom.relate( coverage, 'INSIDE', 


        mdsys.SDO_GEOMETRY(2003, NULL, NULL, mdsys.SDO_ELEM_INFO_ARRAY(1,
1003, 3),

	mdsys.SDO_ORDINATE_ARRAY(33, -114, 34, -112)),
        .005 ) = 'INSIDE';
        






prompt first execute as user shawn


connect shawn/shawn_at_dars


-- causes a bug, first hint is label access control and rtree screwing


up??


-- ERROR at line 1:



    
  
  •   ORA-03113: end-of-file on communication channel
  
  •   if q-tree is being used:






select internalidentifier from dars.dataset where 
mdsys.sdo_relate(coverage,


        mdsys.SDO_GEOMETRY(2003, NULL, NULL, mdsys.SDO_ELEM_INFO_ARRAY(1,
1003, 3),

	mdsys.SDO_ORDINATE_ARRAY(33, -114, 34, -112)),
	'mask = INSIDE querytype = WINDOW')= 'TRUE' order by


internalidentifier;



connect shawn/shawn_at_dars


-- does not cause same problem because we are not using the index


select internalidentifier from dars.dataset where 


    sdo_geom.relate( coverage, 'INSIDE', 


        mdsys.SDO_GEOMETRY(2003, NULL, NULL, mdsys.SDO_ELEM_INFO_ARRAY(1,
1003, 3),

	mdsys.SDO_ORDINATE_ARRAY(33, -114, 34, -112)),
        .005 ) = 'INSIDE';






connect dars/dars_at_dars


prompt break the index at this point


-- causes the index to be broken:



    
  
  •   ERROR at line 1:
  
  •   ORA-29858: error occurred in the execution of ODCIINDEXALTER
routine
  
  •   ORA-29400: data cartridge error
  
  •   ORA-01031: insufficient privileges
  
  •   ORA-13249: internal error in Spatial index: [mdidxrbd]
  
  •   ORA-13249: Error in Spatial index: index build failed
  
  •   ORA-13249: Stmt-Execute Failure: SELECT count(*) from DARS.DATASET
  
  •   ORA-29400: data cartridge error
  
  •   ORA-01031: insufficient privileges
  
  •   ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 232
  
  •   ORA-06512: at line 1
alter index dars.dataset_spatial_idx rebuild;



Received on Thu Dec 13 2001 - 15:06:47 CET












Original text of this message