Home » RDBMS Server » Security » How to Allow a user to create a table in another schema
How to Allow a user to create a table in another schema [message #130621] Mon, 01 August 2005 10:18 Go to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.


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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.
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 Go to previous message
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:
  


Current Time: Fri Sep 24 05:29:34 CDT 2021