VMCD.ORG

Focus on database mail:ylouis83#gmail.com

Scripts: check user all granted privileges

Posted by admin on June 14th, 2012

this scripts is used to check user all privileges

set serveroutput on;
DECLARE
   v_user          VARCHAR2(30)      := 'XXX';
   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(30000);
   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;
/

eg:

SQL> set serveroutput on;
SQL> DECLARE
   v_user          VARCHAR2(30)      := 'LIU';
   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;
/

   CREATE USER "LIU" IDENTIFIED BY VALUES
'S:416DF25746736369B087A8388B7178AF33DA895C7F7DC8D997455C1BF0E9;9E1A31EC43A0799C
'
      DEFAULT TABLESPACE "UDATA"
      TEMPORARY TABLESPACE "TEMP";
-- No quotas granted

   GRANT "DBA" TO "LIU";

  GRANT UNLIMITED TABLESPACE TO "LIU";

  GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "LIU";
  GRANT DELETE ON
"TEST2"."COUPON" TO "LIU";
  GRANT SELECT ON "TEST2"."COUPON" TO "LIU";
  GRANT
SELECT ON "SYS"."T" TO "LIU";
ALTER USER LIU ACCOUNT UNLOCK

PL/SQL procedure successfully completed.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>