How to Configure Authorization to Assign Same Privileges to Multiple Grantees?

[mase310]

My company has recently purchased the PL/SQL Developer software and is in the process of setting-up the Authorization for the 6.0.6.947 version.

We need to setup the same authorization privileges for a group of users. The users are in the same Oracle role, but in PL/SQL it seems that I need to create each user individually and assign privileges manually for each individual user. This is time consuming and prone to human error. There must be a way to assign PL/SQL application-level privileges to a group of users all at once.

Could someone please help to instruct me as to how this could be done?

 

[Marco Kalter]

You can use the Copy and Paste buttons to the right of the Privileges
list:

1. Add a user/role and select it
2. Add the privileges
3. Select the privileges and press Copy
4. Add a new user/role and select it
5. Press the Paste button
6. Add a new user/role and select it
7. Press the Paste button

And so on...

 

[mase310]

>>> "Allround Automations Support" 01/31/2006 10:23:08 AM >>>

> I had previously tried what you suggested with the copying and pasting. It does not work for me.

Hmm, this seems to be a bug. We'll fix it. The only workaround I can think
of is to use SQL:

insert into sys.plsqldev_authorization a1
(grantee, name)
(select 'USER1', a2.name
from sys.plsqldev_authorization a2
where a2.grantee = 'USER2')

This copies the privileges from USER1 to USER 2.

> What about giving privileges to an Oracle role? Does giving the
> privileges to the Oracle role via PL/SQL Developer automatically give it
> to all the accounts in the role, so that anyone in that role gets those
> privileges when loggin into PL/SQL Developer?

Yes. An also if the role is granted to a role that is granted to a user.

 

[Marco Kalter]

The query is wrong though: it copies the privileges from USER2 to USER1.

 

[dcurley]

Sorry for butting in, but what are you talking about? Is this about some way of distributing PL/SQL Dev to users in an organization without each of them needing the license code (which would be cool), or is this just an Oracle select/insert/etc. grant question?

Dave

 

[Marco Kalter]

This has nothing to do with the distribution of PL/SQL Developer. The authorization feature allows you to grant PL/SQL Developer functions to Oracle Users or Roles in specific databases.

To distribute PL/SQL Developer to many users, create a server based installation as described in chapter 2 in the User's Guide.

 

[dcurley]

You can't use roles to assign rights on objects to be used in compiled code. See this Ask Tom article for more info: http://tinyurl.com/akvjn.

This should get you a script (the first column) you can use to duplicate the grants already made to a role or user to any other role or number of users. Marco will recognize it as the query PL/SQL Dev uses to populate the Roles > Object Privs folder with a column for the script added.

WITH u AS (SELECT username FROM all_users u WHERE username IN ('user1 needing rights','user2...',etc))
SELECT distinct 'grant ' || p.PRIVILEGE || ' on ' || p.owner || '.' || p.table_name || ' to ' || u.username || ';',
p.owner object_owner,
p.table_name object_name,
o.object_type,
o.status
from sys.dba_objects o,
sys.dba_tab_privs p,
u
where p.grantee = 'role or user to duplicate'
and o.owner = p.owner
and o.object_name = p.table_name
and not (o.object_type in ('PACKAGE BODY', 'TYPE BODY','TABLE PARTITION'))
order by o.object_type, decode(p.owner, user, 0, 1), p.owner, p.table_name

Dave

 

[Marco Kalter]

This is not about Oracle privileges, but PL/SQL Developer functions. For example: in a production database you may want to disable the Table Definition Editor. PL/SQL Developer functions can be granted to Oracle users and roles.

The Authorization chapter in the User's Guide explains things in detail.

 

[dcurley]

Gotcha. We're so very, very far from having anything like that allowed here it didn't even occur to me....

Dave