You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




28 December 2005

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_AQ" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_AQADM" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_DRS" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_MONITOR" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_SERVER_ALERT" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_SYSTEM" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_WORKLOAD_REPOSITORY" to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','ALERT_QUE','SECURE_OEM_ROLE')
revoke EXECUTE ON "SYS"."DBMS_SERVER_ALERT" from dbsnmp;
revoke EXECUTE ON "SYS"."DBMS_SYSTEM" from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have "quota". In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ':')||':'||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != 'EXTERNAL'
and username != 'SYS')
or grantee='PUBLIC'
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better...

6 Comments:

Blogger Marc Soth said...

Hi Laurent,
hope you've survived x-mas ;-)

In my point of view, the CONNECT and ALTER SESSION rights are enough to kill your ORACLE instance.

Just connect to the ORACLE Instance and

alter session set WORKAREA_SIZE_POLICY = MANUAL

alter session set sort_area_size=10000000000

and do a little sql statement

select * from all_objects, all_objects, all_objects, all_objects,all_objects, all_objects,all_objects, all_objects,all_objects, all_objects,all_objects, all_objects
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,
14,15,16,17,18,19,20,21,22,23,24

(in this case it's depends on the rights you've given a user...)

After a few seconds your system starts swapping....

Of course If you've a table with more rows .... it kills faster your system.


In my point of view is the problem on the UNIX system. The most DBA run there system with unlimited memory allocation for every process. You can check it with unlimited -a. But what would happen if you would limit the memory allocation for a process? Maybe a user can open two or more connections.
I don't know if you can stop this inside of the ORACLE instance.

Wish you a nice start in 2005 and a happy new year

ciao

Marc

28/12/05 17:08  
Blogger Laurent Schneider said...

that's why I said "eventually" alter session.

if you have a ulimit, you should get an ORA-4030.

28/12/05 18:50  
Anonymous Eric Grancher said...

good morning,

I believe that it is not so easy to get an Oracle system "user-proof", where by accident or will a user can not get the instance to slow down dramatically.

We have recently worked quite a lot on resource manager and I believe that it can help quite a lot (in addition to PGA strong limit for example through event 10261).

have a very nice end of year,
eric

29/12/05 09:10  
Blogger Marc Soth said...

Hi Laurent,
agree absolutely with you, that you'll get an ORA-04030 if a process can't get more memory from the OS. But I personally think, that you would get the same ORACLE error message if a process consume to much memory and your OS can't give other processes more memory.

I don't know if the ulimited command can also prevent the SGA to allocate memory, because the shared memory doesn't belongs to a certain process. At the moment I don't have the possibility to check this, but I hope that I've time next year.

* Another possibility is, that a single user can send a lot of sql statements without any bind variables and then you have a nice latch problem.

* If a single user changes the NLS parameters you can have strange values in your database.

I think eric is right if he says, that's is not so easy to get an Oracle system "user-proof". It's similar on a UNIX system. If a UNIX user can write a shell script, he or she can stop or kill also the computer; or at least can stop a system from a smoothley running.

But I agree with you, that a single user shouldn't get to many grants on the System and the big roles (dba/import/export) should use only the dba. Today, the most applications has not a very good security implementation, they have too many rights.

Wish you a happy new year

ciao
Marc

29/12/05 13:01  
Blogger Laurent Schneider said...

> We have recently worked quite a lot on resource manager and I believe
> that it can help quite a lot
good hint! I should invest some time in it too

> I don't know if the ulimited command can also prevent the SGA to allocate
> memory, because the shared memory doesn't belongs to a certain process
well, you can set ulimits to the oracle owner...

have a nice 2006

29/12/05 18:12  
Blogger Marc Soth said...

Hi Laurent,
I agree with you, that the resource manager seems to be a good start point

I don't know to which process the shared memory belongs. ipcs -a shows just only that oracle is the owner of the shared memory.
May be it's the SMON process but that's absolutely wild guess.

ciao
Marc

30/12/05 14:12  

Post a Comment

<< Home