CODEWITHSUNDEEP
oracle-sqldeveloper
sdowning720
sdowning720

Reputation: 1

Is there a way to protect SQL statements from being altered, while still allowing the person to execute the statement?

I work for a large organization that relies heavily on SQL developer for financial reconciliation. We have only SELECT privileges. Several people have access to the same SQL statements, is there a way to ensure they cant change the code? We need to ensure that people who have access to run our SQL statements to generate a report, do not have the ability to change the code. This forces them to submit change requests if they need the code change, which helps us to create and audit log of the changes made. Our financial audit includes audit of our SQL statements. With too many people making changes it is hard to track/validate the change.

Upvotes: 0

Views: 25

Answers (1)

MT0
MT0

Reputation: 167972

  1. Remove their privileges to SELECT from the tables directly.
  2. Wrap the existing code in a stored procedure (if bind variables are used in the SQL statement then they can be arguments to the stored procedure).
    • This also allows you to put additional code for verification/auditing inside the stored procedure so that it is automatically run with the query(ies) that the users require.
  3. Create a ROLE and grant the EXECUTE privilege on the stored procedure to that role.
  4. Give that role only to the people who are required to run that stored procedure.

Upvotes: 1

Related Questions