Hi guys,
I'm working on a system which requires good security, and therefore must operate on a minimum privilege basis.
Naturally I've read the CIS recommendations and applied them, as well as DISA STIG, and disabled all default accounts etc.
The database in question will only ever be accessed by two service accounts, so a near perfect lockdown
ought to be possible.
My intention therefore is to revoke all privileges from PUBLIC and then grant the service accounts only those privileges needed.
Having searched, I understand that potentially this may have significant impact, and will require no little testing, to confirm correct working of the service accounts, once the privileges have been revoked.
Since 12c has 32,126 privileges granted to PUBLIC, I am generating a revocation script and then running it via sql plus.
To generate the revocation script I am using:-
SPOOL DISABLE_PUBLIC_ROLE.SQL
SELECT 'REVOKE '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' FROM PUBLIC;'
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'PUBLIC'
ORDER BY OWNER,TABLE_NAME,PRIVILEGE;
SPOOL OFF
This creates a whopping great script called DISABLE_PUBLIC_ROLE.SQL which, when run, fails spectacularly.
The MDSYS and ORDSYS interfaces cause errors, along with SYS privileges, but the final
coup de grace comes from XDB which unceremoniously terminates the session, as shown below.
REVOKE EXECUTE ON MDSYS./102EE2A8_PSSegmentSegType FROM PUBLIC
ERROR at line 1:
ORA-00903: Invalid table name
REVOKE EXECUTE ON ORDSYS./1004e416_BaselineTIFFTagSetJP FROM PUBLIC
ERROR at line 1:
ORA-01426: numeric overflow
REVOKE EXECUTE ON SYS./1000323d_DelegateInvocationHA FROM PUBLIC
ERROR at line 1:
ORA-00911: invalid character
<code>REVOKE EXECUTE ON XDB.ABSPATH FROM PUBLIC;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4416
Session ID: 131 Serial Number: 5430
I can easily ignore MDSYS, ORDSYS and SYS, and could filter out everything beginning with a slash (/) and even work my way through every XDB privilege by hand. However, this defeats the object of the exercise. Can you recommend a better way of going about this? Am I generating the script from the wrong view? Is there some elegant way of filtering out all the java bits - or is it wrong not to disable the java interface?
I am loathe to even consider dropping the PUBLIC role, as this may not be possible, or if it is, could cause mayhem elsewhere.
All suggestions gratefully received.
MOS note 247093.1 has a hefty warning when it comes to revoking privileges from public:
*** WARNING ***
If you revoke any privilege from PUBLIC it becomes your own responsibility to ascertain that all applications keep working, this can often be accomplished by replacing the privileges formerly granted to PUBLIC to individual users or roles. Oracle support can only assist you in accomplishing this task, however Oracle support cannot help you answer the general question of what will happen if you revoke default privileges as this depends greatly on the implementation details of any application running on a specific database.
So removing all public privileges is a terrible idea. Dropping public is even worse!
Rather than try and remove public access from XDB etc. I'd uninstall these features (assuming you're not using them of course). This will be more secure anyway, as you don't have to worry about vulnerabilities in these components.
If you must revoke some privileges from public, follow the advice above and work with support.