topic Re: how to hide SDE Schema in Data Management Questions

how to hide SDE Schema

HaniuHokkaido — Wed, 10 Sep 2014 10:05:13 GMT

dear experts,

I am confused.

I create a new login "dbadmin" with Server Role : Public, Diskadmin, securityadmin,sysadmin, serveradmin and dbcreator

I already have a geodatabase with the system table under schema SDE. and also i have some tables under DBO schema

With this dbadmin, i create a Database Role: role_editor, and also i create user named "user_editor". User_editor is member of role_editor

In the Database Role property for role_editor, i specify permission for dbo: SELECT,INSERT,ALTER,EXECUTE,DELETE and UPDATE. For sde, i do not specify any permissions.

Question is:

1. when i log on as user_editor, I can see all tables in SDE schema. I can do "select * from sde.tabelxxx" too. Why ? Should SDE tables be hidden ?

2. Then I change the permission of sde in Database Role role_editor so that the SELECT is DENY. Then I cant do SELECT. Why should I specify DENY SELECT for SDE schema ? Shouldnt SELECT is automatically denied since i do not state the permission on SDE schema ?

3. Shouldnt it the other way around, i should specify permission in the Database Role for SDE schema instead of DBO ?

[Database: SQL Server 2012, ArcSDE 10.1]

Thanks

Re: how to hide SDE Schema

VinceAngelo — Wed, 10 Sep 2014 16:47:10 GMT

The tables created with SDE ownership at geodatabase install are critical to geodatabase functionality. You should not change any permissions on any SDE-owned tables if you want ArcGIS to work (implementing such changes without direct guidance from Tech Support staff makes the database unsupportable).

No other tables should ever be created with SDE ownership. The SDE login should be used exclusively for geodatabase administration.

I don't reuse roles created in the default RDBMS install (no matter which one) for geodatabase administration. I create my own roles for the permissions that are necessary, grant those roles necessary access to tables, and grant the roles to the appropriate users. I avoid use of DBO privileges under most situations where it is not absolutely required.

- V

Re: how to hide SDE Schema

HaniuHokkaido — Thu, 11 Sep 2014 02:38:31 GMT

I agree with you Vince. And i do not intend to give permission to view SDE schema, this is just a mere test.

I do not want this user_editor to see tables in SDE schema.

Why these tables in SDE schema are visible to user_editor, whereas the DBO tables are not visible ?

Why do i have to set schema permission in Database Role explicitly for DBO, by GRANT SELECT,EXECUTE, so that all tables are visible and I have to DENY SELECT for SDE schema so that this user_editor cant execute SELECT ? Shouldnt it be the other way around, i have to GRAND SELECT on SDE just to be able to see / SELECT SDE-owned tables ?

user_editor and role_editor are created by myself, not RDBMS's default

thanks

Re: how to hide SDE Schema

VinceAngelo — Thu, 11 Sep 2014 03:25:56 GMT

Users must have access to the contents of SDE system tables for ArcGIS to work. The PUBLIC pseudo-role is granted SELECT access to make this possible. You cannot remove SELECT access from SDE tables and retain geodatabase functionality. I recommend RDBMS documentation for details on available security model features, but counsel caution on the use of more exotic features -- If I don't want a user having access to SDE tables, I don't give them a login.

- V

Re: how to hide SDE Schema

HaniuHokkaido — Thu, 11 Sep 2014 03:35:03 GMT

Hi Vince,

So the SDE schema must be accessible to have geodatabse functionality. thats why the SDE tables are all visible.

Allright. I get it.. thanks for the answer Vince...