topic Re: Privileges for Oracle roles does not work in Data Management Questions

Privileges for Oracle roles does not work

IvanGnevanov — Sun, 03 Nov 2013 11:57:21 GMT

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

Re: Privileges for Oracle roles does not work

VinceAngelo — Mon, 04 Nov 2013 00:12:14 GMT

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

Re: Privileges for Oracle roles does not work

IvanGnevanov — Tue, 05 Nov 2013 02:26:58 GMT

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

Re: Privileges for Oracle roles does not work

VinceAngelo — Tue, 05 Nov 2013 04:26:53 GMT

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

Re: Privileges for Oracle roles does not work

IvanGnevanov — Tue, 05 Nov 2013 05:02:27 GMT

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

Re: Privileges for Oracle roles does not work

VinceAngelo — Tue, 05 Nov 2013 11:03:47 GMT

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

Re: Privileges for Oracle roles does not work

IvanGnevanov — Wed, 06 Nov 2013 04:33:36 GMT

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

Re: Privileges for Oracle roles does not work

WilliamCraft — Sat, 14 Dec 2013 03:15:15 GMT

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.

Re: Privileges for Oracle roles does not work

IvanGnevanov — Mon, 23 Dec 2013 07:51:02 GMT

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

Re: Privileges for Oracle roles does not work

WilliamCraft — Mon, 23 Dec 2013 13:54:47 GMT

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!