OraFAQ Forum: Security » How to Allow a user to create a table in another schema

Home » RDBMS Server » Security » How to Allow a user to create a table in another schema

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

How to Allow a user to create a table in another schema [message #130621]

Mon, 01 August 2005 10:18

erich65
Messages: 5
Registered: February 2005

Junior Member

The create any table grant seems a little extreme. Is there a way to allow user A to ceate a table for user B and not for every other user in the database?

--Eric

Report message to a moderator

Re: How to Allow a user to create a table in another schema [message #130730 is a reply to message #130621]

Tue, 02 August 2005 03:04

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Hi eric,

I dnt think that it is possible without CREATE ANY TABLE .

Everyone is saying the same, check out this links
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054287,00.html

So i think we need to follow Oracle rules.

regards,
tarun

Report message to a moderator

Re: How to Allow a user to create a table in another schema [message #130975 is a reply to message #130621]

Wed, 03 August 2005 12:05

erich65
Messages: 5
Registered: February 2005

Junior Member

I tried this and it seems to have worked.

I created user USERA with create table privileges.

I put this package in schema USERA:

create or replace package seq
IS
procedure createtable(in_table VARCHAR2);

END;
/
show errors;

create or replace package body seq
IS
procedure createtable(in_table VARCHAR2)
IS
BEGIN
execute immediate in_table;
END;

END;

I created USERB without create table privileges but had USERA grant execute on package seq.

I then called this while logged into userb thru sqlplus:

exec usera.seq.createtable('create table foo (a number)');

and the table was created for usera.

Very Interesting possibilities.

Report message to a moderator

Re: How to Allow a user to create a table in another schema [message #130998 is a reply to message #130975]

Wed, 03 August 2005 16:03

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

That would allow userb to do anything that usera can do, not just create tables in usera's schema, but drop them as well, and anything else usera can do. You could add some checking of the in_table parameter, before allowing the execute immediate, in order to apply some restrictions.

Report message to a moderator

Re: How to Allow a user to create a table in another schema [message #131006 is a reply to message #130998]

Wed, 03 August 2005 17:15

erich65
Messages: 5
Registered: February 2005

Junior Member

Yes,

Actually when I put this into use I will probably make it a 2 argument procedure that takes a delimited list of columns and types and I'll construct the create table statement myself within the procedure.

[Updated on: Wed, 03 August 2005 17:16]

Report message to a moderator

Previous Topic:	enable audit
Next Topic:	Problem with connect / as sysdba

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 16 23:10:24 CDT 2025