Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Grant all on a specific schema instead of "* ANY *" privileges

Mustafa KalaycıAug 28 2019 — edited Aug 28 2019

Hi all,

It would be really nice if I can grant privileges on a specific schema. for example there are some lead developers on the db which connect to database and do some certain jobs on application schema and application schema only! in that case if DeveloperA user needs to create a table on APP schema, I must grant "create any table" privilege but this is a too powerful privilege because as you all know with this privilege DeveloperA will be able to create table to any schema. Also I don't want DeveloperA to see another schema's source codes for example I don't want them to see DBA users source codes or change them. this is not just for admin users of course there might be more than one application schema and there might be different responsibilities on developer teams and they shouldn't be intervening to each other. again, " * ANY * " privileges are too powerful.

So it would be nice if I can limit a user to create/drop/alter or see, table/procedure/package etc on a specific schema only! for example DeveloperA shouldn't be able to see any source code on all_source view except his owns and the schema (s) that we allowed. I didn't think with all detail but it might be something like:

grant create table on schema APP to DeveloperA;

grant drop table, alter table on schema APP to DeveloperA;

I am aware that many of you might say developers shouldn't be connecting directly but (at least for my customers) I didn't see any customer which has this rule. Also in any case, there should be a privilege which has less than ANY privileges.

by the way I did my search on ideas about this subject and found this: but I am not sure if OP is asking the same thing with me.

thanks.

Comments
Post Details
Added on Aug 28 2019
14 comments
2,335 views