Category Archives: Security

Generate user DDL with dbms_metadata.get_ddl user

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

For Oracle 18c / 12c / 11g / 10g:

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

More information for the package dbms_metadata, function get_ddl user in the official Oracle 18c/12c documentation :
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_METADATA.html

For Oracle <10 (runs well too with 10g, 11g, 12.2 and 18c):

clear screen

accept uname prompt 'Display the DDL for this specific user: '
accept outfile prompt  ' Output filename : '

col username noprint
col lne newline

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool &&outfile..gen

prompt  -- generate user ddl
SELECT username, 'CREATE USER '||username||' '||
       DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
              'IDENTIFIED BY VALUES '''||password||''' ') lne,
       'DEFAULT TABLESPACE '||default_tablespace lne,
       'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
  FROM DBA_USERS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

SELECT username, 'ALTER USER '||username||' QUOTA '||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
       ||' ON '||tablespace_name||';' lne
  FROM DBA_TS_QUOTAS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

col grantee noprint

select grantee, granted_role granted_priv,
       'GRANT '||granted_role||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_role_privs
 where grantee like upper('%&&uname%')
         UNION
select grantee, privilege granted_priv,
       'GRANT '||privilege||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_sys_privs
 where grantee like upper('%&&uname%')
 order by 1, 2;

spool off

Another use of this procedure is to copy a user account from one Oracle instance to another. With the same password, grants and roles without using the expdp/impdp tools.

 

Another method to retreive the Data Description Language for an Oracle user with all roles and Privileges:

With datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile:
http://www.oracle-scripts.net/standard-datapump-use/

 

Author: Vincent Fenoll
Compatibility: Oracle 18c, 12c, 11g

PasswordListener

-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/09/01
-- # Name: PasswordListener
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # This script protect the listener LISTENER_1  
-- # with a the password xyz
-- ################################################


$>lsnrctl
  set current_listener LISTENER_1
  set save_config_on_stop on
  change_password
  Old password:  
  New password:  xyz
  Reenter new password: xyz
  exit

$>lsnrctl
  set current_listener LISTENER_1
  set password 
  xyz
  stop
  start
  exit

# If you use Grid (or database) Control:
#

Trace session logon

-- ############################################################
-- # Creator: Nicolas Chabault
-- # Created: 2011/06/14
-- # Name: Trace session logon
-- ############################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ############################################################
-- #
-- # Trace session and store logon data in the table dba_audit_trail 
-- # to analyze security or account locking.
-- #
-- ############################################################

Trace session to prevent account lock with the following query:

   - Active the parameter audit_trail = db 
                               SQL> alter system  set audit_trail=DB scope=spfile + startup force);
   - Execute : 
                               SQL> audit create session whenever not successful;
   - Execute : 
                               SQL select os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') 
                               from dba_audit_trail where action_name='LOGON';

-- If you have the response : "no row selected", there's not login authentification problems
-- if you have a login failed you'll have the following lines :              
                administrator
                SYS
                PTS/2
                07-14-2011 16:00:00


-- To remove the audit (You can also put the parameter "audit_trail = none" for every audits)
                               SQL> NOaudit create session whenever not successful;

List_user_role_privs

–- ################################################
–- # Creator: Oracle Corp.
–- # Created: 2005/01/01
–- #
–- ################################################
–- #
–- # Compatible: 8i 9i 10g 11g
–- #
–- ################################################
–- #
-- # Lists the privileges and roles, regardless of 
-- # how the roles/privileges were granted;
-- # either directly or indirectly.
–- #
–- ################################################

SET ECHO off
REM  Name :LIST_USER_ROLE_PRIVS.sql
REM  ------------------------------
REM  Should be run as SYS
REM  ------------------------------
prompt Creating the procedure LIST_USER_ROLE_PRIVS
CREATE OR REPLACE PROCEDURE LIST_USER_ROLE_PRIVS(UNAME VARCHAR2)
IS
U1 VARCHAR2(100):=UNAME;

CURSOR one IS SELECT u1.name USERNAME ,U2.NAME ROLENAME ,SUBSTR(SPM.NAME,1,27) PRIVILEGE
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2,SYS.USER$ U1, SYS.USER$ U2,SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE# (+)
AND SA2.PRIVILEGE# = SPM.PRIVILEGE (+) AND
(U1.NAME IN
  (SELECT GRANTEE FROM DBA_ROLE_PRIVS connect by prior
	  granted_role=GRANTEE start with GRANTEE IN
          (SELECT NAME FROM USER$ WHERE user# in 
                  (select privilege# from sysauth$ t1, user$ t2
                   where t1.grantee#=t2.user# and t2.name=U1)
	  )
   UNION
   SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS connect by prior
	  granted_role=GRANTEE start with GRANTEE IN 
          (SELECT NAME FROM USER$ WHERE user# in 
                  (select privilege# from sysauth$ t1, user$ t2
	           where t1.grantee#=t2.user# and t2.name=U1)
	  )
   )
 OR U1.NAME=U1
 )
ORDER BY U2.USER#, U2.NAME ;

CURSOR two IS select u.name username,spm.name privilege
  from user$ u, sysauth$ s, system_privilege_map spm
  where u.user#=s.grantee# and s.privilege#=spm.privilege
  AND   U.NAME=U1
  ORDER BY 1,2;

BEGIN
 dbms_output.put_line(rpad('USERNAME',30,' ')||rPAD('ROLENAME',21,' ')||rPAD('PRIVILEGE',21,' '));
 DBMS_OUTPUT.PUT_LINE('----------------------------- -------------------- --------------------');
 for y in one loop	
   dbms_output.put_line(rPAD(y.USERNAME,30,' ')||rPAD(y.ROLENAME,21,' ')||rPAD(y.PRIVILEGE,21,' '));
 end loop;
 for x in two loop
   dbms_output.put_line(rPAD(x.username,51,' ')||rPAD(x.privilege,21,' '));
 end loop;

END;
/

set verify off


prompt Enter user to probe (uname)
set serveroutput on size 1000000
begin
 SYS.LIST_USER_ROLE_PRIVS('&UNAME');
end;
/

undef username
set verify on                 

drop PROCEDURE LIST_USER_ROLE_PRIVS;
-- end script LIST_USER_ROLE_PRIVS.sql