1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Revoke Object Privilege( like delete) from "SCOTT" USER

Discussion in 'SQL PL/SQL' started by san2010lather, Dec 10, 2011.

  1. Hi friends ,,
    i want to revoke object privilege( like delete) from "SCOTT" USER.... so that scott user is enable to delete row from any table in its own tables.
    please reply me
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    Then you need to revoke delete on every table SCOTT owns from SCOTT. The following code will generate such a script:

    select 'revoke delete on '||table_name||' from SCOTT;'
    from dba_tables
    where owner = 'SCOTT';

    You will need to run this from a DBA-privileged account then run the resulting commands from that same DBA account.

    Know that if SCOTT creates any new tables this same process will need to be repeated. Know also that this may not work as Oracle may not allow a user who can create tables to be unable to delete from them.