据需求及Oracle特性，限制用户获取数据可通过存储过程方式实现特定数据的访问，再通过特定用户来限制提取相关数据。 举例如下：如需求是计划访问MYEST用户下的表TP1,为限制其访问权限，创建存储过程TP1_SELECT,再通过新建用户USER2021来执行TP1_SELECT获取指定数据。 注：USER2021用户只有连接数据库权限和执行TP1_SELECT权限。 存储过程示例： ``` create or replace procedure tp1_select(num_A varchar2 DEFAULT '0') is v_RKBM TP1.RKBM%TYPE; v_GMSFHM TP1.GMSFHM%TYPE; v_XM TP1.XM%TYPE; N_COUNT number; N_RESULT number; cursor yb is select RKBM,GMSFHM,XM from TP1 where gmsfhm=num_A; begin select count(1) into N_RESULT from TP1 where gmsfhm=num_A; if num_A='0' then dbms_output.put_line('please input value'); elsif N_RESULT=0 then dbms_output.put_line('value is not exist'); else N_COUNT:=1; dbms_output.put_line('column_info: v_RKBM,v_GMSFHM,v_XM'); dbms_output.put_line('-----------: '); open yb; loop fetch yb into v_RKBM,v_GMSFHM,v_XM exit when yb%notfound; dbms_output.put_line('result_info: '||V_RKBM||','||V_GMSFHM||','||V_XM); end loop; end if; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('plesse execute "show errors" display Detailed error information'); end; / --创建一个对外用户 create user user2021 identified by oracle; --赋予user2021连接数据库权限 和执行tp1_select存储过程权限 grant connect to user2021; grant execute on mytest.tp1_select to user2021; --通过user2021用户执行 set serveroutput on; exec mytest.tp1_select(); ``` 结果展示 ``` --正常显示 exec mytest .tp1_selec('371481198007110X'); column_info: v_RKBM,v_GMSFHM,v_XM,v_HH,v_YHZGXDM,v_XBDM,v_MZDM,v_CSRQ,v_JG_SSXQDM -----------: result_info: 371481,371481198007110X,firsouler,aaa,bbbb,bbbb,ccc,dddddd,aaaaa result_info: 371481,371481198007110X,firsouler,aaa,bbbb,bbbb,ccc,dddddd,aaaaa --未输入值 不显示 SQL> exec mytest .tp1_select(); please input value PL/SQL procedure successfully completed. --值不存在 SQL> exec mytest .tp1_select(1000000000000000000); value is not exist PL/SQL procedure successfully completed. ```