oracle身份验证

最新推荐文章于 2024-11-03 03:45:00 发布

lemontree1123

最新推荐文章于 2024-11-03 03:45:00 发布

阅读量721

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle dictionary null authentication table sql

本文链接：https://blog.youkuaiyun.com/lemontree1123/article/details/7795109

Oracle 专栏收录该内容

64 篇文章

订阅专栏

在10G R2中dba_users的字段如下：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

SQL> desc dba_users;

Name Null? Type

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

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

SQL> desc v$pwfile_users;

Name Null? Type

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

USERNAME VARCHAR2(30)

SYSDBA VARCHAR2(5)

SYSOPER VARCHAR2(5)

11g R2（11.2.0.3）中dba_users的字段如下：

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> desc dba_users;

Name Null? Type

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

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

PASSWORD_VERSIONS VARCHAR2(8)

EDITIONS_ENABLED VARCHAR2(1)

AUTHENTICATION_TYPE VARCHAR2(8)

AUTHENTICATION_TYPE分为三种

EXTERNAL - CREATE USER user1 IDENTIFIED EXTERNALLY;
GLOBAL - CREATE USER user2 IDENTIFIED GLOBALLY;
PASSWORD - CREATE USER user3 IDENTIFIED BY user3;

SQL> desc v$pwfile_users;

Name Null? Type

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

USERNAME VARCHAR2(30)

SYSDBA VARCHAR2(5)

SYSOPER VARCHAR2(5)

SYSASM VARCHAR2(5)

11g多了一个sysasm

V$PWFILE_USERS 列出password file中记录的，被赋予sysdba/sysoper/SYSASM权限的用户

dba_ ts_quotas /user_ts_quotas 列出了用户的表空间限额的信息

看maclean的PPT上说：从9i开始，’ANY’权限无法访问SYS用户对象

默认O7_DICTIONARY_ACCESSIBILITY=false ，设置为TRUE可以让’ANY’权限访问SYS对象

否则普通用户必须显示地拥有SYS对象的权限。

下面做个小实验，验证一下

SQL> show parameter acc

NAME TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

control_management_pack_access string DIAGNOSTIC+TUNING

ldap_directory_access string NONE

SQL> show user;

USER is "SYS"

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> select count(*) from sys.obj$;

COUNT(*)

----------

75210

SQL> show user;

USER is "SYS"

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

select count(*) from sys.obj$

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba

Connected.

SQL> show user;

USER is "SYS"

SQL> grant select any table to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

select count(*) from sys.obj$

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

COUNT(*)

----------

75210

SQL> conn / as sysdba

Connected.

SQL> revoke dba from test;

Revoke succeeded.

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

select count(*) from sys.obj$

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba

Connected.

SQL> select * from dba_sys_privs where grantee='TEST';

GRANTEE PRIVILEGE ADM

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

TEST SELECT ANY TABLE NO

SQL> show parameter acc

NAME TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

control_management_pack_access string DIAGNOSTIC+TUNING

ldap_directory_access string NONE

SQL> show user;

USER is "SYS"

SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> select name,issys_modifiable from v$system_parameter where name='O7_DICTIONARY_ACCESSIBILITY';

NAME ISSYS_MOD

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

O7_DICTIONARY_ACCESSIBILITY FALSE

-----查询此参数是否可动态修改，可知此参数不能动态修改，需要重启数据库才能生效

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

select count(*) from sys.obj$

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 490737024 bytes

Database Buffers 339738624 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY boolean TRUE

SQL> conn test/test

Connected.

SQL> select count(*) from sys.obj$;

COUNT(*)

----------

75210