| Trouble with 'grant' procedure [message #11509] |
Tue, 30 March 2004 09:48  |
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  |
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 |
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
