0

Is there any possible way to prevent dml operations for a specific user schema is busy traffic hours without impacting other user schema's dml operations

Watson Ferror
  • 21
  • 1
  • 5
  • 2
    You could lock the account and kill any existing sessions during "busy hours" (or revoke the `create session` privilege rather than locking the account). That could be automated but it would be a rather crude solution. Are you sure that you want to truly prevent the user from logging in rather than limiting their ability to issue expensive queries via a profile or lowering their priority for resources using Resource Manager? – Justin Cave Mar 04 '15 at 20:34
  • Thanks Justin I wanted to limit ability for a specified user schema ,I thought of triggers but un sure how to control it over a specified time stamp and allow normal access later on – Watson Ferror Mar 04 '15 at 21:47
  • Limit or prevent? A job (`dbms_job`, `dbms_scheduler`, `cron`, etc.) could run at a set time and revoke the `create session` privilege, lock the account, and/or kill the existing sessions. Using a profile or using Resource Manager would be more elegant by limiting the resources the user could consume without preventing them from logging in at all. – Justin Cave Mar 04 '15 at 22:19
  • Thanks @JustinCave, read a few definitions of revoking 'create session', seems if i revoke him for a specified time then, it will prevent the schema from establishing a DB connection..so thats a _Prevent_.. and i guess to stick on to _Limit_ i should be going on with Resource Manager right? – Watson Ferror Mar 04 '15 at 22:39

1 Answers1

0

Depending on who has grants to insert/update/delete/execute on that user's objects it could be as easy as

ALTER USER myuser ACCOUNT LOCK;
ALTER USER myuser PASSWORD EXPIRE;

and then unlock and unexpire once you're done.

If other users have access to that schema then I don't think there is a simple way.

kevinskio
  • 4,431
  • 1
  • 22
  • 36