suppress privilege in built-in Oracle Database Role

Emad Al-Mousa

it will be great if Oracle can provide the capability to suppress a certain privilege within a database built-in role. For example, if a database account has been granted the DBA role....i would like revoke/deny from it the capability of create any user privilege.

SQL> grant DBA to user1;

SQL> revoke/deny create any user from user1;

so user1 will have all privileges granted under DBA role except "creating any user".

Regards,

Emad Al-Mousa

Mike Kutz

It has been recommended that companies do not rely on the given Oracle Roles.  Instead, everyone should create their own DBA Role to suite their requirements.

BrunoVroman

Hi Emad,

this would cause nightmares... What if 2 years later (when nobody remembers the "deny create user") user1 wants to create a user and receives "insufficient privileges", then people check "well, user1 is DBA..."

And what if people do "revoke dba from user1; grant dba to user1"? Do you expect that the "cannot create user" is still active? How to re-activate it? ... The list of questions/problems will very quickly grow; Oracle should modify the model and store the "denied privileges" somewhere...

The current solution is much simpler: if you don't want user1 to be "DBA", don't give DBA to user1 but list what you want...

Best regards,

Bruno Vroman.

P.S. not fully related but still (least privilege principle)... I've learned recently the new privilege "GRANT READ" instead of "GRANT SELECT"... Great!

Emad Al-Mousa

It has been recommended that companies do not rely on the given Oracle Roles.  Instead, everyone should create their own DBA Role to suite their requirements.

Hi,

This idea is already implemented in other RDBMS's such as SQL Server, and will add a benefit if only specific permission among hundreds within a role is required to be suppressed. Also, LOCKDOWN PROFILE is performing restriction for ALTER commands execution ..... similar concept !

Regards,

Emad Al-Mousa

Hi,

This idea is already implemented in other RDBMS's such as SQL Server, and will add a benefit if only specific permission among hundreds within a role is required to be suppressed. Also, LOCKDOWN PROFILE is performing restriction for ALTER commands execution ..... similar concept !

Regards,

Mike Kutz

Hi,

This idea is already implemented in other RDBMS's such as SQL Server, and will add a benefit if only specific permission among hundreds within a role is required to be suppressed. Also, LOCKDOWN PROFILE is performing restriction for ALTER commands execution ..... similar concept !

Regards,

Emad

You can assign  multiple ROLEs to a user already.

Those ROLEs can be "turned off" by DEFAULT.

The Session can then turn the appropriate roles on as needed.

.. by supplying an appropriate password.

... by calling a specific procedure.

I have no idea what LOCKDOWN PROFILE means in SQL Server.  An Oracle Profile has to do with resource consumption (eg amount of CPU time a session can use).

Off the top of my head, I have no idea what the ALTER command has to do with Permissions.  But, If you want to limit the usage of a command (eg ALTER), look into using a DB trigger and/or Oracle Vault ($$$$).

A BETTER SOLUTION

Your request fails at the concept of "least privileges".  That is why I downvote your idea.

If you don't want BOB_ACCOUNTING to have READ access to table SCHEMA_1.TABLE_A, you don't grant it to him/her in the first place.

What needs to be done is to grant the DBA the ability to define a group of people (Principle)

The DBA also needs to be able to define the data that will be affected (Domain)

Third, the DBA needs to define the permissions that will be applied ( ??? )

Finally, the DBA combines those three items by creating an Access Control List (ACL)

This is how Oracle's Real Application Security (RAS) works.  IIRC - this is the same method used by Firewalls and (at least one) Document Management System.

I see RAS as a VPD++ feature.  As such, RAS requires EE or 18c+ XE

What really needs to be done is to enhance RAS so that we

apply permissions to "all tables in a schema"

allow more precise permissions (eg TRUNCATE/Partition Management)

ability to use system privileges ( that is:  simulate "grant truncate  on schema_1.table_a to role_for_package_1")

I don't see the need to GRANT "bulk permissions" then REVOKE selected.

MK

Emad Al-Mousa

Emad

You can assign  multiple ROLEs to a user already.

Those ROLEs can be "turned off" by DEFAULT.

The Session can then turn the appropriate roles on as needed.

.. by supplying an appropriate password.

... by calling a specific procedure.

I have no idea what LOCKDOWN PROFILE means in SQL Server.  An Oracle Profile has to do with resource consumption (eg amount of CPU time a session can use).

Off the top of my head, I have no idea what the ALTER command has to do with Permissions.  But, If you want to limit the usage of a command (eg ALTER), look into using a DB trigger and/or Oracle Vault ($$$$).

A BETTER SOLUTION

Your request fails at the concept of "least privileges".  That is why I downvote your idea.

If you don't want BOB_ACCOUNTING to have READ access to table SCHEMA_1.TABLE_A, you don't grant it to him/her in the first place.

What needs to be done is to grant the DBA the ability to define a group of people (Principle)

The DBA also needs to be able to define the data that will be affected (Domain)

Third, the DBA needs to define the permissions that will be applied ( ??? )

Finally, the DBA combines those three items by creating an Access Control List (ACL)

This is how Oracle's Real Application Security (RAS) works.  IIRC - this is the same method used by Firewalls and (at least one) Document Management System.

I see RAS as a VPD++ feature.  As such, RAS requires EE or 18c+ XE

What really needs to be done is to enhance RAS so that we

apply permissions to "all tables in a schema"

allow more precise permissions (eg TRUNCATE/Partition Management)

ability to use system privileges ( that is:  simulate "grant truncate  on schema_1.table_a to role_for_package_1")

I don't see the need to GRANT "bulk permissions" then REVOKE selected.

MK

FYI, LOCKDOWN PROFILE is actually a security feature in Oracle Container Architecture (Multi-tenant) - PDB level.