10 Replies Latest reply on Jan 20, 2020 12:53 PM by pmdba

    Customize pre-defined role

    Mohamed Esmael

      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

        • 1. Re: Customize pre-defined role
          pmdba

          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.

          • 2. Re: Customize pre-defined role
            Emad Al-Mousa

            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

            • 3. Re: Customize pre-defined role
              Mohamed Esmael

              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

              • 4. Re: Customize pre-defined role
                Mohamed Esmael

                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

                • 5. Re: Customize pre-defined role
                  pmdba

                  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.

                  • 6. Re: Customize pre-defined role
                    EdStevens

                    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.

                    • 7. Re: Customize pre-defined role
                      pmdba

                      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.

                      • 8. Re: Customize pre-defined role
                        Mohamed Esmael

                        Can I restricted/Control the privileges of SYSDBA Access ?

                        • 9. Re: Customize pre-defined role
                          Mohamed Esmael

                          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 

                          • 10. Re: Customize pre-defined role
                            pmdba

                            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.