Skip to Main Content
  • Questions
  • Security lockdown - revoke all privileges from PUBLIC

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: January 18, 2017 - 11:27 am UTC

Last updated: October 20, 2018 - 4:39 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

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.



and we said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

As I kinda thought

John Hawksworth, January 19, 2017 - 9:23 am UTC

Thanks for your response. You've confirmed my suspicions that the whole idea is not good.

I've checked the MOS notes available and the one you mentioned. Considering some of the possible impacts, I'll recommend that this approach be abandoned.

It's great to have an informed third party giving input, so once again my thanks, and keep up the good work!
Chris Saxon
January 20, 2017 - 10:42 am UTC

Thanks, I agree with your conclusion ;)

Need to revoke execute from PUBLIC for certain packages

Arun Gupta, October 18, 2018 - 2:09 pm UTC

Hi,
We are also facing a similar situation with an audit. The auditors have flagged execute privileges on some built-in packages granted to PUBLIC. These are:

DBMS_CRYPTO
DBMS_ADVISOR
DBMS_JAVA
DBMS_JOB
DBMS_LDAP
DBMS_LOB
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SCHEDULER
DBMS_SQL
DBMS_XMLGEN
DBMS_XMLQUERY
UTL_FILE
UTL_INADDR
UTL_TCP
UTL_MAIL
UTL_SMTP
UTL_DBWS
UTL_ORAMTS
UTL_HTTP
HTTPURITYPE
DBMS_SYS_SQL
DBMS_BACKUP_RESTORE
DBMS_AQADM_SYSCALLS
DBMS_REPCAT_SQL_UTL
INITJVMAUX
DBMS_STREAMS_ADM_UTL
DBMS_AQADM_SYS
DBMS_STREAMS_RPC
DBMS_PRVTAQIM
LTADM
WWV_DBMS_SQL
WWV_EXECUTE_IMMEDIATE
DBMS_IJOB
DBMS_FILE_TRANSFER

My question is slightly different. I can manage the applications. But if I revoke execute from PUBLIC on all these objects, will the database features continue to work? I read in one of MOS notes that export will stop working as it need execute privilege on DBMS_LOB. So, I have to grant execute on DBMS_LOB to EXP_FULL_DATABASE role. Is there such a complete list of internal grants that need to be fixed?

Thanks...
Connor McDonald
October 20, 2018 - 4:39 pm UTC

Not that I know of (which is a pity). There are 3 critical notes that are recommended:

Note:247093.1 "Be Cautious When Revoking Privileges Granted to PUBLIC" 
Note:797706.1 "Invalid Objects After Revoking EXECUTE Privilege From PUBLIC"
Note:1165830.1 "Problems After Revoking Execute On DBMS_SQL, DBMS_JOB, DBMS_LOB, DBMS_RANDOM, and DBMS_OBFUSCATION_TOOLKIT From PUBLIC"

A couple of things to note:

1) The reason I suspect there isn't a fixed list, is because we have lots of flexibility in terms of what people install on their own systems.

2) I think you're auditors are perhaps misinformed in that public means a security issue. Perhaps provide them with the notes listed above.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.