Granting privileges to dbms_crypto

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers. Test was for user acceptance testing before stuff migrated from dev to prod; 'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.

The error you're getting indicates that the User1 user does not have access to the dbms_job package. You'd need a DBA to

GRANT EXECUTE ON dbms_job
   TO user1;

If you create the trigger and procedure to be owned by a user other than the one that owns the table, you'd need to include the schema name in your GRANT statement inside the procedure.

EXECUTE IMMEDIATE 
  'grant select, insert, update, delete on user1.' || p_table_name || 
  '   to MY_USER_ROLE';

Additionally, you'll want to make sure that your job_queue_processes initialization parameter is set to something greater than 0 to ensure that jobs will actually run.