how to "execute immediate" a grant stmt inside a stored procedure.

what error are you getting

i dont see any extra semicolon

ORA-00911 invalid character
Ora-06512 at line 39
I need to get this working first then I need to add a cursor that finds all the users instread of an IN...I need to execute this command line...

Its at the stmt :=

and execute immediate...

I need to convert this so I can use it standalone..

i still can't see any extra semicolon, what's inside grantee_p?

and what do u mean by this

>>I need to get this working first then I need to add a cursor that finds all the users >>instread of an IN...I need to execute this command line...

its a user name....

I need to see this work for one in first then I will change it to add a section that finds me the user I need instead of adding  doing it one at a time...

I need to be able to walk up to a server...run this as just an exeute patch_uers and the whole thing find users and grant all the permissions..

You are missing semicolon above the stmt:= after LOOP statement.

I took it off...because I was trying to get it to work....

Here is a formated version of your procedure , i can't see where you went wrong in there , can you post the error you are getting again please ?

create or replace
PROCEDURE patch_user(grantee_p IN VARCHAR2) IS
   stmt       VARCHAR2(300);
   grantee    VARCHAR2(30);
BEGIN
   grantee := UPPER(grantee_p);
   dbms_output.put_line(grantee);
   -- Grant execute for types and packages
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                      WHERE object_type = 'PACKAGE'
                         OR object_type = 'TYPE'
                         OR object_type = 'FUNCTION'
                         OR object_type = 'PROCEDURE'
                        AND object_name <> 'SETUP_IWPC_USER')
   LOOP
      stmt := 'grant execute on ' || grant_cur.object_name ||' to ' || grantee;
      EXECUTE IMMEDIATE stmt;
      dbms_output.put_line(stmt);
   END LOOP;
   dbms_output.put_line('out_execute_loop');
   -- Grant select, insert, delete, and update on the tables.
   -- Required for CWT, IPIB, and ACE
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                     WHERE object_type = 'TABLE' )
   LOOP
      stmt := 'grant select, insert, delete, update on ' || grant_cur.object_name || ' to '|| grantee;
      execute immediate stmt;
--dbms_output.put_line(stmt);
  END LOOP;
  dbms_output.put_line('here');
   -- Grant select on Sequences (required for CNOAT but not the others)
  FOR grant_cur IN (SELECT object_name
                      FROM user_objects
                     WHERE object_type = 'SEQUENCE')
  LOOP
      stmt := 'grant select on ' || grant_cur.object_name || ' to ' || grantee;
      EXECUTE IMMEDIATE stmt;
  END LOOP;
  stmt := 'grant select on DEFAULT_CAVEAT to ' || grantee;
  EXECUTE IMMEDIATE stmt;
   -- Drop synonyms
  FOR synonym_cur IN (SELECT owner,
                             synonym_name
                        FROM all_synonyms
                       WHERE owner = grantee)
  LOOP
      -- Drop the synonyms
      stmt := 'drop synonym ' || synonym_cur.owner || '.' || synonym_cur.synonym_name;
      EXECUTE IMMEDIATE stmt;
  END LOOP;
 
  BEGIN
    stmt := 'drop synonym ' || grantee || '.DEFAULT_CAVEAT';
    EXECUTE IMMEDIATE stmt;
    EXCEPTION
       WHEN  OTHERS THEN -- sqlcode = 'ORA-01434' THEN
         NULL;
  END;
   -- Create synonyms
  FOR synonym_cur IN (SELECT object_name
                        FROM user_objects
                       WHERE object_type = 'TABLE'
                          OR object_type = 'TYPE'
                          OR object_type = 'SEQUENCE'
                          OR object_type = 'PACKAGE'
                          OR object_type = 'FUNCTION'
                          OR object_type = 'PROCEDURE'
                         AND object_name <> 'SETUP_IWPC_USER')
   LOOP
      -- Create the synonyms
      stmt := 'create synonym ' || grantee || '.' || synonym_cur.object_name ||
              ' for ' || synonym_cur.object_name;
      EXECUTE IMMEDIATE stmt;
   END LOOP;
   stmt := 'create synonym ' || grantee || '.DEFAULT_CAVEAT for DEFAULT_CAVEAT';
   EXECUTE IMMEDIATE stmt;
END patch_user;

Select allOpen in new window

oops... after i saw it formatted i realized i was wrong... there shouldn't be any semicolon after LOOP statement... .

this how I test...

DECLARE
  GRANTEE_P VARCHAR2(200);
BEGIN
  GRANTEE_P := 'iwpcuser1';

  PATCH_IWPC_USER(
    GRANTEE_P => GRANTEE_P
  );
END;

***********-----------------------------------------------------------------
Error and output statments

Connecting to the database iwpc.
ORA-00911: invalid character
ORA-06512: at "IWPCDBA.PATCH_IWPC_USER", line 39
ORA-06512: at line 6
IWPCUSER1
grant execute on PATCH_IWPC_USER to IWPCUSER1
grant execute on MAP_SET to IWPCUSER1
grant execute on HASH_MAP to IWPCUSER1
grant execute on STRING_LIST to IWPCUSER1
grant execute on GET_SCHEMA_OWNER to IWPCUSER1
grant execute on NUMBER_LIST to IWPCUSER1
grant execute on IWPCDB to IWPCUSER1
grant execute on CLASS_MARKING_DATA to IWPCUSER1
grant execute on CLASS_MARKING_DATA_LIST to IWPCUSER1
grant execute on FAC_CATEGORY_DATA to IWPCUSER1
grant execute on FAC_CATEGORY_DATA_LIST to IWPCUSER1
grant execute on OB_TYPE_DATA to IWPCUSER1
grant execute on OB_TYPE_DATA_LIST to IWPCUSER1
grant execute on LOCK_STATUS_DATA to IWPCUSER1
grant execute on CNOA_NETWORK_ASSET_DATA to IWPCUSER1
grant execute on CNOA_NETWORK_ASSET_DATA_LIST to IWPCUSER1
grant execute on CNOA_INTERFACE_PKG to IWPCUSER1
grant execute on SECURITY_INTERFACE_PKG to IWPCUSER1
grant execute on EVENT_PKG to IWPCUSER1
grant execute on CR_INTERFACE_PKG to IWPCUSER1
grant execute on JFC_OBJ_DATA to IWPCUSER1
grant execute on JFC_OBJ_DATA_LIST to IWPCUSER1
grant execute on RED_GOALS_DATA to IWPCUSER1
grant execute on RED_GOALS_DATA_LIST to IWPCUSER1
grant execute on WINDOW_OPP_DATA to IWPCUSER1
grant execute on WINDOW_OPP_DATA_LIST to IWPCUSER1
grant execute on OTHER_INTERFACE_PKG to IWPCUSER1
grant execute on RED_GOALS_INTERFACE_PKG to IWPCUSER1
grant execute on WINDOW_OPP_INTERFACE_PKG to IWPCUSER1
grant execute on DEBUG to IWPCUSER1
grant execute on SEND_MESSAGE to IWPCUSER1
grant execute on DELETE_IWPC_ITEM to IWPCUSER1
grant execute on TRANSLATE_PKG to IWPCUSER1
grant execute on DELETE_ION_ITEM to IWPCUSER1
grant execute on GET_IWPC_PARENT to IWPCUSER1
grant execute on ION_PLAN_DATA to IWPCUSER1
grant execute on ION_PLAN_DATA_LIST to IWPCUSER1
grant execute on COPY_PKG to IWPCUSER1
grant execute on STUB_PKG to IWPCUSER1
grant execute on STUB_PKG_PUSH to IWPCUSER1
grant execute on STATE_PKG to IWPCUSER1
grant execute on LOCK_PKG to IWPCUSER1
grant execute on EFFECT_SYNC_PKG to IWPCUSER1
grant execute on PHASE_SYNC_PKG to IWPCUSER1
grant execute on E_CAUSAL_LINK_DATA_LIST to IWPCUSER1
grant execute on ION_TO_CPT_PLAN_TRANSFER_PKG to IWPCUSER1
grant execute on CPT_TO_ION_PLAN_TRANSFER_PKG to IWPCUSER1
grant execute on COMMON_DB_PKG to IWPCUSER1
grant execute on COA_STATE_PKG to IWPCUSER1
grant execute on GUID_SET_DATA to IWPCUSER1
grant execute on GUID_SET_DATA_LIST to IWPCUSER1
grant execute on ACTION_CAPABILITY_DATA to IWPCUSER1
grant execute on ACTION_CAPABILITY_DATA_LIST to IWPCUSER1
grant execute on HISTORY_DATA to IWPCUSER1
grant execute on HISTORY_DATA_LIST to IWPCUSER1
grant execute on OP_CHANGE_HISTORY_DATA to IWPCUSER1
grant execute on OP_CHANGE_HISTORY_DATA_LIST to IWPCUSER1
grant execute on PLAN_INTERFACE_HISTORY_PKG to IWPCUSER1
grant execute on OBS_OPPORTUNITY_DATA to IWPCUSER1
grant execute on OBS_OPPORTUNITY_DATA_LIST to IWPCUSER1
grant execute on INDICATOR_STATUS_DATA to IWPCUSER1
grant execute on INDICATOR_STATUS_DATA_LIST to IWPCUSER1
grant execute on INDICATOR_DATA to IWPCUSER1
grant execute on INDICATOR_DATA_LIST to IWPCUSER1
grant execute on E_CAUSAL_LINK_DATA to IWPCUSER1
grant execute on TARGET_DATA to IWPCUSER1
grant execute on TARGET_DATA_LIST to IWPCUSER1
grant execute on ASSET_DATA to IWPCUSER1
grant execute on ASSET_DATA_LIST to IWPCUSER1
grant execute on E_EFFECT_DATA to IWPCUSER1
grant execute on E_PHASE_DATA to IWPCUSER1
grant execute on E_PHASE_DATA_LIST to IWPCUSER1
grant execute on ASSESSMENT_ACCESSOR_PKG to IWPCUSER1
grant execute on TARGET_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on ASSET_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on CAUSAL_LINK_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on E_EFFECT_DATA_LIST to IWPCUSER1
grant execute on EFFECT_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on PHASE_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on PLAN_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on PLAN_EXT_ACCESSOR_PKG to IWPCUSER1
grant execute on PREDEFINED_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on POINT_OF_CONTACT_DATA to IWPCUSER1
grant execute on POINT_OF_CONTACT_ACCESSOR_PKG to IWPCUSER1
grant execute on RFA_MESSAGE_DATA to IWPCUSER1
grant execute on RFAR_MESSAGE_DATA to IWPCUSER1
grant execute on RFA_MESSAGE_DATA_LIST to IWPCUSER1
grant execute on RFAR_MESSAGE_DATA_LIST to IWPCUSER1
grant execute on RFA_MESSAGE_ACCESSOR_PKG to IWPCUSER1
grant execute on BRANCH_CRITERIA_DATA to IWPCUSER1
grant execute on BRANCH_CRITERIA_DATA_LIST to IWPCUSER1
grant execute on ROE_DATA to IWPCUSER1
grant execute on ROE_DATA_LIST to IWPCUSER1
grant execute on RANK_SET_DATA to IWPCUSER1
grant execute on ROE_LEVEL_DATA to IWPCUSER1
grant execute on ROE_LEVEL_DATA_LIST to IWPCUSER1
grant execute on ROE_CATEGORY_DATA to IWPCUSER1
grant execute on ROE_CATEGORY_DATA_LIST to IWPCUSER1
grant execute on PSYOP_HELPER_PKG to IWPCUSER1
grant execute on CHANGE_MTHD_SUB_MTHD_SET_DATA to IWPCUSER1
grant execute on CHNG_MTHD_SET_DATA_LIST to IWPCUSER1
grant execute on TARGET_GROUP_DATA_LIST to IWPCUSER1
grant execute on DELIVERY_METHOD_DATA to IWPCUSER1
grant execute on DELIVERY_METHOD_DATA_LIST to IWPCUSER1
grant execute on MESSAGE_DATA to IWPCUSER1
grant execute on MESSAGE_DATA_LIST to IWPCUSER1
grant execute on TARGET_GROUP_DATA to IWPCUSER1
grant execute on MESSAGE_GROUP_DATA to IWPCUSER1
grant execute on MESSAGE_GROUP_DATA_LIST to IWPCUSER1
grant execute on CHANGE_METHOD_DATA to IWPCUSER1
grant execute on CHANGE_METHOD_DATA_LIST to IWPCUSER1
grant execute on RFA_INFO_DATA to IWPCUSER1
grant execute on RFA_INFO_DATA_LIST to IWPCUSER1
grant execute on CRITERIA_DATA to IWPCUSER1
grant execute on CRITERIA_DATA_LIST to IWPCUSER1
grant execute on COA_COMPARISON_PAIR_DATA to IWPCUSER1
grant execute on COA_COMPARISON_PAIR_DATA_LIST to IWPCUSER1
grant execute on COA_COMPARISON_SET_DATA to IWPCUSER1
grant execute on COA_INTERFACE_COMPARISON_PKG to IWPCUSER1
grant execute on COA_INTERFACE_PSYOP_PKG to IWPCUSER1
grant execute on COA_DATA_ACCESSOR_PKG to IWPCUSER1
grant execute on RESULTS_DATA to IWPCUSER1
grant execute on RESULTS_DATA_LIST to IWPCUSER1
grant execute on COA_INTERFACE_ROE_PKG to IWPCUSER1
grant execute on BLUE_CAPABILITY_DATA to IWPCUSER1
grant execute on BLUE_CAPABILITY_DATA_LIST to IWPCUSER1
grant execute on TARGET_CATEGORY_DATA to IWPCUSER1
grant execute on TARGET_CATEGORY_DATA_LIST to IWPCUSER1
grant execute on CAEN_LINK_NODE_DATA to IWPCUSER1
grant execute on CAEN_LINK_NODE_DATA_LIST to IWPCUSER1
grant execute on CAEN_NODE_DATA to IWPCUSER1
grant execute on CAEN_NODE_DATA_LIST to IWPCUSER1
grant execute on CAEN_LINK_DATA to IWPCUSER1
grant execute on CAEN_LINK_DATA_LIST to IWPCUSER1
grant execute on CAEN_DATA to IWPCUSER1
grant execute on CAEN_HELPER_PKG to IWPCUSER1
grant execute on SUB_COA_DATA to IWPCUSER1
grant execute on SUB_COA_DATA_LIST to IWPCUSER1
grant execute on COA_CAEN_ACCESSOR_PKG to IWPCUSER1
grant execute on ELEMENT_AND_LOCK_WRAPPER to IWPCUSER1
grant execute on LIST_INFO_DATA to IWPCUSER1
grant execute on LIST_INFO_DATA_LIST to IWPCUSER1
grant execute on LIST_INTERFACE_PKG to IWPCUSER1
grant execute on LIST_ASSET_DATA_HELPER_PKG to IWPCUSER1
grant execute on LIST_TARGET_DATA_HELPER_PKG to IWPCUSER1
grant execute on LIST_HELPER_PKG to IWPCUSER1
grant execute on ASSET_LIST_HELPER_PKG to IWPCUSER1
grant execute on TARGET_LIST_HELPER_PKG to IWPCUSER1
grant execute on LIST_SEQ_PKG to IWPCUSER1
grant execute on COPYSUBCATEGORIES to IWPCUSER1
grant execute on COPYCATEGORYCONTENTS to IWPCUSER1
grant execute on COPYCATEGORY to IWPCUSER1
grant execute on CREATECATEGORY to IWPCUSER1
grant execute on CREATEDATASOURCE to IWPCUSER1
grant execute on CREATEFOLDER to IWPCUSER1
grant execute on CREATELINK to IWPCUSER1
grant execute on CREATEQUERY to IWPCUSER1
grant execute on CREATEREFERENCE to IWPCUSER1
grant execute on CREATEREFNULLDATE to IWPCUSER1
grant execute on CREATEREFSTORAGE to IWPCUSER1
grant execute on CREATETEAM to IWPCUSER1
grant execute on CREATEUSERFOLDER to IWPCUSER1
grant execute on CREATEUSERWORKSPACE to IWPCUSER1
grant execute on CREATEINDICATORSET to IWPCUSER1
grant execute on CREATENOTE to IWPCUSER1
grant execute on CREATENOTEBOOK to IWPCUSER1
grant execute on CREATEPROFILE to IWPCUSER1
grant execute on DELETEDATASOURCE to IWPCUSER1
grant execute on DELETEFOLDERCONTENTS to IWPCUSER1
grant execute on DELETEREFERENCE to IWPCUSER1
grant execute on DELETEFOLDERNOTEBOOKLINKS to IWPCUSER1
grant execute on GETWORKSPACECONTENTS to IWPCUSER1
grant execute on INSERTKEYWORDINFO to IWPCUSER1
grant execute on INSERTLINK to IWPCUSER1
grant execute on INSERTMEMBER to IWPCUSER1
grant execute on INSERTNOTEBOOKTEXT to IWPCUSER1
grant execute on INSERTQUERYDS to IWPCUSER1
grant execute on INSERTQUERYLIB to IWPCUSER1
grant execute on INSERTNOTETEXT to IWPCUSER1
grant execute on INSERTPROFILELIB to IWPCUSER1
grant execute on UPDATEDATASOURCE to IWPCUSER1
grant execute on UPDATEQUERY to IWPCUSER1
grant execute on UPDATETEAM to IWPCUSER1
grant execute on UPDATENOTE to IWPCUSER1
grant execute on UPDATENOTEBOOK to IWPCUSER1
grant execute on UPDATEPROFILE to IWPCUSER1
grant execute on UPDATETEAMFOLDER to IWPCUSER1
grant execute on UPDATEUSERFOLDER to IWPCUSER1
grant execute on UPDATEINDICATORSET to IWPCUSER1
grant execute on DELETEPROFILE to IWPCUSER1
grant execute on DELETEINDICATORSET to IWPCUSER1
grant execute on COPYINDICATORS to IWPCUSER1
grant execute on COPYNOTE to IWPCUSER1
grant execute on COPYNOTEBOOK to IWPCUSER1
grant execute on COPYQUERY to IWPCUSER1
grant execute on COPYREFERENCE to IWPCUSER1
grant execute on COPYFOLDERCONTENTS to IWPCUSER1
grant execute on COPYSUBFOLDERS to IWPCUSER1
grant execute on COPYFOLDER to IWPCUSER1
grant execute on COPYTEAM to IWPCUSER1
grant execute on COPYNOTELINK to IWPCUSER1
grant execute on MOVECATEGORY to IWPCUSER1
grant execute on MOVESUBFOLDERS to IWPCUSER1
grant execute on MOVEFOLDER to IWPCUSER1
grant execute on MOVENOTEBOOK to IWPCUSER1
grant execute on MOVEREFERENCE to IWPCUSER1
grant execute on MOVETEAM to IWPCUSER1
grant execute on VALIDATELEVEL to IWPCUSER1
grant execute on E_PLAN_DATA to IWPCUSER1
grant execute on STRAT_PLAN_DATA to IWPCUSER1
grant execute on COA_DATA to IWPCUSER1
grant execute on AOD_DATA to IWPCUSER1
grant execute on STRAT_PLAN_DATA_LIST to IWPCUSER1
grant execute on COA_DATA_LIST to IWPCUSER1
grant execute on AOD_DATA_LIST to IWPCUSER1
out_execute_loop

Oh I thought you meant I was missing a ';' at the one of the stmt..I was experimenting with that...

even if I use a simple

execute patch_user('iwpcuser1');

I get the same error...
its named different because Im playing with another version....

I think there is some hidden character

database is on your local server?

yes the database is on the local server

i think one of your objects contain some special characters.... in that case that object should be enclosed within double quotations

for testing... lets do this
stmt := 'grant execute on "' || grant_cur.object_name ||'" to ' || grantee;

stmt := 'grant execute on ' || grant_cur.object_name ||' to ' || grantee;
   
 EXECUTE IMMEDIATE stmt;

what I thought was the error was this....

1. since this is a multiple statement block the ; is causing an issue..do I need to use /?

The drop synonym isnt working because I get object already exists at the create...?

Check the table names in the user_objects table.
print the statement before you execute it.  That way you'll know immediately which table gives the error

btw you commented the part where you see what statement was executed last ...

-- Grant select, insert, delete, and update on the tables.
   -- Required for CWT, IPIB, and ACE
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                     WHERE object_type = 'TABLE' )
   LOOP
 
      stmt := 'grant select, insert, delete, update on ' || grant_cur.object_name || ' to '|| grantee;
 
 
   -- swapped lines here
   dbms_output.put_line(stmt);
      execute immediate stmt;
 
   -- end swap lines
 
 
  END LOOP;
 dbms_output.put_line('here');
   -- Grant select on Sequences (required for CNOAT but not the others)

Select allOpen in new window

stmt := 'drop synonym ' || synonym_cur.owner || '.' || synonym_cur.synonym_name;

I need the double quotes here too right??  that is a series of single quotes surronding the double right..

>The drop synonym isnt working because I get object already exists at the create...?

what does this mean? drop will work only if the object exists... why would it give this error?

it is dropping into the create without performing the drop...




Connecting to the database iwpc.
ORA-00955: name is already used by an existing object
ORA-06512: at "IWPCDBA.PATCH_IWPC_USER", line 84
ORA-06512: at line 6
IWPCUSER1
out_execute_loop
here
at drop synonym



 END LOOP;
 dbms_output.put_line('here');
   
     -- Drop synonyms
   FOR synonym_cur IN (SELECT owner,
                              synonym_name
                         FROM all_synonyms
                        WHERE owner = grantee)
   LOOP

      -- Drop the synonyms
      stmt := 'drop synonym ' || synonym_cur.owner || '.' || synonym_cur.synonym_name;
dbms_output.put_line(stmt);
      EXECUTE IMMEDIATE stmt;

   END LOOP;
 dbms_output.put_line('at drop synonym');
   BEGIN

   stmt := 'drop synonym ' || grantee || '.DEFAULT_CAVEAT';

   EXECUTE IMMEDIATE stmt;

   EXCEPTION
      WHEN  OTHERS THEN -- sqlcode = 'ORA-01434' THEN
      NULL;

   END;

   -- Create synonyms
   FOR synonym_cur IN (SELECT object_name
                         FROM user_objects
                        WHERE object_type = 'TABLE'

This is a test database...it already has synonyms...

drop synonyms then.... before creating

stmt := 'drop synonym ' || grantee || '.' || synonym_cur.object_name ;

      EXECUTE IMMEDIATE stmt;

the drop is before the create....

A ok...my fault I wasnt reading my drop statement all the way... ..thanks...

You should accept one of the answer which helped you to solve your problem... dont understand why u r closing it

I accepted two of yours..or at least I tried..

There is an Accept button.. from there you can choose an answer

I did selected accetp multiple solutions and picked two of yours...I thought it went throuhg..let me try again