[OT] locked out of SDE account

[LeoDonahue]

...this would be a good time to make
sure that:
1) The SDE user password is very closely held,
2) The SDE user doesn't own any spatial tables,
3) No user data is located in the SDE tablespace,
4) The data ownership account isn't used for browse access,
5) All accesses to tables are performed through GRANTs to roles.

Ideally, each user would connect to the database as themselves (no shared login),
and common MXDs would have the connection properties set so that the password
isn't retained.
- V

Just starting a friendly discussion on this topic.  Assuming ArcSDE 10.0 in SQL Server.

If I create a an mxd with a layer added via a shared database login, and that login happens to be the data owner account, only the layer in the map document is open to a security risk such as edit access, unless, the data layer in question is registered as versioned and the data owner does not have edit access to the versioned layer.

Should data owners have edit access to their owned layers?  Who owns data in your SDE - multiple users or a single user?

Also, if we add users to roles and grant access to roles, and then some user says "please publish my map", would you want to make sure that all the layers in their mxd are connecting with a read only permission to the SDE layers?  But you know that said user also has edit access to some of these layers.  Who updates the mxd so that it has read only access to all referenced layers?

In some situations, are headless database accounts ok? (not headless windows accounts)

[VinceAngelo]

If you publish an MXD with the owner account password saved, then anyone
can drop all the tables owned by that user.

It's not possible to make it so that the owner doesn't have access to their
own tables.  At the lowest level, they can always use SQL to trash the
contents of the database.

Keep in mind that I have access to dozens to hundreds of geodatabases; I've
used many different access models, but mostly I try to have multiple logical
owners (e.g., water, parcels, highways, basemap), all headless, with multiple
roles to capture the necessary access protocols, then let individuals connect
as themselves, and mapping applications as read-mostly headless users.

It depends on the organization, but publishing a map usually involves making
sure all the data is from approved sources accessible by the publishing user,
to which that user has appropriate permissions (an editing application would
need edit access to the feature class(es) in question).

The only real objections I've heard on the subject of headless users is from
grammarians, who ask, "How can a user be without a head?"

- V

[LeoDonahue]

I just want to be clear that I am not arguing with you, but now I'm confused as to how a data owner can do that.

User permissions for geodatabases in SQL Server

This shows that a data creator does not have edit access by default.  Only enough permissions to load the data.

If I create an mxd with a layer added by a data owner account, that has the minimums permissions listed in the link above, and I give it to someone on my network who does not have a .sde connection file to my geodatabase, how do they gain access to my SDE geodatabase from this mxd?

Are you saying the username/password is available in the mxd in clear text?

If your default version of your geodatabase is set to Protected, I'm still fuzzy on how the data owner account can get edit access on a protected version.  Help me understand?

[VinceAngelo · Accepted solution]

That documentation doesn't say what the accesses of the user are, it details the
minimum database permissions necessary for the role.  The owner *ALWAYS*
has DROP TABLE and SELECT/INSERT/UPDATE/DELETE permissions on its own
tables.

The password is encrypted, but decryptable (not one-way, since it must be
forwarded to the database).  The API doesn't let you extract the password,
but that doesn't really matter, since the connection can be used maliciously
without it.

The versioning security model is imposed by ArcObjects, not at the database
level, so any user with the ability to excute SQL can destroy data.

- V

[LeoDonahue]

Very helpful information.

So, who do you let be data owners?  Would you as the sde admin want to control who loads/owns data in your database?

[VinceAngelo]

That's where tech ends and politics starts.  If the shop is small enough, there's only
one player manning all the hats, but as the organization gets larger, roles need to
be more concretely defined.

- V