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…
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
that’s why I said “eventually” alter session.
if you have a ulimit, you should get an ORA-4030.
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
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
> 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
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