Restrict Access of Some Users or Program in Oracle Database

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;
/