Get list of GRANT statements by User or Object in Oracle  

Posted by ReelTym

-- Get a list of grant statements by user:
  SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ASTRO') FROM DUAL;
   
-- Returns:
  GRANT REFERENCES ON "METADOSSIER"."METADOSSIERS" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."AGENT_LOGIN_MAPPING" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."AGENT_ROLE_RLT" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."ECT_ROLE" TO "ASTRO";
  GRANT REFERENCES ON "COMPANY_STRUCTURE"."COMP_COMPANY" TO "ASTRO";
  GRANT REFERENCES ON "COMPANY_STRUCTURE"."USER_USER" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENTS_TYPE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."FUNCTIONAL_DOMAIN_REF" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."HISTORY" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."JOURNAL" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."MESSAGE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."STATUS_REF" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."TEMPLATE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENTS_TYPE_HISTORY" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."MESSAGING_EVENT_NAME" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."NOTIF_CODE_EVENT_NAME" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."UPDATE_ENTITY_ID" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."NOTIFICATION_ERROR" TO "ASTRO";

-- Get a list of grant statements by object:
  SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EVENT_CONFIG','WORKFLOW') FROM DUAL;
   
-- Returns:
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "MID";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ASTRO";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "INTREP";

This entry was posted on Wednesday, April 13, 2011 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments