so the run down on this is we have a peice of software we got from a vendor DBA is the owner of all tables, so all users added to the DB have DBA rights otherwise they can not even login to the software becuase of the way the queries were wrote. so my question is there anyway to make it so all users do not need the DBA auth and be in the DBA group? this is a huge security hole and the vendor told me they are fine with this config and not to install the DB client, ahhhhhhh. Anyway this just annoys the crap out of me as we are trying to secure our infrastructure not leave huge holes.
asked 31 Aug '11, 12:43
You really need to have you vendor address this issue as they are best able to determine the impact of schema changes on their application(s). That said, this is actually a common design problem that can generally be fixed by granting permissions on database objects. In some cases, the problem is made difficult to solve if the queries do not use owner names i.e., dba.t vs. t. In that case, the tables may not be visible to the user. To solve that, the user must either own the table or be a member of the group that owns the table.
At a high level, one way to address this without significant application rework is
The tricky part could be step 4. You could be naive and grant all permissions -- Select,Insert, Update, Delete for tables and views and Execute to procedures and that should mimic what granting DBA authority to an user was accomplishing.
answered 31 Aug '11, 14:34
If you make the DBA user a group and then assign membership in that group to your users, they will have access to the tables owned by DBA but will not have DBA authority. For example:
-- as user dba grant group to dba; grant membership in group dba to graeme; grant all on mytable to dba;
User "graeme" can now access table
answered 31 Aug '11, 14:32