User Profiles  «Prev  Next»

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:
  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

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.