OraFAQ Forum: Server Administration » Granting Create Access

Home » RDBMS Server » Server Administration » Granting Create Access (ORACLE 11g)

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

Granting Create Access [message #656150]

Sun, 25 September 2016 03:03

lokimisc
Messages: 101
Registered: February 2008

Senior Member

Hi,
We have requirement where from GUI user will send details of table and we need to create and alter tables which starts from ZZ_*( Eg: ZZ_emp) in another schema.
Along with create table, user should be able to create index and create referential integrity constraint only for ZZ_* tables.

Also We don't want to provide create any table access to user as user should be able to create or alter tables which starts ONLY from ZZ_* and not other tables in another schema.

Could anyone please help in solving the above requirement.

Regards,
Lokesh

Report message to a moderator

Re: Granting Create Access [message #656151 is a reply to message #656150]

Sun, 25 September 2016 07:31

BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal

Senior Member

IMO, whole "design" is daft & violates Third Normal form by duplicating data purposefully.

It can be done, but I refuse to be complicit is aiding & abetting wrong design principals.

Not everything that can be done, should ever be actually done.
You can also poke yourself in the eye with a sharp pencil.
I strongly advise against doing either.

Report message to a moderator

Re: Granting Create Access [message #656152 is a reply to message #656150]

Sun, 25 September 2016 07:42

EdStevens
Messages: 1295
Registered: September 2013

Senior Member

lokimisc wrote on Sun, 25 September 2016 03:03

Hi,
we need to create and alter tables . . . in another schema.

Also We don't want to provide create any table access

Inherently contradictory requirements.

The only way that user A can create a table in schema B is with the CREATE ANY TABLE privilege.

Even that aside, I agree with BlackSwan. The overall requirement a disaster in the making.

Report message to a moderator

Re: Granting Create Access [message #656153 is a reply to message #656150]

Sun, 25 September 2016 09:04

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

Senior Member
Account Moderator

Create a procedure that can do it after validating all parameters including caller and grant EXECUTE on it to other accounts.

Report message to a moderator

Re: Granting Create Access [message #656156 is a reply to message #656150]

Sun, 25 September 2016 12:53

Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What problem are you trying to solve? Saying that "you have requirement": who requires that, exactly? Why would users create tables? Why wouldn't they INSERT data into existing ones?

On the other hand, that sounds as if you are creating environment for students and want to apply certain restrictions. How about letting them do whatever they want in their own schema? Just don't give them unlimited quota on a tablespace.

Anyway: if you could explain the problem, someone might suggest a better approach than the one you mentioned.

Report message to a moderator

Re: Granting Create Access [message #656230 is a reply to message #656156]

Thu, 29 September 2016 02:10

lokimisc
Messages: 101
Registered: February 2008

Senior Member

Thank You all for providing inputs.

We are implementing the solution as suggested by Michel Cadot i.e to Create a procedure that can do it after validating all parameters including caller and grant EXECUTE on it to other accounts.

Thanks Michel.

Regards,
Lokesh

Report message to a moderator

Re: Granting Create Access [message #656232 is a reply to message #656230]

Thu, 29 September 2016 02:20

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

Senior Member
Account Moderator

It'd be great if you could post the code for mainly 2 reasons:
* It may help people with the same kind of problem
* We could improve it to your benefit

Report message to a moderator

Previous Topic:	spfile and pfile
Next Topic:	Connection failed from Oracle XE in Windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 23 19:06:18 CST 2021