ORACLE 对象管理-用户

1.创建用户

CREATE USER "pin62" IDENTIFIED BY pin62
      DEFAULT TABLESPACE "PIN00"
      TEMPORARY TABLESPACE "TEMP"

准确的创建用户的方法

CREATE USER pin62 IDENTIFIED BY pin62
      DEFAULT TABLESPACE "PIN00"
      TEMPORARY TABLESPACE "TEMP"
SQL>    CREATE USER pin62 IDENTIFIED BY pin62  2        DEFAULT TABLESPACE "PIN00"  3        TEMPORARY TABLESPACE "TEMP";
User created.
SQL> grant connect,resource to pin62;
Grant succeeded.

2.关于Oracle的用户名和密码的大小写

1：Oracle的用户名，默认创建到中会自动全部转换为大写保存

2：Oracle的账号密码不区分大小写

 

以下是验证过程：

1：验证密码不区分大小。无论密码是否加双引号

修改用户密码，不加引号

SQL> alter user harvey identified by fih123456;

User altered.

SQL> disconn
Disconnected from Oracle Database Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

以原密码连接
SQL> conn harvey/fih123456;
Connected.
SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

 

以大写密码连接
SQL> conn harvey/FIH123456;
Connected.
再次修改密码，使用双引号

SQL> alter user harvey identified by "fih123456";

User altered.

SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn harvey/FIH123456;
Connected.
自此发现也是成功的，所以Oracle的账号密码不区分大小写。

 

2:验证用户名的大小写

SQL> create user testa identified by abc123 default tablespace testa temporary tablespace temp;

User created.

SQL> grant connect,resource to testa;

Grant succeeded.

 

SQL> create user "testb" identified by abc123 default tablespace testb temporary tablespace temp;

User created.

SQL> grant connect,resource to testb;
grant connect,resource to testb
                          *
ERROR at line 1:
ORA-01917: user or role 'TESTB' does not exist

SQL> grant connect,resource to "testb";

Grant succeeded.

 

SQL> select username from dba_users
  2  where account_status='OPEN';

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
HARVEY
TESTA
testb

8 rows selected.

 

SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn testa/abc123
Connected.
SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn testb/abc123
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn "testb"/abc123;
Connected.
至此，推测oracle默认把用户名转成大写存入数据库中，双引号原样保留。

3.删除用户

SQL> select username from dba_users where account_status='OPEN';

USERNAME
------------------------------

SYSTEM
SYS
SYSMAN
MGMT_VIEW
pin62
OUTLN

6 rows selected.

SQL> drop user "pin62" cascade;

User dropped.
SQL> select username from dba_users;
USERNAME------------------------------OUTLNMGMT_VIEWSYSSYSTEMSYSMANPIN62DBSNMPWMSYSTSMSYSDIPORACLE_OCM