Somewhat unusual permissions question

[DBAwhosaysNIE]

Is it possible to grant user A privilege to create tables in schema B but not schema C (or others)?

I have a 3rd party application whose install instructions say to grant it the DBA role. Naturally I called them and said: "WRONG ANSWER! Tell me exactly what system & object privileges you need". When they were unable to answer (vendor also sells competing DB & knows little if anything about Oracle) I turned on SQL_trace and determined that their app was creating an Oracle session as user A but trying to create temporary tables under schema B.

I know I could grant A CREATE ANY TABLE (which I've done for now) but I would prefer to limit it to B. Searching through Ora docs yielded no useful hints.

Any feedback would be greatly appreciated! [sig][/sig]

 

[karluk]

I believe there is no way to restrict the create table privilege the way you've described, at least using Oracle functionality. If anyone knows better, please let me know.

You could probably implement this type of security with a stored procedure. You would pass as input the create table statement and the desired schema. The procedure would have logic in it to decide whether you are allowed to create tables in the target schema and only proceed to execute the create statement if you pass edit.

The owner of the stored procedure would have "create any table", but you would only have execute privileges on the procedure. Because of the built in edits you could only create tables in a specific schema.

Unfortunately this approach requires recoding the application, so it probably isn't going to work with a third party application. [sig][/sig]

 

[carp]

No, you can't grant selective DDL privileges like that. It's either CREATE TABLE, which allows creation in your own schema or CREATE ANY TABLE, which is all schemas.

Karluk nearly hits the solution used in Oracle Applications.
For each schema in which you want to allow other users to create tables, you create a procedure that accepts the table definition and executes the dynamic sql to create the table. Since the procedure is created in user B's schema, it executes with user B's privileges. You then grant execute privileges to only those users you wish to be able to create tables in user B's schema. [sig][/sig]