Using concatenation to form grants.

[ChrisQuick]

We are developing an application that will hit against an Oracle 8 database. There will be only about 20 users whose permissions will be assigned at the column level. If we to assign permissions by roles there would be about 1700 or so roles created (please don't ask why) for just 20 users. We are trying to convince a client that using roles would be harder in the long run.<br>
<br>
We am trying to prove to the client that you can execute grants dynamically using slq concatenation, but want to have all of our ducks in a row first.<br>
<br>
We have tried the following to grant update privileges at the column level but it doesn't work.<br>
<br>
SELECT 'grant update (' ¦¦COLUMN_NAME FROM ALL_COL_PRIVS WHERE TABLE_NAME='atablename' AND GRANTEE='username1';¦¦') on atablename TO 'username2';<br>
<br>
Can anyone suggest what the correct syntax is?<br>

 

[MikeLacey]

Hi Chris,<br>
<br>
This looks interesting, but could you give us a bit more info?<br>
<br>
1) What development environment are you using? (you may need to compile - prepare - the resultant sql statement if you're using pure sql*plus)<br>
<br>
2) Could you give an example of the SQL statement that you want to produce?<br>
<br>
I'll look at this again when I'm in the office tomorrow as I don't have an Oracle DB here.<br>
<br>
Mike<br>
<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[yaffle]

Chris, this sort of thing is usually done in two passes - use a select statement to generate your script (i.e. use spool in sqlplus), then run it. The select would probably look like:<br>
SELECT 'grant update (' ¦¦COLUMN_NAME¦¦') on '¦¦TABLE_NAME¦¦' TO username2;'<br>
FROM ALL_COL_PRIVS<br>
WHERE TABLE_NAME='atablename'<br>
AND GRANTEE='username1';<br>
You might wish to do it programatically, in which case you could declare a cursor for the select statement, fetch each grant statement and execute it using dynamic sql.<br>
1700 roles sounds a lot though; what sort of app gets that complex from such a small user base ?

 

[ChrisQuick]

The application is a for a Geographic Information System we are developing for a federal client. In this GIS, there are different &quot;layers&quot; of data, for example, water lines, water valves, water manholes, etc... In all there are about 200 or so layers in this client's system. <br>
<br>
Each layer has anywhere from one to ten tables that are used to store information about the features within each layer, although there are some overlaps. Within each table, there can be anywhere for 50 to 70 columns, with different people responsible for the columns content, about three people per tables.<br>
<br>
The client's DBA wants to create a distinct role for each user for each table they are responsible for maintaining any content in. So if a user has a responsibility for maintaining content in 10 tables, they would have ten individual roles once per table. Also because the Data Standard the Feds use (call the Spatial Data Standards) have provisions for having somewhere in the neighborhood of 1700 different layers, their DBA wants our company to go ahead and create roles for not only the layers that they have, but for any future layers that might be developed, so that they don't have to add any new roles if new layers are developed.<br>
<br>
From our discussions with their DBA, its somewhat apparent that the only way they have EVER managed permmissions is by role not by user, and we have been unsuccessful so far in convincing them that you can even do it by user without it being a real headache. If we do it the way their DBA wants, it will definitly we be, not just for our company but also to the client in the long run. Who in their right mind wants that many roles for so few people????<br>
<br>
Frankly, managing permission by user at the column level really isn't all that hard, but if we can show there DBA any &quot;easy&quot; way of doing it, by concatenation or some other means ( or show their boss ), we can save every one some grief in the long term.<br>
<br>
<br>
yaffle: Thanks, your solution works perfectly!!<br>
<br>

 

[yaffle]

Chris, I see your problem - what'd we do without government eh ? ;-)<br>
If you are forced down the role route though, it'd be worth remembering that role permissions can be nested in Oracle, so instead of assigning n roles to each of 20 users, you could create a type_a_user role, grant it your subset of 1700 individual permission roles, then grant type_a_user to your 20 user accounts.<br>
I may be involved in a project with similarly abstruse security requirements later in the year, so I'd be interested to know how you get on.