-----------------------------------------------------------------
--CREATE DATA STRUCTURES
-----------------------------------------------------------------
 CREATE TABLE TBLOBJECT
  (
    OBJECTID   NUMBER(15)  ,
    OBJECTNAME VARCHAR2(50),
    OBJECTTYPE VARCHAR2(30),
    STATUS     VARCHAR2(100) DEFAULT 'N'
  );
 CREATE TABLE TBLUSERRIGHT
  (
    ID       NUMBER(20)         ,
    USERID   NUMBER(6)          ,
    OBJECTID NUMBER(15)         ,
    SEL      NUMBER(1) DEFAULT 0,
    DEL      NUMBER(1) DEFAULT 0,
    UPD      NUMBER(1) DEFAULT 0,
    INS      NUMBER(1) DEFAULT 0,
    EXE      NUMBER(1) DEFAULT 0,
    ACT      VARCHAR2(900)
  );
-----------------------------------------------------------------
--CREATE MVIEW
-----------------------------------------------------------------

CREATE OR REPLACE MATERIALIZED VIEW V$ObjRight AS
 SELECT R.ID   ,
  R.USERID     ,
  R.OBJECTID   ,
  O.OBJECTNAME ,
  O.OBJECTTYPE ,
  O.STATUS     ,
  R.SEL ,
   R.DEL,
  R.UPD ,
  R.INS ,
  R.EXE
   FROM SCC.TBLUSERRIGHT R ,
  TBLOBJECT O
  WHERE R.objectid=O.OBJECTID;

-----------------------------------------------------------------
--Trigger To control UserRights
-----------------------------------------------------------------
create or replace
TRIGGER SCC.TrUserRight AFTER
   INSERT OR UPDATE 
   ON tbluserright FOR EACH ROW  WHEN (NEW.ACT = NULL)--ACT UPDATE BY PROCEDURE HENCE BREAK CIRCLE
Declare
  OBJECTID_VAR   NUMBER(15);
  OBJECTNAME_vAR VARCHAR2(100);
  OBJECTTYPE_VAR VARCHAR2(50);
  USERNAME_VAR   VARCHAR2(900);
  USERID_VAR     VARCHAR2(50);
  STATUS_VAR VARCHAR2(900);
BEGIN
  IF INSERTING THEN
   OBJECTID_VAR :=:NEW.OBJECTID;
   USERID_VAR := :NEW.USERID;
  ELSIF UPDATING THEN
   OBJECTID_VAR :=:OLD.OBJECTID;
   USERID_VAR := :OLD.USERID;
  END IF; 
---GET APPROPRIATE USER NAME TO GRANT OR REVOKE RIGHTS     
SELECT USERNAME INTO USERNAME_VAR FROM TBLUSER WHERE ID = USERID_VAR;
---GET APPROPRIATE OBJECT NAME TO GRANT OR REVOKE RIGHTS     
SELECT OBJECTNAME,OBJECTTYPE INTO OBJECTNAME_VAR,OBJECTTYPE_VAR FROM TBLOBJECT WHERE OBJECTID = OBJECTID_VAR;
    
---------------------------------------------------------------------
--cALL PROCEDURE USERRIGHTPRO
---------------------------------------------------------------------
USERRIGHTPRO;
---------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
NULL;
END TRUSERRIGHT;
    


-----------------------------------------------------------------
  --CREATE PROCEDURE
-----------------------------------------------------------------
CREATE OR REPLACE
PROCEDURE USERRIGHTPRO
  AS
    USERID_V VARCHAR2(90),
    OBJECTtYPE_V  VARCHAR2(90),
    OBJECTNAME_V  VARCHAR2(90),
    USERNAME_V    VARCHAR2(90),
    INS_V         VARCHAR2(90),
    UPD_V         VARCHAR2(90),
    DEL_V         VARCHAR2(90),
    SEL_V         VARCHAR2(90),
    EXE_V         VARCHAR2(90), 
    STATUS_VAR VARCHAR2 ( 900 ) ;
BEGIN

--Select VALUES FROM MVIEW
SELECT ID   ,
  USERID     ,
  OBJECTID   ,
  OBJECTNAME ,
  OBJECTTYPE ,
  STATUS     ,
  SEL ,
  DEL,
  UPD ,
  INS ,
  EXE 
INTO
  USERID_V     ,
  OBJECTID_V   ,
  OBJECTNAME_V ,
  OBJECTTYPE_V ,
  STATUS_V     ,
  SEL_V ,
  DEL_V,
  UPD_V ,
  INS_V ,
  EXE_V
FROM  V$ObjRight WHERE ID =(SELECT MAX(ID) FROM V$ObjRight);
STATUS_VAR:='';

  ---------------------------------------------------------------------
  --IF TALBE OR VIEW OR SEQUENCE THEN GRANT SELECT
  ---------------------------------------------------------------------
  IF sel_V = '1' AND OBJECTTYPE_V = 'TABLE' OR OBJECTTYPE_V = 'VIEW' OR OBJECTTYPE_V = 'SEQUENCE' THEN
    EXECUTE IMMEDIATE 'GRANT SELECT ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:='GRANTE SELECT SUCCEDED ,';
  ELSE
    EXECUTE IMMEDIATE 'Revoke SELECT ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||'REVOKE SELECT SUCCEDED ,';
  END IF;
  ---------------------------------------------------------------------
  --IF TALBE OR VIEW THEN GRANT INSERT
  ---------------------------------------------------------------------
  IF INS_V = '1' AND OBJECTTYPE_V = 'TABLE' OR OBJECTTYPE_V = 'VIEW' THEN
    EXECUTE IMMEDIATE 'GRANT INSERT ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||' GRANTED INSERT SUCCEDED,';
  ELSE
    EXECUTE IMMEDIATE 'Revoke INSERT ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||'REVOKE INSERT SUCCEDED ,';
  END IF;
  ---------------------------------------------------------------------
  --IF TALBE OR VIEW THEN GRANT UPDATE
  ---------------------------------------------------------------------
  IF UPD_V = '1' AND OBJECTTYPE_V = 'TABLE' THEN
    EXECUTE IMMEDIATE 'GRANT UPDATE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||' GRANT UPDATE SUCCEDED ';
  ELSE
    EXECUTE IMMEDIATE 'Revoke UPDATE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||'REVOKE UPDATE SUCCEDED ,';
  END IF;
  ---------------------------------------------------------------------
  --IF TALBE OR VIEW THEN GRANT DELETE
  ---------------------------------------------------------------------
  IF DEL_V = '1' AND OBJECTTYPE_V = 'TABLE' OR OBJECTTYPE_V = 'VIEW' THEN
    EXECUTE IMMEDIATE 'GRANT DELETE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||' GRANTED DELETE SUCCEDED , ';
  ELSE
    EXECUTE IMMEDIATE 'Revoke DELETE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||'REVOKE DELETE SUCCEDED ,';
  END IF;
  ---------------------------------------------------------------------
  --IF PACKAGE OR PROCEDURE THEN GRANT EXECUTE
  ---------------------------------------------------------------------
  IF EXE_V = '1' AND OBJECTTYPE_V = 'PACKAGE' OR OBJECTTYPE_V = 'PROCEDURE' THEN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||' GRANTE EXECUTE SUCCEDED ';
  ELSE
    EXECUTE IMMEDIATE 'Revoke EXECUTE ON '||'SCC.'||OBJECTNAME_V ||' TO ' ||USERNAME_V;
    STATUS_VAR:=STATUS_VAR||'REVOKE EXECUTE SUCCEDED ,';
  END IF;
---------------------------------------------------------------------
  --INSERT INTO USER RIGHTS
---------------------------------------------------------------------
  IF STATUS_VAR IS NULL THEN
    STATUS_VAR  :='NOTHING PROCESSES';
  END IF;
   UPDATE TBLUSERRIGHT SET ACT = SUBSTR(STATUS_VAR,1,899);
  -----
EXCEPTION
WHEN OTHERS THEN
   UPDATE TBLUSERRIGHT
  SET ACT = SUBSTR((STATUS_VAR
    ||'NEW_USER_RIGHT_VRO Exception Raise While Granting or Revoke'),1,899);
END USERRIGHTPRO;