Re: Managing Oracle Users

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
> only.

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;

User created.

SQL> grant connect to lortmondo;

Grant succeeded.

SQL> connect lortmondo/yink
Connected.
SQL> desc dba_users
ERROR:
ORA-04043: object "SYS"."DBA_USERS" does not exist

SQL>
>
> 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.
>

Correct.

> 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 PUBLIC.
>
> 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
> dangerous.

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.
>
> Thanks,
> Undbund

David Fitzjarrell Received on Sat Aug 01 2009 - 12:12:48 CDT