Home » RDBMS Server » Server Administration » Granting Create Access (ORACLE 11g)
Granting Create Access [message #656150] Sun, 25 September 2016 03:03 Go to next message
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

Re: Granting Create Access [message #656151 is a reply to message #656150] Sun, 25 September 2016 07:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
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.
Re: Granting Create Access [message #656152 is a reply to message #656150] Sun, 25 September 2016 07:42 Go to previous messageGo to next message
EdStevens
Messages: 1376
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.
Re: Granting Create Access [message #656153 is a reply to message #656150] Sun, 25 September 2016 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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.

Re: Granting Create Access [message #656156 is a reply to message #656150] Sun, 25 September 2016 12:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: Granting Create Access [message #656230 is a reply to message #656156] Thu, 29 September 2016 02:10 Go to previous messageGo to next message
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
Re: Granting Create Access [message #656232 is a reply to message #656230] Thu, 29 September 2016 02:20 Go to previous message
Michel Cadot
Messages: 68624
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

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


Current Time: Thu Mar 28 09:59:53 CDT 2024