0

I want to query DBA_USERs in order to identify sys and non-sys users/schemas. My client application however cannot connect as a SYS user and so I cannot query dba_users directly. I cannot grant privileges to the user as mentioned in answer -> gather dba_users information in Oracle database when connected as a user because it is a security flaw.

Investigating further, I found that all_users contains the same data as dba_users, however with less no. of columns (and less details).

So using this, I have come up with the below query -

SELECT distinct owner 
FROM all_tables 
where owner IN (SELECT username FROM all_users) 
and tablespace_name not in ('SYSTEM','SYSAUX')

I am able to run this query as a normal or non-sys user.

Is this complete ? I would like to go ahead and use this query in my production code. Can anyone point out any potential issues or data I might be missing by using this query (by using all_users rather than dba_users). Thanks in advance.

APC
  • 144,005
  • 19
  • 170
  • 281
Mohammed Raqeeb
  • 75
  • 2
  • 12
  • This only selects schemas who have granted privileges on their tables to the connected user. It doesn't identify all the schemas in the database nor does it identify schemas which have granted (say) execute on a package or select on a view but not on a table. Does that mean you are missing data? Perhaps. You haven't defined the actual set of users you want to find so we can't answer the question. – APC Mar 12 '19 at 11:46
  • Apologies for the missing detail. My use case is to find out all non-sys users/schemas/databases in my db. I cannot login as a SYS user to do that, nor can I grant such privileges to the user. I have to find this out being logged in as a normal user only. Please can you help me if you know how to do that ? Thanks you! – Mohammed Raqeeb Mar 12 '19 at 16:50
  • read @thatjeffsmith answer. If you want to know all the users registered in the system you know to query DBA_USERS, directly or indirectly. – APC Mar 12 '19 at 18:44
  • Yeah, guess I am in a deadlocked situation :| – Mohammed Raqeeb Mar 13 '19 at 09:33

1 Answers1

2

Your question assumes you can get access to information in the database that you have not been granted to access.

The database is setup to be secure against such scenarios.

As for your 'hack' -

Is this complete ?

No. Your query won't grab any schemas which are effectively being used as Log On accounts. That is, schemas which are empty. Those users exist, but they won't have any rows in the _TABLES view.

Also, if you wanted to go that route, you should use the DBA_OBJECTS view. Your schema could own a view, but not a table. And also, if you query the ALL_ views, you'll only be able to see the data dictionary that you have privs to see.

End of day - you need some DBA_ view love. Are you a DBA? If so, grant the select to your app user and LOCK down that user so it can't be used for bad things.

If you're NOT the dba, ask your DBA for a solution.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • dba_objects - thought of using it earlier. Have a query already that makes use of it to get the work done (see below). SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username ) AND default_tablespace not in ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN' But I don't have privileges to read it, either. No, I am not a DBA. I am a driver developer. My usecase is to display ONLY non-sys databases/schemas to Customer when he connects to data source using my driver and makes a metadata call. He won't be a privileged user, can't grant him sys privileges. – Mohammed Raqeeb Mar 12 '19 at 16:39
  • On the same lines, is it OK to temporarily grant permissions to a normal user (within the code), as a general security guideline ? I think it is NOT ok to do so and its a security loophole. But curious to know others' views on it. – Mohammed Raqeeb Mar 12 '19 at 16:45
  • 2
    I would never pass a code review where someone is doing a GRANT on anything really, but ESPECIALLY a DBA view – thatjeffsmith Mar 12 '19 at 17:38
  • 1
    @MohammedRaqeeb That is indeed a terrible idea. Don't grant temporary permissions. – Mast Mar 12 '19 at 17:51
  • Yeah I won't, just wanted to check with you guys. – Mohammed Raqeeb Mar 13 '19 at 09:32