Oracle用户密码使用特殊符号,例如&(AND)、$(Dollar)、#(Pound)、*(Star)等

我们在管理ORACLE用户密码安全的时候总会用到各种由工具生成的密码带有特殊符号的问题,例如&、*、#、$等,但是在使用如上特殊密码往往会遇到各种错误,例如:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user maclean_password identified by #$%^&*!;
create user maclean_password identified by #$%^&*!
*
ERROR at line 1:
ORA-00911: invalid character

[oracle@database ~]$ oerr ora 911
00911, 00000, “invalid character”
// *Cause: identifiers may not start with any ASCII character other than
// letters and numbers. $#_ are also allowed after the first
// character. Identifiers enclosed by doublequotes may contain
// any character other than a doublequote. Alternative quotes
// (q’#…#’) cannot use spaces, tabs, or carriage returns as
// delimiters. For all other contexts, consult the SQL Language
// Reference Manual.
// *Action:

注意Oracle用户的密码必须以字母或者数字开头(letters and numbers),否则将出现ORA-00911 错误

当已经确保以字母或者数字开头的情况仍出现错误,则考虑使用双引号”将密码括起来,注意不要用中文输入法的双引号!!

SQL> create user maclean_password identified by 1#$%^&*!;
create user maclean_password identified by 1#$%^&*!
*
ERROR at line 1:
ORA-00911: invalid character

SQL> create user maclean_password identified by “1#$%^&*!”;

User created.

SQL> grant connect to maclean_password
2 ;

Grant succeeded.

SQL> conn maclean_password/”1#$%^&*!”
Connected.

还有一种可能性是在SQLPLUS或者其他IDE(例如PL/SQL DEVELOPER下)存在&(AND)符号时,客户端将&理解为变量,如:

SQL> create user maclean_password1 identified by “0000&a”;
Enter value for a:

此时只需要将define修改为非&的其他符号即可,例如:

SQL> set define +

SQL> show define
define “+” (hex 2b)

SQL> create user maclean_password1 identified by “0000&a”;

User created.

SQL> grant connect to maclean_password1;

Grant succeeded.

SQL> conn maclean_password1/”0000&a”
Connected.

Restoring a user's original password

WARNING
=======

The method described in this article is not officially supported by development,
therefore when you get errors using this procedure support cannot file a bug for it.
Please make no mistakes when setting the HEX value with the VALUES keyword as bad
values may cause internal errors. This undocumented feature exists to support
export / import and retain the original passwords.

Problem Description:
====================

How can you restore a users original password ?

Solution Description:
=====================
With the output of dba_users you can restore a user's old password by
issuing the following:

   alter user <name> identified by values '<original_encrypted_password>';

For example:
============
SQL> create user test identified by test
  2  default tablespace users temporary tablespace temp;

User created.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       CDC423BEB32B1812

SQL> alter user test identified by test1;
User altered.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       22F2E341BF4B8764

SQL> alter user test identified by values 'CDC423BEB32B1812';
User altered.

SQL> connect test/test
Connected.

Solution Explanation:
=====================
To do this you would need a spool of dba_users taken before the password was
changed.

Script:收集数据库安全风险评估信息

以下脚本可以用于收集数据库安全风险评估信息:

REM list database vulnerability assessment info

set escape on;
set linesize 140 ;
spool db_vulnerability_assessment.log

Select role
  from dba_roles r
 where role not in ('CONNECT',
                    'RESOURCE',
                    'DBA',
                    'SELECT_CATALOG_ROLE',
                    'EXECUTE_CATALOG_ROLE',
                    'DELETE_CATALOG_ROLE',
                    'EXP_FULL_DATABASE',
                    'WM_ADMIN_ROLE',
                    'IMP_FULL_DATABASE',
                    'RECOVERY_CATALOG_OWNER',
                    'AQ_ADMINISTRATOR_ROLE',
                    'AQ_USER_ROLE',
                    'GLOBAL_AQ_USER_ROLE',
                    'OEM_MONITOR',
                    'HS_ADMIN_ROLE')
   and not exists
 (Select 1 from dba_role_privs p where p.granted_role = r.role)
/

select tp.grantee, tp.table_name
  from dba_tab_privs tp, dba_users u
 where tp.owner = 'SYS'
   and (tp.table_name like 'V_$%' or tp.table_name like 'G_V$')
   and tp.grantee = u.username
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'DMSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select *
  from (select 'Hidden User in DBA_USERS' ddview, name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in DBA_USERS', username from SYS.dba_users) q1
union all
select *
  from (select 'Hidden User in ALL_USERS', name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in ALL_USERS', username from SYS.all_users) q2
/



select grantee, granted_role
  from dba_role_privs
 where grantee in (select role from dba_roles)
 order by grantee, granted_role
/

select grantee, privilege, admin_option
  from dba_sys_privs sp, dba_users u
 where sp.GRANTEE = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and privilege in (select name
                       from sys.system_privilege_map
                      where 0 = 1
                         or name like '%ANY%'
                         or name like '%DATABASE%'
                         or name like '%DIRECTORY%'
                         or name like '%LIBRARY%'
                         or name like '%LINK%'
                         or name like '%PROFILE%'
                         or name like '%RESTRICTED%'
                         or name like 'SYS%'
                         or name like '%SYSTEM%'
                         or name like '%TABLESPACE%'
                         or name like '%USER%')
 order by 1
/

select role,
       (select count(*)
          from dba_role_privs rp
         where rp.granted_role = r.role) GRANT_COUNT
  from dba_roles r
 where r.role in ('DBA', 'CONNECT', 'RESOURCE')
 order by 1
/

select grantee, granted_role, admin_option
  from dba_role_privs rp, dba_users u
 where rp.grantee = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and granted_role in (select role
                          from dba_roles
                         where 0 = 1
                            or role like '%CATALOG%'
                            or role like '%DATABASE%'
                            or role like '%DBA%')
 order by 1
/

select distinct profile, resource_name, actual_limit 
from (select P.Profile, p.resource_Name,
             decode(p.limit, 'UNLIMITED', '9999999999999999999', 
                   'NULL', null, to_number(p.limit)) limit,
             limit actual_limit
      from ( select profile, resource_name, 
                    decode(resource_name,  'IDLE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'FAILED_LOGIN_ATTEMPTS', decode(limit, 'DEFAULT', '10', limit),
                                           'PASSWORD_LIFE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_MAX', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_GRACE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                'PASSWORD_VERIFY_FUNCTION', decode(limit, 'NULL', '0', null, 0, 'DEFAULT', 0, 1), limit) limit
              from   dba_profiles
              where resource_name in ('IDLE_TIME', 'FAILED_LOGIN_ATTEMPTS',
                                      'PASSWORD_LIFE_TIME', 'PASSWORD_REUSE_MAX',
                                      'PASSWORD_REUSE_TIME','PASSWORD_GRACE_TIME',
                                      'PASSWORD_VERIFY_FUNCTION')) p ) 
where 1=0 
or    (RESOURCE_NAME = 'IDLE_TIME' AND LIMIT > 60)
or    (RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND LIMIT > 90)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_MAX' AND LIMIT > 20)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_TIME' AND LIMIT > 180)
or    (RESOURCE_NAME = 'PASSWORD_GRACE_TIME' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION' AND LIMIT = 0)
order by 1,2
/

Select s.owner, s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner <> 'PUBLIC'
 order by 1
/

Select distinct profile
  from dba_profiles
minus
Select distinct profile from dba_users
/

select table_name
  from dba_tab_privs
 where owner = 'SYS'
   and grantee = 'PUBLIC'
   and table_name in ('UTL_SMTP',
                      'UTL_TCP',
                      'UTL_HTTP',
                      'UTL_FILE',
                      'DBMS_RANDOM',
                      'DBMS_LOB',
                      'DBMS_SYS_SQL',
                      'DBMS_BACKUP_RESTORE',
                      'EMD_SYSTEM',
                      'DBMS_NAMESPACE',
                      'DBMS_SCHEDULER')
 order by 1
/ 
 

select username, password from dba_users order by 1
/


select tp.grantee, tp.table_name, tp.privilege
  from dba_tab_privs tp, dba_users u, dba_tables t
 where tp.owner = 'SYS'
   and tp.grantee = u.username
   and tp.owner = t.owner
   and tp.table_name = t.table_name
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2, 3
/
 
 select sp.grantee, sp.privilege
   from dba_sys_privs sp, dba_users u
  where sp.admin_option = 'YES'
    and u.username = sp.grantee
    and u.username not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES')
  order by 1, 2
/
  
  select p.grantee, p.owner, p.table_name, p.privilege
    from dba_tab_privs p, dba_users u
   where p.grantable = 'YES'
     and u.USERNAME = p.grantee
     and u.username not in ('SYS',
                            'SYSTEM',
                            'SYSMAN',
                            'EXFSYS',
                            'WMSYS',
                            'OLAPSYS',
                            'OUTLN',
                            'DBSNMP',
                            'ORDSYS',
                            'ORDPLUGINS',
                            'MDSYS',
                            'CTXSYS',
                            'AURORA$ORB$UNAUTHENTICATED',
                            'XDB',
                            'FLOWS_030000',
                            'FLOWS_FILES')
   order by 1, 2, 3, 4
/

select username
  from dba_users
 where account_status!='EXPIRED \& LOCKED'
 order by 1
/

Select s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner = 'PUBLIC'
 order by 1
/

select r.grantee, r.granted_role
  from dba_role_privs r, dba_users u
 where r.admin_option = 'YES'
   and u.username = r.grantee
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select username
  from dba_users
 where password = 'EXTERNAL'
 order by username
/

沪ICP备14014813号

沪公网安备 31010802001379号