Limiting access to SQL commands in Oracle

The product profile can be used to restrict access to a particular SQL*Plus command. You would want to limit access to a particular command to make SQL*Plus available to a user community in a limited form. For instance, you may have a reason to allow an application user to delete a row or drop a table, but you do not want to allow the same user this type of power when he or she is outside the confines of the application system. Or, because an application will normally never issue an administrative command like ANALYZE or AUDIT, you may not have bothered to prevent an application user from executing the command, but the user might intentionally or unintentionally try to run it in SQL*Plus.

As explained in the previous lesson, the product profile information is contained in the PRODUCT_PROFILE table in the SYSTEM schema. To limit access to a particular command, use the SQL statement

INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, userid, command_name, ‘DISABLED’);

where userid is a character string representing the user ID for the user you wish to restrict and command_name is a character string representing the name of the command you wish to limit. Because userid and command_name are character strings, you must enclose them in single quotes, as you do for all Oracle character strings. You must issue this command while logged on as SYSTEM.

You can limit access to the following SQL commands using the statement:

INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, userid, command_name, ‘DISABLED’);

1. ALTER
2. ANALYZE
3. AUDIT
4. CREATE
5. DELETE
6. DROP
7. GRANT
8. INSERT
9. LOCK
10. NOAUDIT
11. RENAME
12. REVOKE
13. SELECT
14. SET ROLE
15. SET TRANSACTION
16. TRUNCATE
17. UPDATE

You can limit access to the following SQL*Plus commands with the above statement:

1. CONNECT
2. COPY
3. EDIT
4. EXECUTE
5. EXIT
6. GET
7. HOST
8. QUIT
9. PASSWORD
10. RUN
11. SAVE
12. SET
13. SPOOL
14. START

You can also limit access to the following PL/SQL commands with the above statement:

1. DECLARE
2. BEGIN

There are some special reasons why you might want to limit access to PL/SQL from SQL*Plus.

Remember, most SQL statements can be executed from the context of a PL/SQL block, and PL/SQL blocks can be executed from SQL*Plus. Therefore, if you limit a user’s access to a particular SQL command but allow the user access to the BEGIN, DECLARE, and EXECUTE commands, a determined user could simply couch a SQL statement in the context of a PL/SQL block. If you are limiting access to any SQL commands, you should also limit access to these PL/SQL commands–or else you are depending on the lack of sophistication of your user for your security.

Question: For which reasons might you want to limit access to PL/SQL from SQL*Plus?
There are several reasons why you might want to limit access to PL/SQL from SQL*Plus:

1. Security: PL/SQL code can access sensitive information and perform potentially destructive operations. Limiting access to PL/SQL from SQL*Plus can help to prevent unauthorized access or modification of data by users who do not have the appropriate privileges.
2. Performance: PL/SQL code can be resource-intensive, particularly when executing complex queries or operations. Limiting access to PL/SQL from SQL*Plus can help to prevent performance degradation caused by excessive PL/SQL execution.
3. Control: Limiting access to PL/SQL from SQL*Plus can help to ensure that PL/SQL code is executed only in controlled and monitored environments, where it can be properly tested and verified before deployment.
4. Data integrity: PL/SQL code can potentially modify data in ways that are not intended or expected, leading to data corruption or inconsistency. Limiting access to PL/SQL from SQL*Plus can help to ensure that data modifications are performed only through controlled interfaces, such as stored procedures or application code.

Overall, limiting access to PL/SQL from SQL*Plus can help to improve the security, performance, control, and data integrity of a database system, by restricting the execution of potentially sensitive or resource-intensive PL/SQL code to authorized users and controlled environments.
In the next lesson, you will learn how to disable certain roles for a SQL*Plus user.