We help IT Professionals succeed at work.

Prevent an Oracle User from using EXP and IMP utility

toneebhoi
toneebhoi asked
on
Is it possible to restrict an Oracle User to use EXP and IMP?  Is there a database object that can be used for revoking access.
Comment
Watch Question

Commented:
rollback EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user

Commented:
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 ?

Commented:
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 !!!

Author

Commented:
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.

Commented:
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;

Open in new window

Commented:
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;
/

Open in new window

PAQed with no points refunded (of 250)

Computer101
EE Admin

Explore More ContentExplore courses, solutions, and other research materials related to this topic.