PL/SQL block with multiple queries

Oracle DB 12c Multitenant

I'm trying to combine two tasks in to one pl/sql block. I have the task of removing privs on certain tables from public then granting them to all the database users besides public. The issue is having two FOR statements inside the block. how can I accomplish this?

   -- prevent any further connections
      IN (SELECT username
            FROM dba_users
           WHERE  username <>'PUBLIC');

FOR r IN (SELECT table_name
                FROM dba_tab_privs
               WHERE table_name IN ('DBMS_LOB',
      -- grant execute
    EXECUTE IMMEDIATE  'Grant execute ON ' || r.table_name || ' to user.username
   EXECUTE IMMEDIATE 'revoke execute on <Priv> from Public';
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I might not do this in PL/SQL.  I would probably do the revokes from public as single statements in a SQL script but since you want it, this should be close.

I don't have a database where I want to test this so it might have an issue or two.

I removed the WHERE on the dba_users query because PUBLIC is a ROLE not a USER.
	for my_object in (
		select object_name from dba_objects where object_name in (
	) loop

		EXECUTE IMMEDIATE 'revoke execute on ' || my_object.object_name || ' from Public';

		FOR my_USER IN (
			SELECT username FROM dba_users
		)    LOOP
		    EXECUTE IMMEDIATE  'Grant execute ON ' || my_object.object_name || ' to ' || my_user.username

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Just so you are aware, the grants to PUBLIC on DBMS_LOB, DBMS_SQL, DBMS_JOB, and DBMS_OBFUSCATION_TOOLKIT are all default grants that are part of building the database.  Oracle has made it quite clear that if you revoke these privileges, your database will be unsupported and may become unusable.  I'm sure that there is still a Metalink article out there about it.

Also, as they are part of the standard grants on these objects, be aware that patching would likely reapply the privilege.
slightwv (䄆 Netminder) Commented:
Revoking execute from PUBLIC on those packages is fairly common to any shop that runs security scans.  I'm familiar with the STIG and it has you remove them.  I had a few more to remove.

You need to be careful but it can be done.  I had to do it on all my databases.

>>I'm sure that there is still a Metalink article out there about it.

You would be correct.  A few...
Problems After Revoking Execute On DBMS_SQL, DBMS_JOB, DBMS_LOB, DBMS_RANDOM, and DBMS_OBFUSCATION_TOOLKIT From PUBLIC (Doc ID 1165830.1)

Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

However, those just talk about revoking and not granting them back to specific users.

>>be aware that patching would likely reapply the privilege.

100% agree!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
The scans just look for anything granted to PUBLIC.  I've dealt with plenty of them.  The sad part is the auditors running the scan have no idea what they are talking about.  We always refer to the Metalink documents as the reasoning to not do them.  There used to be one that was quite clear in saying that if you revoked the privileges from PUBLIC, they considered your database unsupported.  It is probably an old one and we keep referencing it because the auditors can't see Metalink.

I have had auditors tell me that I had to revoke privileges on DUAL and DIANA.  I asked if they would support the database if I did that because I know Oracle won't.  In fact, I believe if you do that you can't even start the database.
slightwv (䄆 Netminder) Commented:
>>if they would support the database if I did that because I know Oracle won't

I've used similar...  ;)
Geert GOracle dbaCommented:
auditors and database knowledge don't mix ...

an auditor asked for the output of user$ once
i responded with that question being something a hacker would typically ask, so they could attempt to break in

i usely reply with the a link to a document, without being very specific with this:
"your request will cause severe problems in our system and will not be followed.
please read this whole document for the reasons.  
please don't request modifications for items which you do not fully comprehend or can asses the impact of"

it's a nasty reply, but they usually back off
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.