Home » RDBMS Server » Security » Trouble with 'grant' procedure
Trouble with 'grant' procedure [message #11509] Tue, 30 March 2004 09:48 Go to next message
Shane
Messages: 27
Registered: December 1999
Junior Member
I'm trying to give one user select permissions to another user's tables. It compiles fine, it executes fine (no errors) but it does not actually grant anything.

I tried to use this script:
http://www.quest-pipelines.com/pipelines/plsql/archives/grants.sql

but I couldn't get it to work either. Oracle said I must define dba_users, dba_objects, etc. I'm running 9.2.0.1 on Windows, using TOAD 7.6.

Any help is appreciated.

Thanks,
Shane

CREATE OR REPLACE PROCEDURE grant_read_permissions( owner_user IN VARCHAR2, read_user IN VARCHAR2)

IS
CURSOR crsr_Tables IS
SELECT
a_tb.TABLE_NAME
FROM
all_tables a_tb
WHERE
a_tb.OWNER = owner_user;

BEGIN
FOR rec_Table IN crsr_Tables
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || owner_user || '.' || rec_Table.TABLE_NAME || ' TO ' || read_user;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No DATA!!!');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM(SQLCODE));

END grant_read_permissions;
/
Re: Trouble with 'grant' procedure [message #11510 is a reply to message #11509] Tue, 30 March 2004 10:12 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Shane, under which user are you creating this procedure? SYSTEM?

It sounds like you need this proc to be defined with invoker's rights (instead of the default definer's rights):

CREATE OR REPLACE PROCEDURE grant_read_permissions( owner_user IN VARCHAR2, read_user IN VARCHAR2)
  authid current_user
IS
Re: Trouble with 'grant' procedure [message #11511 is a reply to message #11510] Tue, 30 March 2004 10:35 Go to previous messageGo to next message
Shane
Messages: 27
Registered: December 1999
Junior Member
Todd,
Thanks!! I was running the script under my own user, which has DBA privileges (scary huh? :)). I'll lookup this 'authid'. Any further explanation would be helpful. I see that it works, but I would like to understand why.

Thanks again,
Shane
Re: Trouble with 'grant' procedure [message #11513 is a reply to message #11511] Tue, 30 March 2004 11:15 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
With the default of definer's rights, the proc will only be able to see tables in the all_objects view that have been explicitly granted ('grant select' as opposed to through a role) to you. With invoker's rights, the proc will be able to see those tables plus the tables visible through roles.
Re: Trouble with 'grant' procedure [message #11514 is a reply to message #11511] Tue, 30 March 2004 11:35 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
There are no roles in stored PL/SQL. "DBA" is a role that includes various system privileges, but within stored PL/SQL, it doesn't exist.
Re: Trouble with 'grant' procedure [message #11515 is a reply to message #11514] Tue, 30 March 2004 11:40 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
There are no roles in stored definer-rights PL/SQL. Invoker rights procedures take their chances at runtime.
Previous Topic: Create role
Next Topic: Why cant audit?
Goto Forum:
  


Current Time: Sat Apr 10 13:27:15 CDT 2021