grant execute on package by DBA

[ciapecki]

I would like to find out if there is a possibility (as a non-DBA user)
to list all objects (owned by that user) including the grants done by
DBA on these objects.

I have a package called chrispack.
Whenever I want to expose a procedure or function from within that
package I create this function unpackaged and just return in the body
call to my package.
Then I can grant execute only on this unpackaged function/procedure.

e.g. in my schema USERA I have a function called DIGITS_ONLY:
CREATE OR REPLACE
FUNCTION digits_only(input_string varchar) return varchar
is
begin
return chrispack.digits_only(input_string);
end;
/

-- Grants for Function
GRANT EXECUTE ON digits_only TO public
/

Then I tried to log in with the USERB credentials

I can
select USERA.digits_only('asd2f1') from dual;
but as well:
select USERA.chrispack.digits_only('asd2f1') from dual;

this second call should not succeed!

when I check with:
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'CHRISPACK'
-- no rows returned

select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'DIGITS_ONLY'
-- 1 row as expected

How can I see who (I suppose DBA) granted EXECUTE on my whole package?

thanks,
chris

[ciapecki]

I tried as well:
revoke execute on chrispack from USERB;
*
ORA-01927: cannot REVOKE privileges you did not grant

chris

[s***@hccnet.nl]

Post by ciapecki
revoke execute on chrispack from USERB;
*
ORA-01927: cannot REVOKE privileges you did not grant
chris

Not surprising, isn't it?

[s***@hccnet.nl]

Comments embedded

Post by ciapecki
I would like to find out if there is a possibility (as a non-DBA user)
to list all objects (owned by that user) including the grants done by
DBA on these objects.

DBA is a ROLE not a PERSON!!

Post by ciapecki
I have a package called chrispack.
Whenever I want to expose a procedure or function from within that
package I create this function unpackaged and just return in the body
call to my package.
Then I can grant execute only on this unpackaged function/procedure.

This is, really sorry to say so, EXTREEMLY stupid.

Post by ciapecki
CREATE OR REPLACE
FUNCTION digits_only(input_string varchar) return varchar
is
begin
return chrispack.digits_only(input_string);
end;
/
-- Grants for Function
GRANT EXECUTE ON digits_only TO public
/
Then I tried to log in with the USERB credentials
I can
select USERA.digits_only('asd2f1') from dual;
select USERA.chrispack.digits_only('asd2f1') from dual;
this second call should not succeed!

You don't mention a version, so there is nothing to reproduce.

Post by ciapecki
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'CHRISPACK'
-- no rows returned

Obviously, as you never granted any privilege. Apart from that, you
are selecting from an incorrect data dictionary view.
ALL_ ... views lists anything YOU HAVE ACCESS TO.
As you don't have access to the package it isn't listed.

Post by ciapecki
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'DIGITS_ONLY'
-- 1 row as expected
How can I see who (I suppose DBA) granted EXECUTE on my whole package?

Just replace ALL by DBA.
And DBA is NOT a PERSON, it is a ROLE.

Post by ciapecki
thanks,
chris

[ciapecki]

Post by s***@hccnet.nl

Post by ciapecki
How can I see who (I suppose DBA) granted EXECUTE on my whole package?

Just replace ALL by DBA.
And DBA is NOT a PERSON, it is a ROLE.

as "a non-DBA user" I meant a user without DBA role!

It means I do not have access to DBA_ objects.

any other ideas?

thanks,
chris

[ddf]

Post by ciapecki

Post by s***@hccnet.nl

Post by ciapecki
How can I see who (I suppose DBA) granted EXECUTE on my whole package?

Just replace ALL by DBA.
And DBA is NOT a PERSON, it is a ROLE.

as "a non-DBA user" I meant a user without DBA role!
It means I do not have access to DBA_ objects.
any other ideas?
thanks,
chris

No, since some DBA-privileged account did grant access to your
package. Surprisingly this was SYS, and it happened when you granted
execute on your 'wrapper' procedure to PUBLIC. Since your 'wrapper'
procedure calls your packaged offering PUBLIC needs access to it to
run the 'wrapper', thus SYS, with some creative recursive SQL, granted
the necessary privilege on your package to PUBLIC. Unfortunately for
you REVOKE doesn't behave the same way:

revoke execute on retrieverows_only from public
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

So now PUBLIC has access to your package as well as your 'wrapper'.
If you REALLY want to keep that from occurring you'll need to actually
create a standalone procedure that performs the same work as your
packaged offering without calling the packaged counterpart. Which is
silly, really, since you'll have duplicated code. Why you didn't
simply grant execute on this package to PUBLIC in the first place is a
mystery -- what deep, dark secrets does this package unlock that the
unwashed can't access it?


David Fitzjarrell

[ciapecki]

Post by ddf
No, since some DBA-privileged account did grant access to your
package.  Surprisingly this was SYS, and it happened when you granted
execute on your 'wrapper' procedure to PUBLIC.  Since your 'wrapper'
procedure calls your packaged offering PUBLIC needs access to it to
run the 'wrapper', thus SYS, with some creative recursive SQL, granted
the necessary privilege on your package to PUBLIC.  Unfortunately for
revoke execute on retrieverows_only from public
*
ORA-01927: cannot REVOKE privileges you did not grant
So now PUBLIC has access to your package as well as your 'wrapper'.
If you REALLY want to keep that from occurring you'll need to actually
create a standalone procedure that performs the same work as your
packaged offering without calling the packaged counterpart.  Which is
silly, really, since you'll have duplicated code.  Why you didn't
simply grant execute on this package to PUBLIC in the first place is a
mystery -- what deep, dark secrets does this package unlock that the
unwashed can't access it?

Hi David

It's not entirely true. Please see below.

I just did a test on my local db 10gR2.

as user with DBA role called CHRIS.

CREATE OR REPLACE
function up(input_string varchar) return varchar2 is begin return
chrispack.up(input_string); end;
/
GRANT EXECUTE ON up TO public;

now I connected with a user TEST with minimal privileges

I can run:
select chris.up('asdf') from dual; -- ASDF
but I cannot run:
select chris.chrispack.up('asdf') from dual;
*
ORA-00904: : invalid identifier

This means that execute GRANT on up function that points to
chrispack.up function does not automatically GRANTS EXECUTE priv on
the whole package.


My question still remains, how can I (is it possible) to list
GRANTEES, GRANTORS on one of my objects (package) when I did not
execute that GRANT?

thanks,
chris

[ddf]

Post by ciapecki

Post by ddf
No, since some DBA-privileged account did grant access to your
package.  Surprisingly this was SYS, and it happened when you granted
execute on your 'wrapper' procedure to PUBLIC.  Since your 'wrapper'
procedure calls your packaged offering PUBLIC needs access to it to
run the 'wrapper', thus SYS, with some creative recursive SQL, granted
the necessary privilege on your package to PUBLIC.  Unfortunately for
revoke execute on retrieverows_only from public
*
ORA-01927: cannot REVOKE privileges you did not grant
So now PUBLIC has access to your package as well as your 'wrapper'.
If you REALLY want to keep that from occurring you'll need to actually
create a standalone procedure that performs the same work as your
packaged offering without calling the packaged counterpart.  Which is
silly, really, since you'll have duplicated code.  Why you didn't
simply grant execute on this package to PUBLIC in the first place is a
mystery -- what deep, dark secrets does this package unlock that the
unwashed can't access it?

Hi David
It's not entirely true. Please see below.
I just did a test on my local db 10gR2.
as user with DBA role called CHRIS.
CREATE OR REPLACE
function up(input_string varchar) return varchar2 is begin return
chrispack.up(input_string); end;
/
GRANT EXECUTE ON up TO public;
now I connected with a user TEST with minimal privileges
select chris.up('asdf') from dual; -- ASDF
select chris.chrispack.up('asdf') from dual;
              *
ORA-00904: : invalid identifier
This means that execute GRANT on up function that points to
chrispack.up function does not automatically GRANTS EXECUTE priv on
the whole package.
My question still remains, how can I (is it possible) to list
GRANTEES, GRANTORS on one of my objects (package) when I did not
execute that GRANT?
thanks,
chris- Hide quoted text -
- Show quoted text -

Not without DBA privilege.


David Fitzjarrell

[Gerard H. Pille]

Post by ciapecki
I would like to find out if there is a possibility (as a non-DBA user)
to list all objects (owned by that user) including the grants done by
DBA on these objects.
I have a package called chrispack.
Whenever I want to expose a procedure or function from within that
package I create this function unpackaged and just return in the body
call to my package.
Then I can grant execute only on this unpackaged function/procedure.
CREATE OR REPLACE
FUNCTION digits_only(input_string varchar) return varchar
is
begin
return chrispack.digits_only(input_string);
end;
/
-- Grants for Function
GRANT EXECUTE ON digits_only TO public
/
Then I tried to log in with the USERB credentials
I can
select USERA.digits_only('asd2f1') from dual;
select USERA.chrispack.digits_only('asd2f1') from dual;
this second call should not succeed!
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'CHRISPACK'
-- no rows returned
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'DIGITS_ONLY'
-- 1 row as expected
How can I see who (I suppose DBA) granted EXECUTE on my whole package?
thanks,
chris

You should check as USERB what privileges you have been granted, ie. where USERB is the grantee.

Maybe USERB has been granted "execute any procedure".

[ciapecki]

Post by Gerard H. Pille
You should check as USERB what privileges you have been granted, ie. where USERB is the grantee.
Maybe USERB has been granted "execute any procedure".

thanks for the tip.
That helped me to find out, that some generous DBA granted DBA role to
USERB.

thanks,
chris

[Gerard H. Pille]

Post by ciapecki

Post by Gerard H. Pille
You should check as USERB what privileges you have been granted, ie. where USERB is the grantee.
Maybe USERB has been granted "execute any procedure".

thanks for the tip.
That helped me to find out, that some generous DBA granted DBA role to
USERB.
thanks,
chris

Now use this knowledge wisely, Chris ;-)

And don't call that generous person a DBA, you might insult some people.