Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Somewhat unusual permissions question

Somewhat unusual permissions question

Somewhat unusual permissions question

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!

RE: Somewhat unusual permissions question

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.

RE: Somewhat unusual permissions question

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close