DBA owner of all tables, any way to not have all users have DBA rights?

[Former Member]

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.

[chris_keating · Accepted solution]

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

1. convert the user DBA to a group
2. revoke DBA authority for users who should not have that authority
3. grant membership to the group DBA to the users
4. grant appropriate permissions to the group DBA to the appropriate database objects

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.

[Former Member · Accepted solution]

not for sure what this means, but if i remove the DBA auth even with them being in the DBA group it still breaks. they can't login. really not making sense unless they are doing something funky.

[chris_keating · Accepted solution]

That is not a default behavior.

What version of SQL Anywhere is being used? In newer versions, you can have a login policy that limits the number of non-dba connections or prevents non-dba users from connecting. The schema may also have a login_procedure that checks the user and only permits those with DBA authority. Both of these are user implementations.

What specifically is the behaviour of "they can't login". Is there a message or error? What is the exact text?

[Former Member · Accepted solution]

first error: failed to check machine Id...

second error: you must initialize this machine before running

third error: failed to clear broken connections

forth error: software has encountered an unknown error, shutting down.

add user back to DBA auth and all goes away

ASA 8.0

[graeme_perrow · Accepted solution]

Please use comments rather than submitting new answers when having back-and-forth discussions like this. Thanks

[graeme_perrow · Accepted solution]

None of these are SQL Anywhere error messages. These are likely caused by your application, which probably does some other things before accessing the objects you've granted access to. You'll have to figure out what else it's doing and grant the appropriate permissions.

[chris_keating · Accepted solution]

These errors are not SQL Anywhere errors.

Here are potential explanations: 1. You have missed granting permissions on objects that are being accessed at login time. See point 4 in my answer. Tables are only one level of objects to which permissions may need to be granted. Are views and/or procedures being used by the application? 2. The application may be invoking statements that require DBA authority. If this is the case, there may be little that can be done with the vendor since it would likely means changes at the application level.

You can learn about what requests are being made with request level logging. See -zr in the online help for more details.

[Former Member · Accepted solution]

you were correct, the execute on the SP is what i forgot and it now works. you guys are awesome sorry for being such a sybase newb.

sweet

[graeme_perrow]

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 dba.mytable but does not have DBA authority.

[Former Member]

From what i have found if i remove DBA authority from a user it breaks, so i am wondering if they aren't just using table name because they are all in the DBA group, but if they do not have both then it breaks.

What would cause the DBA authority to break it? becuase if they are in the DBA group i would think they would access to the tables still.

The vendor is basically telling me deal with it this is the way it is and we are not doing anything to change it. 😞

[graeme_perrow]

Make sure you grant access on the objects to DBA. This seems counterintuitive (you need to grant select on t to dba when dba owns t), but it's necessary to allow members of the group to access that object.

[Former Member]

wow that does seem odd that you have to do both like that

[Former Member]

i added select rights with the DBA auth removed and got the same result. same login errors.