Pages

Monday, August 9, 2010

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





1 comment:

  1. Hello,
    instead of (SELECT DISTINCT SID FROM V$MYSTAT) I suggest ( SELECT sid FROM v$mystat WHERE rownum = 1 )

    ReplyDelete