Lesson 3 | Limiting access to a command |
Objective | Restrict a user's access to a SQL*Plus command. |
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.
Limit Access Syntax
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.
Available commands
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’);
- ALTER
- ANALYZE
- AUDIT
- CREATE
- DELETE
- DROP
- GRANT
- INSERT
- LOCK
- NOAUDIT
- RENAME
- REVOKE
- SELECT
- SET ROLE
- SET TRANSACTION
- TRUNCATE
- UPDATE
You can limit access to the following SQL*Plus commands with the above statement:
- CONNECT
- COPY
- EDIT
- EXECUTE
- EXIT
- GET
- HOST
- QUIT
- PASSWORD
- RUN
- SAVE
- SET
- SPOOL
- START
You can also limit access to the following PL/SQL commands with the above statement:
- DECLARE
- BEGIN
Reasons to limit access
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 users 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 commandsor 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:
- 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.
- 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.
- 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.
- 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.