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

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
DBA Tips Data Pump Reference

1 comments:

Pankaj Khanna on 24 June 2011 at 21:27 said...

awesome..

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google