Oracle选择性系统授权
Oracle选择性系统授权
Selective System Grants
问题:
我只想授予XX用户alter system set user_dump_dest 权限!
I want to give users the ability to execute "alter system set user_dump_dest" only. I want to give them the ability to turn on extended tracing but write out the trace files to a different directory, such as /tmp, rather than to the default directory. Is there a way to do this easily?
Tom回答:
存储过程是绝佳方案!
Stored procedures are great for this!
存储过程,默认以定义者权限运行。你需要做的就是:
A stored procedure, by default, runs with the base privileges of the definer of the routine. As a user with the ability to use ALTER SYSTEM , all you need to do is
create or replace procedure set_udump (p_udump in varchar2) as begin if ( p_udump NOT LIKE '%=%' ) then execute immediate 'alter system set user_dump_dest = '''||p_udump||''' scope=memory'; else raise_application_error(-20000,'Sorry, but for safety reasons this procedure does not allow "=" in the parameter value'); end if; end; /
防止SQL注入攻击!
Note: Revised content—to prevent SQL injection—for this procedure submitted by Roy Jorgensen.
The owner of the procedure needs to have ALTER SYSTEM granted directly, not via a role. Seeasktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html for details on that nuance. So we run
SQL> grant alter system to a; Grant succeeded. SQL> grant execute on set_udump to scott; Grant succeeded. Now, connected as SCOTT , run SQL> exec set_udump( '/tmp' ); PL/SQL procedure successfully completed.
这种途径提供优秀的安全机制。
This approach works for any privilege you want to grant selectively like this. Stored procedures—with their ability to run as definer—provide an excellent security mechanism!