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 with the above statement:
- 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.
In the next lesson, you will learn how to disable certain roles for a SQL*Plus user.