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@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 - 08:06:47 CST