Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 5 May 2012

Restrict Access of Some Users or Program in Oracle Database

Check user profile and change its limit ?
SELECT * FROM dba_users
WHERE USERNAME='HRMS';
or
SELECT * FROM dba_profiles
where profile='DEFAULT';

select * from profile$   --to check profile attribute
select * from profname$  --to check profile names
SELECT * from  user$;
SELECT name, lcount FROM user$
WHERE lcount <> 0;
Oracle stores the history of passwords for a user in user_history$.
select * from user_history$;
To change profile limit:
ALTER PROFILE default limit
failed_login_attempts UNLIMITED;

for more information related to profile limitation, you can visit this link:
http://psoug.org/reference/profiles.html

You can use this trigger to restrict the user from perticular program to use particualr schema
into the oracle database. My personal suggestion is do not directly use on production environment.
Take it as a practice environment only.

CREATE OR REPLACE TRIGGER programe_restrict AFTER LOGON ON DATABASE
BEGIN
FOR p IN (SELECT username, program FROM SYS.v_$session
WHERE audsid = USERENV ('sessionid'))
LOOP
IF LTRIM (RTRIM (p.username)) = 'HRMS'
AND LTRIM (RTRIM (p.program)) IN ('sqlplusw.exe', 'Toad.exe')
THEN
raise_application_error
(-20999,'You have no authorization to use in this Environment');
END IF;
END LOOP;
END programe_restrict;
/

0 comments:

Post a Comment