synonym - after dba privileges user is not able to access the option. only schema owner can access

create or replace trigger logon_trg
   after logon on database
 begin
    if user in ('A','B') then
        execute immediate 'alter session set current_schema=X';
    end if;
 end;
/
----------------------

create or replace trigger logon_trg
   after logon on database
declare
usr varchar2(1000);
 begin
select wm_concat(''''|| username || '''') into usr from dba_users;
    if user in (usr) then
        execute immediate 'alter session set current_schema=LDBO';
    end if;
 end;
/

SYS@NBS11G> select wm_concat(''''|| username || '''') from dba_users;
select wm_concat(''''|| username || '''') from dba_users
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30

RESTRICT NON DBA USER TO ACCESS DEVELOPMENT TOOLS LIKE SQLPLUS , TOAD,...

Create or Replace TRIGGER TG_OTHER_TOOL
  v_prog sys.v_$session.program%TYPE;
  v_module sys.v_$session.program%TYPE ;
lnUsercode number ;
lcUserdetails Varchar2(100)  ;
lnTotalrecords number ;
Begin
  Select program,module into v_prog,v_module From sys.v_$session
      Where  audsid = USERENV('SESSIONID')
    and  audsid != 0  -- Don't Check SYS Connections
    and  rownum = 1;  -- Parallel processes will have the same AUDSID's
  Select Count(*) into lnTotalrecords From Dba_Users Dba_Users
  Where Dba_Users.Username=User and Dba_Users.Default_tablespace='USR' ;
  If lnTotalrecords!=0 then
      IF UPPER(V_MODULE) NOT IN ('LD.EXE','LDSCHEDULER.EXE','LDSCHEDULER.VSHOST.EXE','DBMS_SCHEDULER','DLLHOST.EXE')
     Then
        Raise_application_error(-20000,'On Database Development tools are Restricted');
     end if;
  end if ;
  lnUsercode:=-1;
  If ((upper(v_prog)='LD.EXE' and upper(v_module)='LD.EXE') or
      (upper(v_prog)='LDSCHEDULER.EXE' and upper(v_module)='LDSCHEDULER.EXE') or
      (upper(v_prog)='LDSCHEDULER.VSHOST.EXE' and upper(v_module)='LDSCHEDULER.VSHOST.EXE') or
      (upper(v_prog)='ORACLE.EXE (J000)' and upper(v_module)='DBMS_SCHEDULER') or
      (upper(v_prog)='DBMS_SCHEDULER' and upper(v_module)='DBMS_SCHEDULER') or
      (upper(v_prog)='DLLHOST.EXE' and upper(v_module)='DLLHOST.EXE')) then
     lcUserdetails:=sys_context('userenv','ip_address')  ;
     Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
  else
     lcUserdetails:='USER LOGGED ON' ;
     Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
  end if ;  
  Commit ;
  Exception
      When NO_DATA_FOUND then
           NULL ;
End;
/

 

if Non DBA user is trying to login into sqlplusw / PlSQL developer or some other tool then it shows error because of splogininformation,tglogin.

if i rename sqlplusw.exe to abc.exe then non DBA user can login into sqlplusw.exe

i think you check v$session.program in SP for that.

you have to check v$session.module.

select program,module from v$session;

abc.exe   SQL*Plus

module shows currently executing module

program shows operating system program name

 

The MODULE column of V$SESSION only gets populated with the DBMS_APPICATION_INFO package. 

SQL*Plus automatically uses this package and registers the application with the database. 

This is why you can see it in the MODULE column of V$SESSION. Unfortunately, this won't happen until near the end of the SQL*Plus initialization. 

You can not set_module before logging. For obvious reason - you are not logged

http://docs.oracle.com/cd/A58617_01/server.804/a53717/ape.htm

PRODUCT_USER_PROFILE is owned by SYSTEM not SYS.

PRODUCT_USER_PROFILE is only work for SQLPLUSW not for other tool

One alternative is to make use of PRODUCT_USER_PROFILE table to restrict the nondba users from firing any command after logging in.

e.g to disallow nondba users from selecting any data from SQL * plus prompt, log in as system and 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','SELECT','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','UPDATE','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','DELETE','DISABLED'); 

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','INSERT','DISABLED'); 

commit; 

Restrict Users to access other application exe

create or replace procedure sp_ltmlogin AS
  MODULE SYS.V_$SESSION.MODULE%TYPE ;
L_USER NUMBER ;
BEGIN
 SELECT MODULE INTO MODULE FROM SYS.V_$SESSION WHERE  AUDSID = USERENV('SESSIONID') AND  AUDSID != 0 AND  ROWNUM = 1;
 SELECT COUNT(*) INTO L_USER FROM DBA_USERS DBA_USERS WHERE DBA_USERS.USERNAME=USER AND DBA_USERS.PROFILE='LTM';
  IF L_USER=0 THEN
     IF UPPER(MODULE) ='LDLIVETRADEMONITOR.EXE'
     THEN
        RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LTM software');
     END IF;
  END IF ;
  IF L_USER > 0 THEN
     IF UPPER(MODULE) ='LD.EXE'
     THEN
        RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LD software');
     END IF;
  END IF ;

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
           NULL ;
END;
/

create or replace

trigger tg_ltmlogin

      after logon on Database

Begin

    sp_ltmlogin() ;

End;

/

To restrict user to login only LTM user, you have to create a LTM profile and move that users to LTM profile

Followings are the steps to implement the same

1     Connect to sysdba user

    Create profile for LTM users and move them to LTM profile

CREATE PROFILE LTM

  LIMIT PASSWORD_REUSE_MAX DEFAULT

        PASSWORD_REUSE_TIME DEFAULT;

ALTER USER LTM PROFILE LTM;

      

Two User Logon Not Allowed on same Machine

create or replace trigger TG_SINGLELOGIN
after logon on database
declare
cnt pls_integer;
begin
select count(*) into cnt from v$session
where username !=sys_context('userenv','session_user')  and terminal =sys_context('userenv','terminal');
if cnt >= 1 then
raise_application_error (-20001, 'You are already connected on another user with this machine');
end if;
exception
when no_data_found then raise;
end;
/

Concurrent User Login Denied from Different Machine

create or replace trigger TG_MULTILOGIN
after logon on database
declare
cnt pls_integer;
begin
select count(*) into cnt from v$session
where username=sys_context('userenv','session_user')  and terminal !=sys_context('userenv','terminal') and status like '%ACTIVE%';
if cnt >= 1 then
raise_application_error (-20001, 'You are already connected on another machine');
end if;
exception
when no_data_found then raise;
end;
/

RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon'); 

wont work for a  user who has ADMINISTER DATABASE TRIGGER privilege. 

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe. If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

if someone is exit from oracle without logout then record is still present in v$session then  error occur.

we can use status like '%ACTIVE%'; to avoid killed and sniped session

User Machine Specific Access

suppose you have five machines and five users ( one user per machine)

Note: these users are database users...

e.g

User name	Machine name
User1	WORKGROUP\PC-01
User2	WORKGROUP\PC-02
User3	WORKGROUP\PC-03
User4	WORKGROUP\PC-04
User5	WORKGROUP\PC-05

So according to above table ye create a table in our database as sys user

Create table user_record

(user_name               varchar2(25),

Machine_name         varhcar2(30));

now insert user info ….

Insert into user_record (user_name,machine_name)

Values (‘USER1’,’ WORKGROUP\PC-01’);

Insert into user_record (user_name,machine_name)

Values (‘USER2’,’ WORKGROUP\PC-02’);

Insert into user_record (user_name,machine_name)

Values (‘USER3’,’ WORKGROUP\PC-03’);

Insert into user_record (user_name,machine_name)

Values (‘USER4’,’ WORKGROUP\PC-04’);

Insert into user_record (user_name,machine_name)

Values (‘USER4’,’ WORKGROUP\PC-04’);

Insert into user_record (user_name,machine_name)

Values (‘USER5’,’ WORKGROUP\PC-05’);

COMMIT;

NOW CREATE AFTER LOGON ON DATABASE TRIGGER AS SYS..

 *******************************************************************************************************

CREATE OR REPLACE TRIGGER USER_CHECK

  AFTER LOGON ON DATABASE

  DECLARE

 U_NAME       VARCHAR2(20);

  M_NAME     VARCHAR2(20);

BEGIN

 SELECT USER_NAME, MACHINE_NAME INTO U_NAME,M_NAME

 FROM USER_RECORD

 WHERE (USER_NAME, MACHINE_NAME) IN

    (SELECT USERNAME, MACHINE FROM V$SESSION WHERE SID=(SELECT DISTINCT SID FROM

V$MYSTAT));

  EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE_APPLICATION_ERROR(-20000,’YOU ARE NOT AUTHRIZE TO LOGIN FROM THIS MACHINE’);

 END;

 *******************************************************************************************************

this will connect 

user1 from WORKGROUP\PC-01 only 

user2 from WORKGROUP\PC-02 only

user3 from WORKGROUP\PC-03 only

user4 from WORKGROUP\PC-04 only

user5 from WORKGROUP\PC-05 only

Database Hardening

Following are the general guidelines used for DB hardening:

Complete server hardening checklist. Ideally, run on latest supported version (or at least a supported version) of the Operating System.

Use the latest generation of database server.

Install the latest vendor-provided patches for the database. Be sure to include patches for database support software that isn’t directly bundled with the database.

Remove default usernames and passwords

Manually reviews installed stored procedures and delete those that aren’t going to be used. In many cases, most or all stored procedures can be deleted.

Where possible, isolate sensitive databases to their own servers. Databases containing Personally Identifiable Information, or otherwise sensitive data should be

protected from the Internet by a network firewall, and administrative/DBA access should be limited to as few individuals as possible.

Ensure that application access to the database is limited to the minimal access necessary. For example, reporting applications that just require read-only access should be appropriately limited.

Manually validate that logging of successful and failed authentication attempts is working.

Use complex names for database users. Use especially complex passwords for these users.

Create alternative administrative users for each DBA, rather than allowing multiple individual users to regularly use the default administrative account.