CODEWITHSUNDEEP
oracle-databaseexceptionpartitioningvpd
user2671057
user2671057

Reputation: 1543

Exchange subpartition failed because of VPD even after grant exempt policy

I have a process that execute EXCHANGE SUBPARTITION statement.

Acoording to the fact that all the tables in the schema have a VPD POLICY protection,

we have a stored procedure that grant EXEMPT ACCESS POLICY to the user just before the action and revoke it just after the action.

Normally it's working perfect, but sometimes, we still get this exception:

ORA-14136: ALTER TABLE EXCHANGE restricted by fine-grained security

It occours even after we gave the grant.

Example:

The exchange subpartition code:

-- exchange subpartition statement
--ALTER TABLE DWH.FACT_AMS EXCHANGE SUBPARTITION P07_2017_ALL_AUDIT WITH TABLE DWH.MNG_ALL_CURRENT_KPIS

V_EXEC_I :=Q_CUR.SQL_QUERY;                                                      

--insert to log the exchage partition step
LOG_WRITER_INS(V_EXEC_I,V_ERROR_MSG,V_COMMENT_MSG,V_MASTER,C_PROCESS_NAME,C_PID);

-- execute exchange subpartition statement
DBA_MAINT.SF_GRANT_EXCEMPT(1);
EXECUTE IMMEDIATE V_EXEC_I;
DBA_MAINT.SF_GRANT_EXCEMPT(0);

The sf_grant_excempt procedure:

create or replace
procedure           SF_GRANT_EXCEMPT(grant_opt in number)
is

v_cnt number := -1;

begin

if grant_opt = 1 then
      EXECUTE IMMEDIATE 'GRANT EXEMPT ACCESS POLICY TO dwh';
ELSIF GRANT_OPT = 0 THEN
      EXECUTE IMMEDIATE 'REVOKE EXEMPT ACCESS POLICY from dwh';
END IF;

END SF_GRANT_EXCEMPT;

It can happend with different tables (this is a dynamic process), but the tables which involved in the statement are always of the same owner (which is always dwh), the code of the EXCHANGE SUBPARTITION is of package of the same user also (Dwh), and the exception we got is not comming from the SF_GRANT_EXCEMPT procedure. So the grant is working...

Upvotes: 2

Views: 179

Answers (0)

Related Questions