Home » RDBMS Server » Security » Drop Table (Oracle 10g 10.2.0.1 RHEL5)
Drop Table [message #395216] Tue, 31 March 2009 14:29 Go to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Hi,
I have a question:
1. why the user of oracle has privilege
to drop table in your schema only
with grant of connect and create session?

Thanks,
Marcos Santos
Re: Drop Table [message #395217 is a reply to message #395216] Tue, 31 March 2009 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
By default, the owner of any object can do anything he desires with that object.

By your question how/why was user able to create the table in the 1st place?

[Updated on: Tue, 31 March 2009 14:36]

Report message to a moderator

Re: Drop Table [message #395218 is a reply to message #395216] Tue, 31 March 2009 14:45 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Yes.

The user was not able TO CREATE a table
but was able to DROP the table in
your schema.

Your privilege are only TO CONNECT.

The DROP is more dangerous.

I think this illogical.

Thanks,
Re: Drop Table [message #395219 is a reply to message #395216] Tue, 31 March 2009 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I think this illogical.
Submit your change request to/at http://metalink.oracle.com

Re: Drop Table [message #395220 is a reply to message #395216] Tue, 31 March 2009 14:56 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
I dont understand.
I create a user in oracle.
I grant privilege to CONNECT for this user.
Only CONNECT.
1. Can this user create a table in your schema?
2. If I create a table with user SYS for this user,
Can he DROP this table?

Thanks,

Re: Drop Table [message #395221 is a reply to message #395216] Tue, 31 March 2009 14:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What object type is "CONNECT"?

Use CUT & PASTE to provide proof of the answer.
Re: Drop Table [message #395222 is a reply to message #395216] Tue, 31 March 2009 15:05 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Hi,
steps:
1.
sql>create user user1 identified by pass;

2.
sql> grant connect to user1;


My questions:
1. can user1 to create tables in your schema;
2. can user1 to drop tables in your schema;

Thanks,

Re: Drop Table [message #395223 is a reply to message #395216] Tue, 31 March 2009 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since you do not answer my question, I will no longer answer yours.

>1. can user1 to create tables in your schema;
>2. can user1 to drop tables in your schema;
YOUR is not a schema in my database, so you need to run these experiments yourself.
Re: Drop Table [message #395224 is a reply to message #395216] Tue, 31 March 2009 15:13 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Laughing

My questions:
1. can user1 to create tables in self schema;
2. can user1 to drop tables in self schema;

thanks,
Re: Drop Table [message #395225 is a reply to message #395216] Tue, 31 March 2009 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELF is not a schema in my database, so you need to run these experiments yourself.

Re: Drop Table [message #395227 is a reply to message #395216] Tue, 31 March 2009 15:21 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Can someone help me, please.
Re: Drop Table [message #395228 is a reply to message #395216] Tue, 31 March 2009 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What object type is "CONNECT"?

Use CUT & PASTE to provide proof of the answer.

You need to run these experiments yourself.

[Updated on: Tue, 31 March 2009 15:23]

Report message to a moderator

Re: Drop Table [message #395230 is a reply to message #395216] Tue, 31 March 2009 15:25 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
CONNECT is a role from Oracle Database.
Re: Drop Table [message #395231 is a reply to message #395216] Tue, 31 March 2009 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CONNECT is a role from Oracle Database
OK, CONNECT is NOT a privilege as you previously stated above.
Which privileges are associated with CONNECT role?

What happened to CUT & PASTE of query & response?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Drop Table [message #395233 is a reply to message #395230] Tue, 31 March 2009 15:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER use predefined roles.
ALWAYS create your own.

Now follow what BlackSwan is trying to make you learn, it is very important if you want to use Oracle.

Regards
Michel
Re: Drop Table [message #395536 is a reply to message #395233] Wed, 01 April 2009 16:10 Go to previous messageGo to next message
panchino
Messages: 1
Registered: April 2009
Location: Caceres
Junior Member
Hi

If you create user "any1" and give the "ROLE CONNECT", only he can connect to database, if the user "any1" do not have got grant to "create table" or "delete table" or "delete any table" the user can not do it.

And if the user "any1" don't have quota in any tablespace the user will can not create any object
Re: Drop Table [message #395653 is a reply to message #395536] Thu, 02 April 2009 02:51 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

SQL> conn babu
Enter password:
Connected.
SQL>
SQL>

SQL> select  * from session_roles;

ROLE
------------------------------
CONNECT

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> show user
USER is "BABU"

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> create table babu.babu_tst (a int);

Table created.

SQL> conn babu
Enter password:
Connected.
SQL>
SQL> select * from babu_tst;

no rows selected

SQL> drop table babu_tst;

Table dropped.

SQL> create table babu_Tst (A int);
create table babu_Tst (A int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


I hope the above one asking Mr marcossantos.

Here "babu_tst" object owner is "babu" that's the reason i can able to drop this object using my user.

If you want to protect then write database trigger.

Babu

Re: Drop Table [message #395656 is a reply to message #395653] Thu, 02 April 2009 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gentlebabu wrote on Thu, 02 April 2009 09:51
If you want to protect then write database trigger.

Better lock the user and don't use it as application user but just as object container.

And DON'T use SYS even for a demonstration, you can use a DBA user of your own.
Remember doing something with SYS never proves anything for another user.

Regards
Michel

[Updated on: Thu, 02 April 2009 02:58]

Report message to a moderator

Re: Drop Table [message #395660 is a reply to message #395656] Thu, 02 April 2009 03:12 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>Better lock the user and don't use it as application user but just as object container.

Suppose if it's production database; then we need to go only database triggers (before drop).

>>And DON'T use SYS even for a demonstration, you can use a DBA user of your own.
Remember doing something with SYS never proves anything for another user.

Sure Mr Mic. I'll create one dba user.

Thanks

Babu
Re: Drop Table [message #396011 is a reply to message #395216] Fri, 03 April 2009 09:29 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Hi,
Is there a method to prevent that some
user DROP objects in own schema?
Thanks,

Marcos Santos

[Updated on: Fri, 03 April 2009 09:29]

Report message to a moderator

Re: Drop Table [message #396022 is a reply to message #395216] Fri, 03 April 2009 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CONNECT is a role from Oracle Database
OK, CONNECT is NOT a privilege as you previously stated above.
Which privileges are associated with CONNECT role?


If you do not answer our questions, why should we answer yours?
Re: Drop Table [message #396023 is a reply to message #396011] Fri, 03 April 2009 09:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Please read complete thread. I hope already we updated all the details.

Babu
Re: Drop Table [message #396024 is a reply to message #395216] Fri, 03 April 2009 09:37 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Role CONNECT
Privileges: CREATE SESSION

Thanks,
Re: Drop Table [message #396026 is a reply to message #396024] Fri, 03 April 2009 09:42 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Thanks for the details.

But please follow Mr BlackSwan

Quote:
Use CUT & PASTE to provide proof of the answer.
Re: Drop Table [message #396028 is a reply to message #395216] Fri, 03 April 2009 09:51 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
SQL> conn sys as sysdba
Informe a senha:
Conectado.

SQL> create user user_teste identified by teste;

Usußrio criado.

SQL>
SQL> grant create session to user_teste;

ConcessÒo bem-sucedida.


[Updated on: Fri, 03 April 2009 10:38] by Moderator

Report message to a moderator

Re: Drop Table [message #396042 is a reply to message #396011] Fri, 03 April 2009 10:39 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
marcossantos wrote on Fri, 03 April 2009 16:29
Hi,
Is there a method to prevent that some
user DROP objects in own schema?
Thanks,

Marcos Santos

Database trigger on the schema.

Regards
Michel

Previous Topic: Transparent Data Encryption, Oracle Wallet
Next Topic: Microsoft, Oracle issue patches for slew of holes
Goto Forum:
  


Current Time: Thu Mar 28 11:15:03 CDT 2024