OraFAQ Forum: Security » ROLES & PRIVILEGES

Home » RDBMS Server » Security » ROLES & PRIVILEGES

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

ROLES & PRIVILEGES [message #268589]

Wed, 19 September 2007 02:29

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268607 is a reply to message #268589]

Wed, 19 September 2007 02:53

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268611 is a reply to message #268589]

Wed, 19 September 2007 02:55

Michel Cadot
Messages: 54213
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can always alter your tables, no privilege is needed.

Regards
Michel

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268616 is a reply to message #268589]

Wed, 19 September 2007 03:13

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268856 is a reply to message #268589]

Wed, 19 September 2007 22:48

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268892 is a reply to message #268856]

Thu, 20 September 2007 00:58

Michel Cadot
Messages: 54213
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

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268935 is a reply to message #268892]

Thu, 20 September 2007 02:38

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

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268938 is a reply to message #268589]

Thu, 20 September 2007 02:43

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>

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268940 is a reply to message #268589]

Thu, 20 September 2007 02:45

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

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268942 is a reply to message #268935]

Thu, 20 September 2007 03:08

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

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268945 is a reply to message #268589]

Thu, 20 September 2007 03:17

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268957 is a reply to message #268945]

Thu, 20 September 2007 03:59

Michel Cadot
Messages: 54213
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

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268963 is a reply to message #268892]

Thu, 20 September 2007 04:13

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.

Report message to a moderator

Re: ROLES & PRIVILEGES [message #268973 is a reply to message #268963]

Thu, 20 September 2007 05:05

Michel Cadot
Messages: 54213
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 12:36:42 CDT 2013

Total time taken to generate the page: 0.08272 seconds