• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

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.
0
toneebhoi
Asked:
toneebhoi
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now