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!

How to Revoke 'ANY' priviliges being granted to Non-DBA users

Discussion in 'SQL PL/SQL' started by laxman, Jul 26, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    I need to be revoked all 'ANY' privileges that have been granted to all non-DBA user or Role in the database.
    To achieve this what i assume is
    1> i need to find out Role as well all user who are non-DBA
    2> For all non-DBA user i need to find out and revoked 'ANY' privileges if they would have.

    Here i need some information about all tables related to privilieges and non-DBA users
    The below are some example as a reference.

    USER Privileges
    ------------ ----------------------
    CTXADMIN SELECT ANY TABLE

    PUBLIC MERGE ANY VIEW

    LAXORA ANALYZE ANY
    EXECUTE ANY PROCEDURE
    GRANT ANY ROLE
    INSERT ANY TABLE
    SELECT ANY TABLE

    LAX_NEW ANALYZE ANY
    CREATE ANY TABLE
    DELETE ANY TABLE
    DROP ANY TABLE
    INSERT ANY TABLE
    SELECT ANY TABLE

    USER_NEW SELECT ANY TABLE

    Thank n reagrds
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    1) Yes but take care you do not change any Oracle supplied roles as functionality may be lost.

    2) Yes, provided they do not actually need such privileges.