Re: Default user permissions

From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Tue, 08 Nov 2011 17:57:28 +0000
Message-ID: <4EB96D88.1070702_at_petefinnigan.com>

There are a number of "silent" privileges in Oracle. a user can issue "alter user identified by blah" for his own user or "noaudit select on tab". Similarly a user can drop his own tables without a drop table privilege; there are more. e.g.:

SQL> create user test identified by test; create user test identified by test

            *
ERROR at line 1:
ORA-01920: user name 'TEST' conflicts with another user or role name

SQL> create user testt identified by testt;

User created.

SQL> grant connect to testt;

Grant succeeded.

SQL> connect testt/testt_at_ora11gr2
Connected.
SQL> create table test (num varchar2(1)); create table test (num varchar2(1))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect system/xxxxxx_at_ora11gr2
Connected.
SQL> create table testt.test(num varchar2(1));

Table created.

SQL> connect testt/testt_at_ora11gr2
Connected.
SQL> drop table test;

Table dropped.

SQL> The user cannot create tables though. I test this separately:

SQL> connect system/xxxxxx_at_ora11gr2
Connected.
SQL> alter user testt quota unlimited on users;

User altered.

SQL> connect testt/testt_at_ora11gr2
Connected.
SQL> create table test1 (num varchar2(1)); create table test1 (num varchar2(1))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> You can run hhttp://www.petefinnigan.com/find_all_privs.sql which will list all the privileges for a user and also http://www.petefinnigan.com/who_has_priv.sql and feed in CREATE TABLE or CREATE ANY TABLE to see who has these system privileges.

Other than that can you show a sequence of creating the user and then creating a table?

cheers

Pete

Leo Drobnis wrote:
> I am a bit puzzled, maybe I am getting rusty.
>
>
> I need to create a user with bare minimum permissions:
>
>
>
> CREATE USER bb_stage
>
> IDENTIFIED BY "password"
>
> DEFAULT TABLESPACE users
>
> TEMPORARY TABLESPACE TEMP;
>
> GRANT CONNECT TO bb_stage;
>
> ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";
>
>
>
> Connect role only has create session.
>
> Public has no privileges.
>
>
>
> However the newly created user can create and drop tables.
>
>
>
> I am trying to find where it's coming from.
>
>
>
> Any idea???
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Pete Finnigan
CEO and Founder
PeteFinnigan.com Limited

Specialists in database security.

Makers of PFCLScan the database security auditing tool.
Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

If you need help to audit or secure an Oracle database, please ask for
details of our training courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7759 277220
email: pete_at_petefinnigan.com
site : http://www.petefinnigan.com

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940668114

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 08 2011 - 11:57:28 CST