No question at this time
DBA Top 10
1 M. Cadot 22600
2 A. Kavsek 15200
3 M. Hidayathullah ... 11000
4 B. Vroman 8100
5 P. Wisse 6000
6 T. Boles 5200
7 J. Schnackenberg 3300
8 G. Lambregts 2200
8 P. Knibbs 2200
8 K. Pagadala 2200
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48017
Total active users1850
Act. users last 24h5
Act. users last hour0
Registered user hits last week339
Registered user hits last month1183
Go up

user restriction or control
Next thread: gcc version for Oracle 11.2.0.4 on Linux 7
Prev thread: Oracle sqlplus scrolling interruption

Message Score Author Date
Hi Guys I need a script to prevent anyone to lo...... Tso P Feb 10, 2017, 09:53
I don't think this is possible. Period. And eve...... Jan Schnackenberg Feb 10, 2017, 10:06
Just create a logon trigger and verify SYS_CONTE...... Michel Cadot Feb 10, 2017, 10:11
Thanks Jan All I am saying is I don't want anyo...... Tso P Feb 10, 2017, 10:14
Hi Tso Ah, you want to prevent SSH-logons to th...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Jan Schnackenberg Feb 10, 2017, 10:34
Thanks Jan Will this not affect the scheduled j...... Tso P Feb 10, 2017, 10:43
Thanks Michel I am referring to the operating s...... Tso P Feb 10, 2017, 10:44
Hi Tso Well, if you have scripts, that need to ...... Jan Schnackenberg Feb 10, 2017, 14:52
>>> <i> I am referring to the operating system u...... Michel Cadot Feb 10, 2017, 17:34
Hi Tso, as Michel has suggested; you can define...... Bruno Vroman Feb 12, 2017, 17:50
>>> <i> Note that this trigger will not preven...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Feb 12, 2017, 18:10

Follow up by mail Click here


Subject: user restriction or control
Author: Tso P, South Africa
Date: Feb 10, 2017, 09:53, 190 days ago
Os info: rhel5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Message: Hi Guys

I need a script to prevent anyone to login to the database server with OS oracle user...

I need people to su to their individual users to login to the server...

Thanks in advance...

Please Help!!!
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Jan Schnackenberg, Germany
Date: Feb 10, 2017, 10:06, 190 days ago
Message: I don't think this is possible. Period.

And even if you find a way to do it without immediatly preventing your database to function at all, I'd really not recommend this.

The ORACLE_HOME owner is the one starting/stopping the database. He must be able to log on to the instance to do this.

As I said, even if you manage to prevent such logins, you'll probably break other crucial things, too.

Still: I'm curious if someone did manage to do this successfully.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Michel Cadot, France
Date: Feb 10, 2017, 10:11, 190 days ago
Message:
Just create a logon trigger and verify SYS_CONTEXT('USERENV','OS_USER').

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Tso P, South Africa
Date: Feb 10, 2017, 10:14, 190 days ago
Message: Thanks Jan

All I am saying is I don't want anyone to go to the OS and login directly to the database server.

What should happen is that the dba(s) for should login with their username and su to oracle (su - oracle).

They can be authenticated at that level but not directly.

I don't think that would break anything on the server.

Thanks

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Jan Schnackenberg, Germany
Date: Feb 10, 2017, 10:34, 190 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso

Ah, you want to prevent SSH-logons to the server for the ORACLE_HOME owner.

Just set an invalid password for the user.
Allow all DBAs to execute "sudo su - oracle".
Done.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Tso P, South Africa
Date: Feb 10, 2017, 10:43, 190 days ago
Message: Thanks Jan

Will this not affect the scheduled jobs that are scheduled via crontab or I need to edit the scripts and make sure that there is no direct connection via ssh?

Thanks a lot...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Tso P, South Africa
Date: Feb 10, 2017, 10:44, 190 days ago
Message: Thanks Michel

I am referring to the operating system user...

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Jan Schnackenberg, Germany
Date: Feb 10, 2017, 14:52, 190 days ago
Message: Hi Tso

Well, if you have scripts, that need to access the server directly as the "oracle"-user, then this would of course block them. You could modify them, to do the same thing (login as a "service"-user and then change into the oracle-user), though.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Michel Cadot, France
Date: Feb 10, 2017, 17:34, 190 days ago
Message:
>>> I am referring to the operating system user...

Yes me too.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Bruno Vroman, Belgium
Date: Feb 12, 2017, 17:50, 188 days ago
Message: Hi Tso,

as Michel has suggested; you can define a trigger fired "on logon"; in this trigger you can check what is the OSUSER at the origin of the connection, and you can raise an error if this user is "oracle" (with for example a message 'It is not allowed to connect with OSUSER oracle!')

Note that this trigger will not prevent a connection of an Oracle user with DBA role ("expected behaviour" to avoid blocking the database, see for example https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926), but in this case a message will be visible in the alert logfile.

So this is not enough to really block users (for example if they take benefit of being connectd as "oracle" to issue "sqlplus / as sysdba") but this will block connections to standard users (like SCOTT), and, assuming that only few people are able to connect as "oracle" at OS level, looking in the alert log will help you to find the "illegal connections" so that you can communicate with the guilty people (suggestion: audit SYS operations)

Best regards,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: user restriction or control
Author: Michel Cadot, France
Date: Feb 12, 2017, 18:10, 188 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:

>>> Note that this trigger will not prevent a connection of an Oracle user with DBA role

This is true but a workaround to this is to state a policy that no one can have this role by default and the dba should activate this role to be able to use it (using SET ROLE command).
Of course, once this role is activated a dba could set it to default but in this case this means he/she has voluntarily violate an enterprise policy and then could be fired (once this happened once then no one will do it in future).
In addition, you can set an audit to log all connections with OS user "oracle" along with the machine name or IP of the client, or, if this is a local connection terminal which allows to retrieve the original client machine in OS logs.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here