Feature class with no privileges granted can be seen by all users

1718
7
06-03-2014 09:57 AM
Highlighted
New Contributor II
Hello,

I have imported a new feature class into our SDE Geodatabase, sde user is the owner.  I have not granted any privileges to roles or users through ArcCatalog and SQL backend shows no permissions to the table, but all of the GIS users can see tha data.  I am working with our SQL DBA to figure out what is going on, but thought I would put it out here to hopefully get suggestions on where to look.  We are using SQL Server 2012 and ArcGIS desktop 10.2.1.  Thanks for any help you can offer!
Reply
0 Kudos
7 Replies
Highlighted
Esri Esteemed Contributor
Best practice is to AVOID loading spatial data as the 'sde' user.

Is the feature class in a feature dataset? 

What do you mean by "all of the GIS users can see [the] data"?  Can they execute
a "SELECT * FROM tablename" from a SQL client?

- V
Reply
0 Kudos
Highlighted
New Contributor II
I am loading the data through ArcCatalog, not SQL.  Who is recommeneded to own the data?

No, it is not in a dataset.

All GIS users can see the data in ArcGIS desktop and use it.  We did test a select query through SQL backend and it was successful as a user who theoretically should not be able to see the data.
Reply
0 Kudos
Highlighted
Esri Esteemed Contributor
Best practice is to create one or more users to own spatial data, then load data as that user.
Use of the 'sde' user for anything other than geodatabase instance management can result
in geodatabase corruption.

The RDBMS software has exclusive management of security in a geodatabase.  If you've got
all the users mapped to SysAdmin rights on the database server, they will always have permission
to access all tables, not matter what has been explicitly granted.

- V
Reply
0 Kudos
Highlighted
New Contributor II
No user in the geodatabase has sys admin rights, not even the 'sde' user.
Reply
0 Kudos
Highlighted
Occasional Contributor III
please list the privielges the users have ? (do they have for example "select any table" privilege )


as vince mentioned, create a new "schema" so you can create and load the data in it.

in sql server case you can use "dbo".

Regards,
Reply
0 Kudos
Highlighted
New Contributor II
The reader role has view all tables permission. We denied select in the reader role for this feature class and granted select for only those departmental roles that need to see it.

Now, the bigger issue of sde being the owner of the data....can we change the owner of the data in SQL?  Or should we create an entirely new database and reimport data with new owner?

Thank you for all of your help!
Reply
0 Kudos
Highlighted
Esri Esteemed Contributor
I'd recommend avoiding the use of default roles like "reader", rather than changing
them to be non-standard.

The only way to change ownership is to delete the tables (using ArcGIS, if that was
used to create them), then reload using a different user.

- V
Reply
0 Kudos