Re: use TOAD to check user privileges

From: <wblanchard_at_oshkoshcorp.com>
Date: Wed, 13 Jun 2012 10:32:18 -0500
Message-ID: <OF935025F5.72A9E849-ON86257A1C.00552B44-86257A1C.00555B75_at_oshkoshcorp.com>

Here's the script that I use to generate the user DDL. set serveroutput on;
DECLARE

    v_user          VARCHAR2(30)      :=    '';
    v_ddl           VARCHAR2(2000);
    v_status        VARCHAR2(32);

BEGIN

• Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

    select dbms_metadata.get_ddl('USER',v_user) INTO v_ddl from dual;     dbms_output.put_line(v_ddl);

• Get the user's granted quotas DECLARE v_quota VARCHAR2(2000); BEGIN select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_user) INTO v_quota from dual; dbms_output.put_line(v_quota); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('-- No quotas granted'); END;

    --Get the user's granted roles
    DECLARE
        v_role VARCHAR2(2000);
    BEGIN
        select dbms_metadata.get_granted_ddl('ROLE_GRANT',v_user) INTO v_role from dual;

        dbms_output.put_line(v_role);
    EXCEPTION

        WHEN OTHERS THEN
            dbms_output.put_line('-- No roles granted');

    END;

• Get the user's system grants DECLARE v_system VARCHAR2(2000); BEGIN select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_user) INTO v_system from dual; dbms_output.put_line(v_system); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('-- NO system grants'); END;
• Get the user's object grants DECLARE v_object VARCHAR2(2000); BEGIN select dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_user) INTO v_object from dual; dbms_output.put_line(v_object); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('-- NO object grants'); END;

    SELECT account_status INTO v_status FROM dba_users WHERE username = v_user;

    IF(v_status = 'OPEN') THEN

        dbms_output.put_line('ALTER USER '||v_user||' ACCOUNT UNLOCK');     END IF;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('-- User not found'); END;
/

Although this e-mail and any attachments are believed to be free of any virus or other defect which might affect any computer system, it is the responsibility of the recipient to check that it is virus-free and the sender accepts no responsibility or liability for any loss, injury, damage, cost or expense arising in any way from receipt or use thereof by the recipient.

The information contained in this electronic mail message is confidential information and intended only for the use of the individual or entity named above, and may be privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this transmission in error, please contact the sender immediately, delete this material from your computer and destroy all related paper media. Please note that the documents transmitted are not intended to be binding until a hard copy has been manually signed by all parties. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 13 2012 - 10:32:18 CDT