How to make tables unavailable for users during certain times in Oracle

oracleplsql

Iam a student and one of the questions i am stuck is to implement an IT management rule on all tables in oracle in my own schema using pl/sql.I dont have DBA previlege and the requirement is that on fridays from 08:00-09:00 none of the tables should be available and should display message saying its maintainance time. so basically I have prevent myself from accessing the tables in my schema during those times. I have used the following DML trigger for insert,update and delete

create or replace TRIGGER test_trig

 BEFORE INSERT OR UPDATE OR DELETE ON employees
 FOR EACH ROW
 WHEN (TO_CHAR (SYSDATE, 'DY')='THU' AND
(TO_CHAR(SYSDATE, 'HH24:MI'))BETWEEN '20:25' AND '20:30')

DECLARE
 e_invalid_time EXCEPTION;

BEGIN
 //DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again 
later'); 
RAISE_APPLICATION_ERROR(-20500, 'You may insert'
||' into EMPLOYEES table only during '
||' normal business hours.');

EXCEPTION
WHEN e_invalid_time THEN
DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again 
later');
 END test_trig;

But this will only be effective on one table. And SELECT queries will be still performed.I have about 6 tables
I read Fine grain auditing will help with select and all DML commands. what is the bet approach?This is for educational purposes.

Best Answer

You can put the database in a Quiesced State (Oracle Database Administrator's Guide)

Restrict access. Only DBAs will be able to access the DB (i.e. user SYS or SYSTEM):

ALTER SYSTEM QUIESCE RESTRICTED;

Restore the database to normal operation:

ALTER SYSTEM UNQUIESCE;

Related Solutions

Oracle – list users with access to certain tables

List all users who have been assigned a particular role

-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'

List all roles given to a user

-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';

List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

List which tables a certain role gives SELECT access to?

-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

List all tables a user can SELECT from?

--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME' 
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

In Oracle, will certain users only be able to see certain data in the privileges tables

The table_privileges view is only preserved for compatibility with ancient versions of Oracle.

You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).

Best Answer

Related Solutions

Oracle – list users with access to certain tables

In Oracle, will certain users only be able to see certain data in the privileges tables

Related Question