Re: use TOAD to check user privileges
- From: wblanchard@xxxxxxxxxxxxxxx
- To: mccdba1@xxxxxxxxx
- Date: Wed, 13 Jun 2012 10:32:18 -0500
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
