Forum Stats

  • 3,824,838 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

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

Mustafa_KALAYCI
Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
edited Aug 28, 2019 4:23PM in Database Ideas - Ideas

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.

Adityanath DewoolkarSven W.KayKUser_PT223philippe_eAndreas HuberEvandro Lima-OracleKiran Swamiuser11970842sdstuber
10 votes

Active · Last Updated

«1

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    It sounds like you want a free way to implement part of Oracle Database Vault ($$$)

    https://www.oracle.com/database/technologies/security/db-vault.html

    MK

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    It sounds like you want a free way to implement part of Oracle Database Vault ($$$)

    https://www.oracle.com/database/technologies/security/db-vault.html

    MK

    I didn't think it that way might be similar but vault is mostly related to hiding sensitive data from too privileged users like SYS. no one wants their db admins to see credit card information but I mostly want a user to do his job with his/her boundaries. Vault allow you to hide even just a table. this is not what I meant and what I meant is not for limiting SYS or any dba privilged user.

  • Adityanath Dewoolkar
    Adityanath Dewoolkar Member Posts: 346 Bronze Badge

    Even it should be allowed to grant truncate on particular table under different user. Something like given below:

    grant truncate on a.table1 to b;

    Currently only way to achieve this is to grant "drop any table" to user b.

    Regards,

    Adi

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    Even it should be allowed to grant truncate on particular table under different user. Something like given below:

    grant truncate on a.table1 to b;

    Currently only way to achieve this is to grant "drop any table" to user b.

    Regards,

    Adi

    Hi,

    this is actually something else. that might be a standard privilege instead of drop table but it can be used on my idea too of course. my primary goal is allowing some privileges on a specific schema. by the way because of the design truncate privilege might not be possible yet because when you truncate a table, it really drop an object (data object of the table) so drop might be a mandatory.

    thanks.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    Hi,

    this is actually something else. that might be a standard privilege instead of drop table but it can be used on my idea too of course. my primary goal is allowing some privileges on a specific schema. by the way because of the design truncate privilege might not be possible yet because when you truncate a table, it really drop an object (data object of the table) so drop might be a mandatory.

    thanks.

    I wonder if the requirement can be achieved via Real Application Security (RAS)?

    I don't have access to a system that supports RAS (and I can play on) at this moment so I can't test things out.

    But, if XS_SECURITY_CLASS.ADD_PRIVILEGES() supports TRUNCATE or DROP, then the limit to specific tables should be possible.

    Now, as far as any table (current or future) in a given schema, that would be a different thing.

    My $0.02

    MK

    Mustafa_KALAYCI
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    This idea seems to be a generalization of the   idea.

    Which is (for some time now) accepted as an ER:  This request is now referenced with ER: 16899440 - SCHEMA WIDE PRIVILEGES

    Mustafa_KALAYCI
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    This idea seems to be a generalization of the   idea.

    Which is (for some time now) accepted as an ER:  This request is now referenced with ER: 16899440 - SCHEMA WIDE PRIVILEGES

    agreed Sven. I think the one of the most important thing is hiding the source codes of the schemas except the ones that you are authorized. create/drop/alter might be prevent with a ddl trigger which certainly I don't want to do that but source codes are become public with too much "ANY" privilege

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    I wonder if the requirement can be achieved via Real Application Security (RAS)?

    I don't have access to a system that supports RAS (and I can play on) at this moment so I can't test things out.

    But, if XS_SECURITY_CLASS.ADD_PRIVILEGES() supports TRUNCATE or DROP, then the limit to specific tables should be possible.

    Now, as far as any table (current or future) in a given schema, that would be a different thing.

    My $0.02

    MK

    I didn't work with RAS or use it. I will test it too.

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited Sep 5, 2019 5:31AM

    Would proxy users suffice for this as a somewhat halfway house?

    alter user A grant connect through B;

    Obviously it's not as fine-grained as you might like (A gets ALL of B's permissions) but it removes the need to grant across all schemas.

    Mustafa_KALAYCI
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Would proxy users suffice for this as a somewhat halfway house?

    alter user A grant connect through B;

    Obviously it's not as fine-grained as you might like (A gets ALL of B's permissions) but it removes the need to grant across all schemas.

    Unfortunatly a PROXY connect does not help. This only handles authentication not authorization.
    For example a PROXY user would still be able to drop a table although he should only get SELECT, READ, INSERT, UPDATE, DELETE on all tables from this schema.

    Mustafa_KALAYCI