4 Replies Latest reply on May 16, 2015 11:50 AM by PKR

    How to prevent select access to tables present in specific schema from users having "select any table" privilege assigned

    1038595

      Hi,

       

      In one of the db instance we have few roles created which has 'SELECT ANY TABLE' privileges assigned. Now this role is assigned to lot of users.

      In the same instance we have 2 schemas (each have 25 tables) for which we would like to restrict access to any other schema. Basically restrict users who has role assigned with 'SELECT ANY TABLE' privileges on tables present in the 2 schemas. DB version is 11.2.0.3

       

      One option we were thinking was:

       

      > Create a new ROLE for the users who need select privilege on all the tables.
      > GRANT select privilege to all the required tables within the new role created.
      > REVOKE 'SELECT ANY TABLE' privilege from all the user defined roles and users.

       

      Would this be a good option or is there a better way of doing this?

       

      Regards,
      Shakti

        • 1. Re: How to prevent select access to tables present in specific schema from users having "select any table" privilege assigned
          Cobert

          Usual rule of thumb with grants is give the minimal possible access to do the job. Saves people getting themselves into trouble and others getting in trouble.

          Select any xx would break quite a few rules (Sarbanes Oxley, COBIT and PCI compliance) as there's no breakdown by role for access. You also have to consider audit trails and independence from interference / centralised auditing.

           

          What you've suggested is a good start, if you have a security / compliance team it's best to liaise with them rather than redevelop adhoc roles on what you think is best, at least then you can enhance role based access or limit access to the DB altogether via your middle tier.

           

          You need to identify what access is required, have business sign off on this and then create and test roles before rolling out to business. If you have a compliance team they will usually give you a steer so at least you don't become embattled for revoking access.

          • 2. Re: How to prevent select access to tables present in specific schema from users having "select any table" privilege assigned
            gaverill

            While I tend to think that the solution you outlined above is the better approach, simply because you are getting rid of such a powerful grant, another possible solution might be to put row-level security policies on the 25 tables using a predicate function something like this:

             

            create package Security_Policies
            as
            ...
                function Non_SelectAnyTable_Users
                is
                begin
                    return    q'[not exists (
                            select    1
                            from    SYS.SESSION_ROLE r
                            join    SYS.ROLE_SYS_PRIVS p on
                                    p.ROLE = r.ROLE
                                and    p.PRIVILEGE = 'SELECT ANY TABLE'
                        )]';
                end;
            ...
            end;
            

             

            While the users can still SELECT the tables, they can't see any rows.

             

            For what it's worth...

             

            Gerard

            • 3. Re: How to prevent select access to tables present in specific schema from users having "select any table" privilege assigned
              Aish13

              Hi Shakti,

               

              Incase you want to restrict the access to schema wise, you can create a dedicated Read (or select) role for each schema and then map the roles to the users/application groups as needed.

               

              Say you have  schema A and B, so you now create 2 roles for each schema A_R and B_R, then you can assign these roles to the users as needed.

              the role A_R can hve a select on all the tables of schema A, and similarly for schema B

               

              This would not only help you in restricting the access, but would also make your role management pretty scalable and simple to manage.

              Say people belonging to Team A need access to only schema A, so you can then go ahead and grant the role to those users. and not unnecessarily grant them access to schema B.

               

              Hope this makes sense

               

              Regards,

              Aish

              • 4. Re: How to prevent select access to tables present in specific schema from users having "select any table" privilege assigned
                PKR

                Hi

                Best approach............

                 

                Oracle Database Vault Realms can be used to protect an entire application or a specific set of tables within an application, providing highly flexible and adaptable security enforcement.

                 

                Rajesh.PK