Establishing Management Standards

As we can see, the DBA has limited control over the origin of SQL, especially in a client/server environment. Given this reality, the DBA must encourage procedures for SQL standardization as well as disseminate SQL techniques to the development staff.

SELECT CHIPS, POP FROM FRIDGE 
WHERE CHIPS = 'FRITOS' AND POP = 'PEPSI'

Of course, it is one thing to impose standards, but quite another for DBAs to force the developers to adhere to the DBA rules. Here are some common methods that are used by the DBA staff to prohibit unauthorized SQL:

1. Cut off SQL*Plus access by changing the product_user_profile table. Oracle allows the DBA to restrict access to the UNIX SQL*Plus utility by making entries in SYS.PRODUCT_USER_PROFILE.
2. Grant only the GRANT EXECUTE privilege with no other access rights. In this fashion, the only way to allow the developers access to the data is by executing your stored procedure. With GRANT EXECUTE, the invoker will temporarily have the same access privileges as the creator of the stored procedure, but only while they are executing the stored procedure.

In the next lesson, you will learn techniques to assist with managing developers.

Before going on to the next lesson, click the link below to read about how to enforce SQL standards.
Oracle SQL Standards