Prevent an Oracle User from using EXP and IMP utility

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.
toneebhoiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:
rollback EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user
0
sonicefuCommented:
sorrry
revoke EXP_FULL_DATABASE and IMP_FULL_DATABASE and DBA role from that user, grant only specific privileges
0
Jinesh KamdarCommented:
@sonicefu: I guess u meant revoked instead of rollback right ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sonicefuCommented:
jinesh_kamdar !
thanks, i mean revoke not revoked, i've already corrected it.
0
dalebettsCommented:
lol
0
Jinesh KamdarCommented:
@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 :)
0
Jinesh KamdarCommented:
Though it beats me what dalebetts found so amusing !!!
0
toneebhoiAuthor 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.
0
sonicefuCommented:
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

0
sonicefuCommented:
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

0
Computer101Commented:
PAQed with no points refunded (of 250)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.