Home » RDBMS Server » Security » ROLES & PRIVILEGES
ROLES & PRIVILEGES [message #268589] Wed, 19 September 2007 02:29 Go to next message
dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad
Junior Member
Hi..,
I've created a user that Test

Provided the following:
Grant connect,resource to test;

RESOURCE Role is a set of the following privileges:
Note :I've checked it using the query "select * from dba_sys_privs where grantee='RESOURCE'"

CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

Without ALTER TABLE Privilege how can I able to Altering my table which is exists in test User..?
Can u pl. explain..?

Thanks in Advance,
Giri K.Y.
Re: ROLES & PRIVILEGES [message #268607 is a reply to message #268589] Wed, 19 September 2007 02:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Not possible. You must have alter any table privilege to alter another schema's table.
Re: ROLES & PRIVILEGES [message #268611 is a reply to message #268589] Wed, 19 September 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can always alter your tables, no privilege is needed.

Regards
Michel
Re: ROLES & PRIVILEGES [message #268616 is a reply to message #268589] Wed, 19 September 2007 03:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

@OP, without alter any table privilege he even will not have access another schema's objects.
Re: ROLES & PRIVILEGES [message #268856 is a reply to message #268589] Wed, 19 September 2007 22:48 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create table allow the grantee to create,alter and drop tables in the grantee's own schema.

So if you have create table permission you can create, alter as well as drop table of your own schema.
Re: ROLES & PRIVILEGES [message #268892 is a reply to message #268856] Thu, 20 September 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So if you have create table permission you can create, alter as well as drop table of your own schema

Even if you don't have the privilege.

Regards
Michel
Re: ROLES & PRIVILEGES [message #268935 is a reply to message #268892] Thu, 20 September 2007 02:38 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel Cadot wrote on Thu, 20 September 2007 11:58
Quote:
So if you have create table permission you can create, alter as well as drop table of your own schema

Even if you don't have the privilege.

Regards
Michel



No, I think.

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table test ( a number);
create table test ( a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Re: ROLES & PRIVILEGES [message #268938 is a reply to message #268589] Thu, 20 September 2007 02:43 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

No, I think.


User test should have create table privilege not create session privilege
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create user test identified by test;

User created.

SQL> grant create table to test;

Grant succeeded.

SQL> create table test ( a number);

Table created.

SQL> alter table test add ( b number);

Table altered.

SQL> drop table test;

Table dropped.

SQL> 
Re: ROLES & PRIVILEGES [message #268940 is a reply to message #268589] Thu, 20 September 2007 02:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I forgot to mention version. 10.0.2.1
SQL> create table test ( a number);
create table test ( a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn / as sysdba
Connected.
SQL> grant resource to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table test ( a number);

Table created.
SQL> show release
release 1002000100

Re: ROLES & PRIVILEGES [message #268942 is a reply to message #268935] Thu, 20 September 2007 03:08 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Arju wrote on Thu, 20 September 2007 02:38


No, I think.

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table test ( a number);
create table test ( a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges




You gave test user create session privilege, but i gave test user
create table privilege
Quote:

SQL> grant create table to test;

Grant succeeded.




Try with this
revoke resource from test

revoke create session from test

grant create table to test


And then create table , alter table and drop table
Re: ROLES & PRIVILEGES [message #268945 is a reply to message #268589] Thu, 20 September 2007 03:17 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Muzahid(Showrav), You misleadingly interpret my message. It is not a matter of that one you demonstrate.

I responded to Michel's answer in which he like to say no need of any privilege to create table in own schema.

Re: ROLES & PRIVILEGES [message #268957 is a reply to message #268945] Thu, 20 September 2007 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I responded to Michel's answer in which he like to say no need of any privilege to create table in own schema.

This is wrong.
I said you don't need any privilege to ALTER a table in your own schema.
SQL> create user t identified by t  quota unlimited on ts_d01;

User created.

SQL> grant create session to t;

Grant succeeded.

SQL> create table t.t (val integer);

Table created.

SQL> connect t/t
Connected.
T> alter table t add (val2 integer);

Table altered.

T> drop table t;

Table dropped.

The only T privilege is CREATE SESSION.

Regards
Michel

Re: ROLES & PRIVILEGES [message #268963 is a reply to message #268892] Thu, 20 September 2007 04:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel Cadot wrote on Thu, 20 September 2007 11:58
Quote:
So if you have create table permission you can create, alter as well as drop table of your own schema

Even if you don't have the privilege.

Regards
Michel



Sorry , Michel if I do any wrong.I responded to this one which seemed to me not correct.
Re: ROLES & PRIVILEGES [message #268973 is a reply to message #268963] Thu, 20 September 2007 05:05 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My answer was for the last part of your sentence: "you can ... alter as well as drop table of your own schema" which is related to the original question.

Regards
Michel
Previous Topic: Password problem on Oracle 10g (merged)
Next Topic: user unable to connect
Goto Forum:
  


Current Time: Thu Mar 28 06:17:48 CDT 2024