Currently, all our application tables are owned by dba.
Is this considered good practice?
If no, is there an easy way to change ownership of all our application's
tables?
Thanks,
Doug

I was taught that this is not a good idea. The reasoning is that all your
users will have DBA powers. Make a group with non-DBA powers and put all the
normal users in there. Make all the tables belong to that group, then you
don't need to have long table names (e.g mygroup.table1) in your queries.

This advice was from the days when a company had one giant Oracle DB on a
Unix box somewhere so I don't know how relevant it is to single user
embedded DB.

The DB can also contain all the same named tables more than once if they
belong to different groups. Again more of an enterprise consideration than a
stand alone one.

Certainly I think controlling access is relevant for any multi-user DB. Evan
at departmental level it is good to show some consideration for security.
Quite what the best practice method is I hope someone more learned will jump
in and say.

Regards
Clive

"Doug Stone" <doug.stone (AT) res-q (DOT) com> wrote

Doug,

it's no problem when the schema is owned by DBA. (Some use dbo for
ASE/MS-SQL compatibiliy).

The question is whether your users connect as DBA (or the owner of the
schema) or not. *That* would give them way too much power - usually they
would only need to select from some tables/views and
insert/update/delete with other tables/views.

The statement "The reasoning is that all your users will have DBA
powers." is not correct, if your users do not connect as DBA, methinks:

You can turn DBA into a group and add users to that group.
By doing so, *users don't get DBA rights*. They only get the possibility
to access DBA's tables without qualifying them, i.e. they can do
SELECT * from MyTable
instead of
SELECT * from "DBA".MyTable

But unless they are granted select rights and the like, they won't be
able to access the table. This is because the owner of a table has
implicit access rights which are not "inherited" to group members.
(Under the assumption that one has not granted access rights for the DBA
user, himself! - those rights *would be inherited*).
Additionally, the particular "DBA" priviledge isn't inherited, either.


If your schema is owned by DBA, my recommendation would be to
a) turn DBA into a group
b) create a group "users" and make it part of the DBA group
c) grant the according select/execute rights to that group
d) create your particular users and make them part of the "users" group

Repeat steps b-d if there are different groups of users with different
access needs.

HTH
Volker

P.S. AFAIK, "changing ownership" is not possible with SA. You would have
to drop and recreate all objects (possibly during a reload).





Am 26.01.2010 11:51, schrieb Clive Collie:

That is absolutely NOT true with SQL Anywhere... I mean, it does not
HAVE to be true just because DBA owns everything. SQL Anywhere is not
anything like Oracle when it comes to defining privileges and users.

The generic rule DOES apply to both Oracle and SQL Anywhere: "Do not
give administrator privileges to anyone other than administrators."

....but the specific rules are very different. Here's the simplest safe
setup for SQL Anywhere:

(1) Create all your tables owned by DBA. Everyone does that. No reason
not to.

(2) Do this one time:

GRANT GROUP TO DBA;

(3) Do this for each end user id, so that the tables can be visible
(no more "not found" errors):

GRANT MEMBERSHIP IN GROUP DBA TO uuu;

Note that the "DBA authority" is one of many that is NOT inherited via
membership in a group. See this section in the V11 Help: SQL Anywhere
Server - Database Administration » Configuring Your Database »
Managing user IDs, authorities, and permissions » Database permissions
and authorities overview

Also note that the DBA user id and the DBA authority are two different
things. A new database, by default, has a DBA user id with DBA
authority, but you can change that (but don't do that in real life,
just change the password for the DBA user id).

(4) Do something like this for each end user id, depending on what
privileges you want them to have:

GRANT SELECT ON ttt TO DBA; -- stops "permission denied" errors

I have pasted a demonstration script below, for your dbisql
experimentation pleasure.

Note that if you have zillions of end user ids, you may want to set up
other groups for the purposes of inheriting SELECT, UPDATE, etc,
privileges. That's a whole other topic... the important thing to
remember is that the DBA authority is NOT inherited.

Breck


---------------------------------------------------------------------
GRANT GROUP TO DBA;
GRANT MEMBERSHIP IN GROUP DBA TO uuu; -- stops "not found" errors
GRANT SELECT ON ttt TO DBA; -- stops "permission denied" errors
-- The full "DBA authority" is NOT inherited by uuu

---------------------------------------------------------------------
-- Testing...

--------------------
-- Connect as DBA...

CREATE TABLE ttt ( pkey INTEGER NOT NULL PRIMARY KEY );

GRANT CONNECT TO uuu IDENTIFIED BY sql;

--------------------
-- Connect as uuu...

SELECT * FROM ttt; -- Table 'ttt' not found

--------------------
-- Connect as DBA...

GRANT GROUP TO DBA;
GRANT MEMBERSHIP IN GROUP DBA TO uuu; -- stops "not found" errors

--------------------
-- Connect as uuu...

SELECT * FROM ttt; -- Permission denied...

--------------------
-- Connect as DBA...

GRANT SELECT ON ttt TO DBA; -- stops "permission denied" errors

--------------------
-- Connect as uuu...

SELECT * FROM ttt; -- works

DROP TABLE ttt; -- Permission denied...





That is aOn 26 Jan 2010 02:51:27 -0800, "Clive Collie"
<c.collie_doesnt_like_spam (AT) btclick (DOT) com> wrote:

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

On 25 Jan 2010 16:49:34 -0800, "Doug Stone" <doug.stone (AT) res-q (DOT) com>
wrote:

Yes, it's good practice.
No, there is no easy way.
See my response to Clive.

Breck

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail