Restrict User 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

HOPE THIS WILL WORK