Database Administrator: user management

Showing posts with label user management. Show all posts

Saturday, July 27, 2013

Grant role access to all objects in a schema to another user

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('PROCEDURE','PACKAGE','FUNCTION') )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('TABLE') )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('SEQUENCE') )
LOOP
EXECUTE IMMEDIATE 'SELECT ON '||i.object_name ||' TO kshitij';
END LOOP;
END;
/

Thursday, August 23, 2012

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;

Wednesday, August 8, 2012

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 ….