Privileges for Oracle roles does not work

[IvanGnevanov]

Hello!

I have an issue: there are oracle users and roles, created for managing access to the data.
I gave the privileges on the ArcSDE feature datasets to the oracle roles, but the users in that roles still do not have access to the data.

[ATTACH=CONFIG]28836[/ATTACH]

The configuration is: ArcGIS Desktop 10.1 -> ArcSDE 9.3.1 -> Oracle 9i

Any ideas?

Ivan

[VinceAngelo]

It's been most of decade since I last used Oracle 9i, but I've never had any difficulty
using roles to manage access in Oracle.  How exactly are you going about assigning
permissions?  What geometry storage are you using?

You should know that Oracle retired 9iR2 long ago, and ArcGIS 9.3.1 rolls off into
Retired status soon, so you'll be working without a net soon.

- V

[IvanGnevanov]

Vince,

There has been created oracle roles for different data access (for example, read-only access). SELECT ANY TABLE system privilege has been granted to the roles. The roles has been granted to different oracle users.
Then I used ArcCatalog to apply a privileges on feature datasets to that roles (for example, SELECT only).
The users in the roles do not have any access to the data unless I give the privileges to the user directly.
Maybe it is neccesary to give more base privileges to the roles, not only to the data itself?

Yes, that is the old software configuration and it is planned to move to contemporary releases in the near future.

Ivan

[VinceAngelo]

SELECT ANY TABLE access is a dangerous trap.  Don't go anywhere near it.

The proper way to grant access has three tiers:

• Tables


• Roles


• Users

You should explicitly grant role access to the tables (feature classes) using Desktop,
and explicitly grant user access to roles using SQL.

- V

[IvanGnevanov]

Vince,

Ok, SELECT ANY TABLE is a bad idea, but it was implemented after a fault to give access using roles.

You should explicitly grant role access to the tables (feature classes) using Desktop,
and explicitly grant user access to roles using SQL.

It is all done exactly as you say. The role access to tables is done using ArcCatalog, user access to roles in made using SQL.

Ivan

[VinceAngelo]

You'll need to give an example of a set of permission maps that doesn't work
as expected before we can help you.

- V

[IvanGnevanov]

Vince,

Here are the scripts for the role GEOPH_SURF and a user ABRAMOVA_MI.
------------------------

DROP ROLE GEOPH_SURF;

CREATE ROLE GEOPH_SURF NOT IDENTIFIED;

GRANT DELETE, INSERT, SELECT, UPDATE ON GEOPHYSICS.MINELEASE_T TO GEOPH_SURF;
GRANT DELETE, INSERT, SELECT, UPDATE ON GEOPHYSICS.S165_IDX$ TO GEOPH_SURF;

GRANT GEOPH_SURF TO SYS WITH ADMIN OPTION;
GRANT GEOPH_SURF TO ABRAMOVA_MI;

----------------------------------

DROP USER ABRAMOVA_MI CASCADE;

CREATE USER ABRAMOVA_MI
  IDENTIFIED BY VALUES '*'
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for ABRAMOVA_MI
  GRANT CONNECT TO ABRAMOVA_MI;
  GRANT GEOPH_SURF TO ABRAMOVA_MI;
  GRANT RESOURCE TO ABRAMOVA_MI;
  ALTER USER ABRAMOVA_MI DEFAULT ROLE CONNECT, RESOURCE;
  -- 1 System Privilege for ABRAMOVA_MI
  GRANT UNLIMITED TABLESPACE TO ABRAMOVA_MI;

--------------------------------------

The privileges for a feature dataset with a single feature class in a Desktop are:
[ATTACH=CONFIG]28887[/ATTACH]

In this case the user does not see the feature class, unless I give explicit rights:
[ATTACH=CONFIG]28888[/ATTACH]

Ivan

[WilliamCraft]

I'm not convinced that all of the permissions have been assigned correctly; mainly with the F, S, D, and A tables that correspond to your base table.  I see from you original screenshot that the object class in question is versioned as a State ID is referenced in the error.  This is why it's critical to use the ArcGIS Desktop tools when granting and revoking permissions to roles for given object classes... because doing so in Oracle can cause issues if you don't do it for all of the right tables involved. 

You will need to find out which F, S, D, and A tables correspond to your feature class's base table and grant the same permissions to those as you did with the base table.  Once you commit your permission SQL statements from within Oracle, close and re-open ArcGIS Desktop and try connecting again with a user from the role that was just given permissions.

EDIT: I've just re-read a few things in this thread.  You said that the user can see the data if permissions are given to the user directly rather than just to the role.  In that case, please check to see if the role in question for that user account is set to be a DEFAULT role in Oracle.

[IvanGnevanov]

EDIT: I've just re-read a few things in this thread.  You said that the user can see the data if permissions are given to the user directly rather than just to the role.  In that case, please check to see if the role in question for that user account is set to be a DEFAULT role in Oracle.

Thank you very much, William. You were exactly right about the default roles.

Ivan

[WilliamCraft]

Thank you very much, William. You were exactly right about the default roles.

Ivan

I'm glad it worked out for you.  Please mark the correct answer using the green check so others with the same challenges can identify the solution.  Have a great day!