Restrict the commands (SELECT DELETE UPDATE) in SQL*PLUS for a particular user

advertisements

_____________________________________________________________________________________________________________________

DBAs can restrict the commands like select, delete, update etc. in SQL*Plus by adding one row in PRODUCT_PROFILE table. Each row in the table is explains which command is blocked for which user. It can be re-enabled by removing the corresponding row from the table.

These are the list of Commands which can be disabled.

CONNECT, ALTER, BEGIN, COPY, ANALYZE, DECLARE, EDIT

AUDIT, EXECUTE, CREATE, EXIT, DELETE, GET, DROP, HOST, GRANT

QUIT, INSERT, PASSWORD, LOCK, RUN, NOAUDIT, SAVE, RENAME

SET, REVOKE, SPOOL, SELECT, START, SET ROLE, SET TRANSACTION,

TRUNCATE, UPDATE

Here is the PRODUCT_PROFILE insert command.

INSERT INTO product_profile

(product, userid, attribute, char_value)

VALUES ('SQL*Plus','USER','COMMAND','DISABLED');

Where

SQL*Plus is the product needs to be disabled

USER is the username from which the command to be restricted

COMMAND is the command (select, update, etc) to be disabled from the user

Example

          Disable SELECT on SCOTT user in SQL*PLUS

          Step 1. Connect to sqlplus as sys and execute

SQL> INSERT INTO product_profile

    (product, userid, attribute, char_value)

    VALUES ('SQL*Plus','SCOTT','SELECT','DISABLED');

1 row created.

SQL> COMMIT;

Commit complete.

Step 2. Connect to SCOTT USER and Check whether it is disabled or not?

SQL> show user

USER is "SCOTT"

SQL> select 1 from dual;

SP2-0544: Command "select" disabled in Product User Profile

_____________________________________________________________________________________________________________________

Website Stats