Showing posts with label Oracle. Show all posts

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";

Filed under 0 comments

Oracle - Unlock Account After Exceeding Failed Attempts  

Posted by ReelTym 

SQL> select username,account_status
  2  from dba_users
  3  where username in ('SYS','SYSTEM','SCOTT');
 
 USERNAME                       ACCOUNT_STATUS
 ------------------------------ --------------------------------
 SCOTT                          LOCKED(TIMED)
 SYSTEM                         LOCKED(TIMED)
 SYS                            OPEN
 
SQL> -- put everything back as was
SQL> alter user scott account unlock;
 
 User altered.

Filed under 0 comments
Older Posts