Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle Spatial Oracle Label Security
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);
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;
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');
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 byinternalidentifier;
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:
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 byinternalidentifier;
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: