Replacement for Pragma Restrict references

Question

Up to Oracle 11.1 you could mark a package as only reading from the database by adding PRAGMA RESTRICT_REFERENCES( DEFAULT, WNDS);

This has now been deprecated. I would like to enforce read only on some of the packages.

• it would help ensure that junior developers don't add new functionality that writes 'just because'
• it makes granting execute on read only packages to report users cleaner

Is there any substitute that can enforce that a package only reads from the database?

Answer 1

I have several poor answers. I hope one of them works for you.

1. Moving the packages to another schema may be an option. You might then grant that other schema a bare minimum set of privileges. This would get ugly for name resolution, though.

2. Auditing the code by searching the schema might be another option:

   SELECT * FROM dba_source
   WHERE schema = 'ME'
     AND name LIKE 'MYPROC'
     AND (UPPER(text) LIKE '%UPDATE %'
          OR UPPER(text) LIKE '%INSERT %'
          OR UPPER(text) LIKE '%DELETE %');
   

3. Defining the package as AUTHID CURRENT_USER would scale back the permissions at runtime to the calling user's permissions. This solution has its own can of worms.

Answer 2

No, there is no direct replacement for PRAGMA RESTRICT REFERENCES… WNDS, however, the feature has not been removed, only deprecated.

Looking further though, your question assumes that these developers can’t be trusted to not add write commands, but can be trusted not to remove the PRAGMA or create another package without it. Although your question is legitimate on a technical level, your real problem seems to be a code review and/or management issue. Here are some recommendations that could help with that.

• Make the read only purpose of these packages clear by creating a naming convention that indicates they are or as durette suggested, move them to a different schema. (I like the different schema approach).
• As part of the ongoing training of these junior developers, make it clear which packages should be treated as read only.
• Have code written by these developers reviewed by a more trusted developer. (This is a good idea for all code for all developers). The query durette suggested could be one tool used in the technical review process.
• Have management provide for increased code scrutiny and remediation when junior developers violate your development standards.