Blog do Marcio Portes

;-)

olá pessoal...
estou fazendo um trabalho da pós-graduação,
era pra ter entregue semana passada, mas estou
travado num problema...
estou criando uma Policy pelo comando:

BEGIN
 SYS.DBMS_RLS.ADD_POLICY (
 'ALUNO14',                                -- user
 'NOTA_ALUNO',                             -- tabela
 'POL14',                                  -- nome da policy
 'ALUNO14',                                -- user
 'F_ALUNO');                               -- nome da function
END;
/

A policy está sendo criada, PORÉM, ela não fica válida...
ao tentar fazer um select na tabela nota_aluno, vem o erro:

SQL> select * from nota_aluno;

select * from nota_aluno
*
ERROR at line 1:
ORA-28112: failed to execute policy function

Ao clicar na Policy pelo TOAD, aparece o erro:
ORA-00942: table or view does not exist
Socorro!!!!!

idle> connect / as sysdba
Connected.
sys@LNX10GR2> create user
  2     aluno14 identified by aluno14
  3     default tablespace users
  4     quota unlimited on users
  5  /

User created.

sys@LNX10GR2> grant create session,
  2        create table,
  3        create procedure,
  4        create public synonym
  5     to
  6        aluno14;

Grant succeeded.

sys@LNX10GR2> grant execute on sys.dbms_rls to aluno14;

Grant succeeded.

sys@LNX10GR2>
sys@LNX10GR2> connect aluno14/aluno14
Connected.
aluno14@LNX10GR2>
aluno14@LNX10GR2> create table
  2  nota_aluno (
  3          aluno   varchar2(30),
  4          nota    int
  5  );

Table created.

aluno14@LNX10GR2>
aluno14@LNX10GR2> create or replace
  2  function f_aluno (
  3                    p_schema in varchar2,
  4                    p_object in varchar2
  5           ) return varchar2
  6  is
  7  begin
  8      if ( user = 'OPS$MARCIO' )
  9      then
 10         return '1=1';
 11      else
 12         return 'aluno = user';
 13      end if;
 14  end;
 15  /

Function created.

aluno14@LNX10GR2>
aluno14@LNX10GR2> insert into nota_aluno values ('ALUNO14', 8 );

1 row created.

aluno14@LNX10GR2> insert into nota_aluno values ('MILTON', 10 );

1 row created.

aluno14@LNX10GR2> insert into nota_aluno values ('SCOTT', 1 );

1 row created.

aluno14@LNX10GR2> commit;

Commit complete.

aluno14@LNX10GR2>
aluno14@LNX10GR2> select * from nota_aluno;

ALUNO                                   NOTA
------------------------------ -------------
ALUNO14                                    8
MILTON                                    10
SCOTT                                      1

3 rows selected.

aluno14@LNX10GR2>
aluno14@LNX10GR2> grant select on nota_aluno to public;

Grant succeeded.

aluno14@LNX10GR2> create or replace public synonym nota_aluno for nota_aluno;

Synonym created.

aluno14@LNX10GR2>
aluno14@LNX10GR2> begin
  2     sys.dbms_rls.add_policy (
  3          object_name     => 'NOTA_ALUNO',
  4          policy_name     => 'POL14',
  5          policy_function => 'f_aluno',
  6          statement_types => 'select ',
  7          update_check    => TRUE
  8     );
  9  end;
 10  /

PL/SQL procedure successfully completed.

aluno14@LNX10GR2>
aluno14@LNX10GR2> select * from nota_aluno;

ALUNO                                   NOTA
------------------------------ -------------
ALUNO14                                    8

1 row selected.

aluno14@LNX10GR2> connect /
Connected.
ops$marcio@LNX10GR2> select * from nota_aluno;

ALUNO                                   NOTA
------------------------------ -------------
ALUNO14                                    8
MILTON                                    10
SCOTT                                      1

3 rows selected.

ops$marcio@LNX10GR2> connect scott/tiger
Connected.
scott@LNX10GR2> select * from nota_aluno;

ALUNO                                   NOTA
------------------------------ -------------
SCOTT                                      1

1 row selected.

scott@LNX10GR2> connect aluno1/aluno1
Connected.
aluno1@LNX10GR2> select * from nota_aluno;

no rows selected

aluno1@LNX10GR2>

# posted by Marcio : 11:00 PM