Re: Managing Oracle Users
Date: Sat, 1 Aug 2009 10:12:48 -0700 (PDT)
On Jul 31, 7:03 pm, undbund <undb..._at_gmail.com> wrote:
> On Jul 31, 5:16 pm, undbund <undb..._at_gmail.com> wrote:
> > Hi I had created a user with connect role and resorce role. When I
> > login to Oracle with this user, I am still able to see other shemas
> > and view their tables. Is there a way to prevent this so that when a
> > user who is a non dba logins in he/she should not see other shemas and
> > their tables and other objects?.
> > Thanks,
> > Undbund
> Hi All, thanks for your help in understanding Oracle. I am using
> Oracle 10g XE and the user that I had created has only Connect role
Not according to you:
"Hi I had created a user with connect role and resorce role."
Had you created such a user with only the CONNECT role you'd not be seeing any object not granted PUBLIC access:
SQL> create user lortmondo identified by yink;
SQL> grant connect to lortmondo;
SQL> connect lortmondo/yink
SQL> desc dba_users
ORA-04043: object "SYS"."DBA_USERS" does not exist
> According to Oracle....
> However, beginning in Oracle Database 10g Release 2 (10.2), the
> CONNECT role has only the CREATE SESSION privilege, all other
> privileges are removed.
> This should mean that when the user has only Connect role, he/she
> should only be able to login in and do nothing else.
Incorrect, as PUBLIC has access to a number of tables/procedures/
packages/functions that are automatically granted to every user
created in the database; at last count in a 10.2.0.3 database there
are 2320 tables in the SYS and SYSTEM schemas granted access by
> When I connect using this user... I can see other shemas like sys,
> system, etc and view some of their tables and the data in it, which is
Really? Which of those tables/views is considered dangerous for public access? I do not see tables/views like LINK$, USER$, DBA_USERS, DBA_DB_LINKS in that list.
> I use Navicat software to work with Oracle.
And I use TOAD and in many cases these tools are written to require a DBA-privileged account.
> I did notice that using this user, I can only view some of the tables
> from sys and system shema (as compared to logging in as system), which
> is still dangerous, I think.
I'm lost, then, as to why Oracle would grant PUBLIC access to these tables/views if they weren't fairly innocuous. Security alerts have already been issued for such packages as UTL_FILE so it (and others like it) no longer possess PUBLIC grants.
> I am really lost as to how to prevent this from happening.
You COULD, I suppose, revoke all grants to PUBLIC but I'm not certain how well your database would function.
> All your help is appreciated.
David Fitzjarrell Received on Sat Aug 01 2009 - 12:12:48 CDT