| How to Restrict User from Connecting to Database Through Specific hostname [message #619315] |
Mon, 21 July 2014 07:09  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
Client requirement is to restrict user from Connecting to Database Through specific user HOST & DB User.
Below is my trigger -
CREATE OR REPLACE TRIGGER system.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
begin
select sys_context ('USERENV', 'OS_USER') into OSUSER from dual;
select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;
if sys_context('USERENV','SESSION_USER') in ('ASHISH','SCOTT','ASHISH2') and sys_context('USERENV', 'HOST') in ('ER02DSK40615002')
then
raise_application_error(-20022,'Denied! You are not allowed to logon from host' );
end if;
end;
/
But when i logged on as SCOTT, ASHISH, system allowed to enter instead of firing trigger.
if I comment
and sys_context('USERENV', 'HOST') in ('ER02DSK40615002')
trigger fire .
Please assist me to resolve this issue.
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to Restrict User from Connecting to Database Through Specific hostname [message #619683 is a reply to message #619677] |
Thu, 24 July 2014 06:16  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Yes Lalit.
CREATE OR REPLACE TRIGGER system.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
begin
select sys_context ('USERENV', 'OS_USER') into OSUSER from dual;
select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;
if sys_context('USERENV','SESSION_USER') in ('ASHISH','SCOTT','ASHISH2') and sys_context('USERENV', 'HOST') in ('<comp. name>\ER02DSK40615002')
then
raise_application_error(-20022,'Denied! You are not allowed to logon from host' );
end if;
end;
/
Above trigger is okay, as I was checking for the same server. And it's working.
Now, I want to restrict by the same trigger to restrict remote session (hostname) for the specific username. As database is connecting without trigger gets fired.
Below is the host name -
'<comp. name>\ER03DSK40614266'
Regards,
Ashish
|
|
|
|
|
|
| Re: How to Restrict User from Connecting to Database Through Specific hostname [message #619688 is a reply to message #619686] |
Thu, 24 July 2014 07:43 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
On hostname (<comp.name>\ER02DSK40615002), database is installed. Requirement is to restrict hostnames with specified username. eg
<comp.name>\ER02DSK40615002 >> SCOTT, ASHISH
<comp. name>\ER03DSK40614266 >> ASHISH, ASHISH2
In many machine, pl/sql developer is installed. We want to restrict specified database schemas from the trigger.
Trigger is as below -
CREATE OR REPLACE TRIGGER system.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
begin
select sys_context ('USERENV', 'OS_USER') into OSUSER from dual;
select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;
if sys_context('USERENV','SESSION_USER') in ('SCOTT','ASHISH2')
and sys_context('USERENV', 'HOST') in ('CMCINDIA\ER02DSK40615002') then
raise_application_error(-20022,'Denied! You are not allowed to logon from host' );
elsif sys_context ('USERENV','SESSION_USER') in ('ASHISH','ASHISH2')
and sys_context ('USERENV', 'HOST') in ('CMCINDIA\ER03DSK40614266')
then
raise_application_error(-20022,'Denied! You are not allowed to logon from host' );
end if;
end;
/
Hope, this time, I am able to clear you all.
Regards,
Ashish
|
|
|
|
|
|
| Re: How to Restrict User from Connecting to Database Through Specific hostname [message #619692 is a reply to message #619688] |
Thu, 24 July 2014 07:55 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashishkumarmahanta80 wrote on Thu, 24 July 2014 18:13We want to restrict specified database schemas from the trigger.
Why use trigger for this? I asked you if you completely want to restrict the user from accessing the database, you replied yes. Now you say you want to restrict the user from accessing specified schema. Then why to grant such privilege to the user at all? Can't you revoke the privileges already granted.
|
|
|
|
| Re: How to Restrict User from Connecting to Database Through Specific hostname [message #619781 is a reply to message #619692] |
Fri, 25 July 2014 01:06 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear EdStevens,
Let me explain more clearly -
User A or B is not the major concern. Combination of user A or B & Machine "M" is major concern. If user A or B is using machine 'M", he should not be logged in by database user "SCOTT" or " ASHISH2". When user A or B is using machine "N", should not be logged in database user "ASHISH".
Dear Lalit,
I cannot revoke the privileges from the database users(SCHEMAS). Restriction is combinations of hostname & database users(schemas).
Hope, able to make you clear.
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
| Re: How to Restrict User from Connecting to Database Through Specific hostname [message #619859 is a reply to message #619818] |
Fri, 25 July 2014 10:20 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
"Not working" is not an Oracle message.
Obviously your test is wrong, taking what your words:
Quote: If user A or B is using machine 'M", he should not be logged in by database user "SCOTT" or " ASHISH2".
IF <user> IN ('SCOTT','ASHISH2) AND <machine> = 'M' THEN error
Quote: When user A or B is using machine "N", should not be logged in database user "ASHISH".
IF <user> = 'ASHISH' AND <machine> = 'N' THEN error
Combining both:
IF ( <user> IN ('SCOTT','ASHISH2) AND <machine> = 'M' )
OR ( <user> = 'ASHISH' AND <machine> = 'N' )
THEN error
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
