Prevent an Oracle User from using EXP and IMP utility

rollback EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user

sorrry
revoke EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user, grant only specific privileges

@sonicefu: I guess u meant revoked instead of rollback right ?

jinesh_kamdar !
thanks, i mean revoke not revoked, i've already corrected it.

lol

@sonicefu: Thanks for the clarification though i didn't mean to correct you!
I subscribe to the questions that u hv answered here mostly for the intent of learning :)

Though it beats me what dalebetts found so amusing !!!

sonicefu
revoke EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user, grant only specific privileges

this is the current setting i have, i created a specific role for the user

CREATE ROLE "USERNAME_DEFAULT_ROLE"  NOT IDENTIFIED;
GRANT CREATE SESSION TO "USERNAME_DEFAULT_ROLE"
GRANT DELETE ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT EXECUTE ANY PROCEDURE TO "USERNAME_DEFAULT_ROLE"
GRANT INSERT ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT SELECT ANY SEQUENCE TO "USERNAME_DEFAULT_ROLE"
GRANT SELECT ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT UPDATE ANY TABLE TO "USERNAME_DEFAULT_ROLE"
CREATE USER "USERNAME"  PROFILE "DEFAULT"
    IDENTIFIED BY "dbnamei27r3blz" DEFAULT TABLESPACE "USERNAME"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "USERNAME";
GRANT "USERNAME_DEFAULT_ROLE" TO "USERNAME";

The USERNAME can be any user.

I can still export objects from the schema USERNAME using USERNAME user.

What is the output of the following query

SELECT        LPAD ('     ', 2 * LEVEL)
           || granted_role "User, his roles and privileges"
      FROM (SELECT NULL grantee, username granted_role
              FROM dba_users
             WHERE username LIKE UPPER ('%&enter_username%') --<<---- ENTER USER NAME
            UNION
            SELECT grantee, granted_role
              FROM dba_role_privs
            UNION
            SELECT grantee, PRIVILEGE
              FROM dba_sys_privs)
START WITH grantee IS NULL;

Select allOpen in new window

when you grant "CREATE SESSION" Privileges  or "CONNECT" role to a user then he is automatically granted exp/imp rights on his objects.
i think you want to prevent a user to export/import his own object, is it so? if yes then create the following trigger after connecting as a DBA

CREATE OR REPLACE TRIGGER prevent_exp_imp
   AFTER LOGON ON DATABASE
BEGIN
   IF     USER = 'SCOTT'     ----<----user name to restrict
      AND UPPER (SYS_CONTEXT ('userenv', 'module')) IN ('EXP.EXE', 'IMP.EXE')
   THEN
      raise_application_error
                             (-20001,
                              'You are not allowed to export or import data.'
                             );
   END IF;
END;
/

Select allOpen in new window