Forum Stats

  • 3,854,339 Users
  • 2,264,353 Discussions
  • 7,905,649 Comments

Discussions

Customize pre-defined role

Mohamed Esmael
Mohamed Esmael Member Posts: 15
edited Jan 20, 2020 7:53AM in Database Security - General

Hello All,

      I am trying to customize predefined roles like DBA ROLE as to apply SoD(Segregation of duties) between database system admin and database security admin

          for example to revoke create user privileges from DBA ROLE and to grant to new role created for security

My Question:- what is the effect of that ? does Oracle recommend to customize on predefined role ?

Thanks in advance

pmdbaBPeaslandDBAMohamed Esmaelandrewmy

Answers

  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jan 13, 2020 10:06AM

    In general, no, I would not alter pre-defined roles, as they are potentially used by system/service accounts. Even if you did modify it, it could easily be reset during the installation of a patch or an upgrade. Create a copy of the role and modify that. If you need real separation of duties, then look into Database Vault, as well. If you are using 12c or higher (hopefully), then you also have the SYSBACKUKP, SYSDG, and SYSKM privileges to work with.

    andrewmy
  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Jan 13, 2020 10:11AM

    basically follow the "least privilege" concept, what do security team need exactly with the database account you are going to create ? what is the objective ? for example do they need to read database views,dictionary....etc ?

    you can create a custom role and within this custom role grant the permissions that they need only (for example they shouldn't have SELECT ANY TABLE permission which is part of the built-in DBA role which will enable them to select/query "user data").

    I hope this helps

    Regards,

    Emad

    pmdba
  • Mohamed Esmael
    Mohamed Esmael Member Posts: 15
    edited Jan 14, 2020 1:19AM

    from my reading on data vault , i think it's better for business data which contained on Realm but here i talking about security admins on DataBase which will responsible for user management (create, drop , alter  user and profile) and Access Management (Grant and Revoke roles , system privileges and object privileges) 

    Can I apply that without existence of data vault ? Note: take advantages of data vault without use it

  • Mohamed Esmael
    Mohamed Esmael Member Posts: 15
    edited Jan 14, 2020 1:29AM

    As I mentioned above I want to create  new custom role (Security admin role) which will be granted (Create user , Alter user ,Drop user , Create profile , Alter Profile, Drop profile) Privileges and  Them from DBA Role , Also Security admin will be responsible for Access Management (Grant , Revoke) privileges

  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jan 14, 2020 10:45AM

    Sounds like you have what you need for the Sec Admin role. You can create a custom DBA role as a copy of DBA (call it dba_lite ) and remove what you don't want it to have, but consider that someone, somewhere (probably your DBA) will still have access to the SYS account or SYSDBA privileges in order to start/stop services, perform backup and recovery, install patches, etc. and will be able to circumvent your custom roles should they choose to do so.

    Mohamed Esmaelandrewmy
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 14, 2020 10:47AM
    pmdba wrote:Sounds like you have what you need for the Sec Admin role. You can create a custom DBA role as a copy of DBA (call it dba_lite ) and remove what you don't want it to have, but consider that someone, somewhere (probably your DBA) will still have access to the SYS account or SYSDBA privileges in order to start/stop services, perform backup and recovery, install patches, etc. and will be able to circumvent your custom roles should they choose to do so.

    Of course, at least one person should have legitimate need to stop/start the database and generally have unrestricted access as sysdba.  And if you can't trust that person, then you have hired the wrong person for the job.

    BPeaslandDBApmdbaMohamed Esmaelandrewmy
  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jan 17, 2020 12:43PM

    As I also mentioned above, the default roles can be automatically restored during some patch installations or during an upgrade. It is important to note that when this happens, you will NOT be notified. The patch/upgrade script will just do it without telling you, so by modifying a pre-defined role you run the risk not only of breaking some internal part of Oracle, but of causing future unauthorized privilege escalation through the installation of required patches and updates. For custom needs, always create custom solutions; don't modify default configurations.

    Mohamed Esmael
  • Mohamed Esmael
    Mohamed Esmael Member Posts: 15
    edited Jan 19, 2020 1:16AM

    Can I restricted/Control the privileges of SYSDBA Access ?

  • Mohamed Esmael
    Mohamed Esmael Member Posts: 15
    edited Jan 19, 2020 3:38AM

    is there list of Patch/Script that will restore predefined roles with predefined privileges?

    As your recommendation  I think the customized role will do the job 

  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jan 20, 2020 7:53AM

    No, there is no way to restrict SYSDBA, other than to be careful who you give that privilege to. The commands to recreate the role are buried in the $ORACLE_HOME/rdbms/admin/sql.bsq script.

    Mohamed Esmaelandrewmy