| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Discovered way to grant/revoke any right from any user!
A copy of this was sent to "Viewer" <bmotzer_at_spacestar.com>
(if that email address didn't require changing)
On Thu, 15 Apr 1999 23:11:53 -0500, you wrote:
>No this is not some kind of crazy email. I've been an Oracle dba for five
>years now and it always bothered me
>that Oracle did not provide DBA's with a method of revoking/granting
>privileges to users or between users
>without loggin in as them and issuing the grant/revoke.
>
>It bothered me enough to find a way to do it and I want to share it with the
>rest
>of the Oracle dba's who may be having this problem. At a very high level
>what I do is use a
>c program to modify the contents of an export file replacing create table
>statements
>and create index statements with a sql statement like "REVOKE SELECT ON
>TABLE1
>from USERA".
>
[snip]
the import trick works because import silently 'becomes' the other user (in effect, it is logging in as that other user for you).
there is an easier way to do this that works with 7.0 and up on all platforms in sqlplus. If a user has "CREATE ANY PROCEDURE" and "EXECUTE ANY PROCEDURE" (eg: a DBA), the following script works well:
rem 1 = OWNER OF OBJECT rem 2 = OBJECT NAME rem 3 = PRIV to grant rem 4 = WHO to grant to
show user
create procedure &1..dba_parse_$$( p_cur in number, p_str in varchar2 )
as
begin
dbms_sql.parse( p_cur, p_str, dbms_sql.native );
end;
/
column grantee format a8
column grantor format a8
select grantee, grantor, privilege from dba_tab_privs where owner = upper('&1') and table_name = upper('&2') /
declare
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
sql_stmt varchar2(255);
begin
sql_stmt := 'grant &3 on &1..&2 to &4';
&1..dba_parse_$$( exec_cursor, sql_stmt );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
end;
/
select grantee, grantor, privilege from dba_tab_privs where owner = upper('&1') and table_name = upper('&2') /
drop procedure &1..dba_parse_$$;
You would run it as such:
SQL> @dogrant scott emp all public
user is "TKYTE"
old 1: create procedure &1..dba_parse_$$( p_cur in number, p_str in varchar2 )
new 1: create procedure scott.dba_parse_$$( p_cur in number, p_str in varchar2
)
Procedure created.
old 2: where owner = upper('&1') and table_name = upper('&2') new 2: where owner = upper('scott') and table_name = upper('emp')
no rows selected
old 6: sql_stmt := 'grant &3 on &1..&2 to &4'; new 6: sql_stmt := 'grant all on scott.emp to public'; old 7: &1..dba_parse_$$( exec_cursor, sql_stmt ); new 7: scott.dba_parse_$$( exec_cursor, sql_stmt );
PL/SQL procedure successfully completed.
old 2: where owner = upper('&1') and table_name = upper('&2') new 2: where owner = upper('scott') and table_name = upper('emp')
GRANTEE GRANTOR PRIVILEGE
-------- -------- ----------------------------------------PUBLIC SCOTT ALTER
old 1: drop procedure &1..dba_parse_$$ new 1: drop procedure scott.dba_parse_$$
Procedure dropped.
SQL>
that shows that it worked (the grants were in fact given) without logging in as the other user.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |