REM $Header$
REM
 
REM Copyright (c) Oracle Corporation All Right Reserved.
 
REM
REM NAME:
REM      sdogrant.sql
REM DESCRIPTION
REM   This file is used to propagate the grants on the registered base 
REM table to the various partitions
REM PROCEDURES
REM   propagate_grants 
REM NOTES
REM   This procedure is used to propagate the grants on the base table to 
REM   the paartitions. This procedure must be called by the owner of the 
REM   partitions. 
REM
REM MODIFIED     (DD-MON-YY)  DESCRIPTION
REM NAgarwal      06-SEP-96   Creation

REM NOTES:


CREATE OR REPLACE PROCEDURE propagate_grants (base_table varchar2) IS
  crs		integer;
  crs1		integer;
  dummy		integer;
  grnt_count	integer;
  i		integer;
  sn		varchar2(30);
  part_name	varchar2(30);
  seq		varchar2(30);
  user_name	varchar2(30);
  stmt		varchar2(32767);
BEGIN 
  crs  := dbms_sql.open_cursor;
  crs1 := dbms_sql.open_cursor;

  select user into sn from dual;

  dbms_sql.parse(crs, 'select count(*) from USER_TAB_PRIVS where table_name ='''||
    base_table || '''', dbms_sql.v7); 
  dbms_sql.define_column(crs, 1, grnt_count);
  dummy := dbms_sql.execute_and_fetch(crs);
  dbms_sql.column_value(crs, 1, grnt_count);

  if (grnt_count > 0) then 
    dbms_sql.parse(crs, 'select partition_table_name from USER_MD_PARTITIONS '||
      ' where md_table_name = '''||base_table||'''', dbms_sql.v7);
    dbms_sql.define_column(crs, 1, part_name, 30);
    dummy := dbms_sql.execute(crs);
  
    LOOP 
      dummy := dbms_sql.fetch_rows(crs);
      if (dummy = 0) then 
        exit;
      end if;

      dbms_sql.column_value(crs, 1, part_name);
      seq := substr(part_name, instr(part_name, '_P', -1, 1) +2, 30);
      mdsys.mdgen.setup_grnt(sn, base_table);
         
      FOR i in 1..grnt_count 
      LOOP 
        stmt := mdsys.mdgen.getnext_grnt(sn, base_table, seq);
        dbms_sql.parse(crs1, stmt, dbms_sql.v7);
        dummy := dbms_sql.execute(crs1); 
      END LOOP; 
    END LOOP;
  end if;

  dbms_sql.close_cursor(crs);
  dbms_sql.close_cursor(crs1);
 
END propagate_grants; 

/