DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: restrict drop command

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi all,
    Can someone please let me know how I can stop a user from executing the "Drop" command? examples are much appreciated!

    Thanks,
    Leonard905
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Aug 2001
    Location
    Hyderabad, India
    Posts
    29

    Smile

    REVOKE DROP ANY TABLE
    FROM bill, mary;

    Here bill and mary are users, It will restricts users droping the objects other than their own schema.

    REVOKE DROP TABLE
    FROM scott;

    Here scott is the user. He can not drop any object from the database.
    Venkateshwarlu.K

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    U can disable the drop command from the SQL*Plus.There is a table called PRODUCT_USER_PROFILE in system user schema.If the table is not there u can run the pupbld.sql script in system user to create that table.
    once the table is created u can insert the following row which will disable the DROP command for that user.

    sql>INSERT into product_user_profile
    (product,userid,attribute,char_value,date_value)
    values('SQL*Plus','SCOTT','DROP','DISABLED',NULL);
    sql> commit;

    So when user SCOTT logs in through SQL*Plus and enter the following command:
    sql>drop table emp;
    he will receive the following error
    SP2-0544: invalid command: drop

    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width