oracle 最近对象,ORACLE 对象管理-用户

1.创建用户

CREATE USER "pin62" IDENTIFIED BY pin62

DEFAULT TABLESPACE "PIN00"

TEMPORARY TABLESPACE "TEMP"

SQL> grant connect,resource to pin62;

grant connect,resource to pin62

*

ERROR at line 1:

ORA-01917: user or role 'PIN62' does not existSQL> select USERNAME||','||USER_ID||','||ACCOUNT_STATUS from dba_users;

OUTLN,11,OPEN

MGMT_VIEW,30,OPEN

SYS,0,OPEN

SYSTEM,5,OPEN

SYSMAN,28,OPEN

pin62,31,OPEN

DBSNMP,24,EXPIRED & LOCKED

WMSYS,26,EXPIRED & LOCKED

TSMSYS,21,EXPIRED & LOCKED

DIP,19,EXPIRED & LOCKED

ORACLE_OCM,25,EXPIRED & LOCKED

SQL> grant connect,resource to "pin62";

Grant succeeded.SQL> conn pin62/pin62

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn "pin62"/pin62

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL>

准确的创建用户的方法

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

------------------------------

OUTLN

MGMT_VIEW

SYS

SYSTEM

SYSMAN

PIN62

DBSNMP

WMSYS

TSMSYS

DIP

ORACLE_OCMSQL> conn pin62/pin62

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.