以用户身份连接时，在Oracle数据库中收集dba_users信息 [英] gather dba_users information in Oracle database when connected as a user

问题描述

我是Oracle数据库的新手.我看到如果我以sys用户身份连接，我可以做到

I am new to Oracle database. I see that if I connect as sys user, I can do

select * from dba_users;

但是，一旦我完成了conn nonsys@dbid，我就不能再这样做了.我会说一个错误

But once I have done conn nonsys@dbid, I can no longer do that; I will get an error saying

ORA-00942: table or view does not exist

select * from sys.dba_users;也不起作用.

您能解释一下为什么会这样吗，以及以非系统用户身份连接后如何执行select * from dba_users;吗?

Could you please explain why this is and how I can do select * from dba_users; after connected as a non-sys user?

此外，disconnect使我完全断开连接；是否只能退出"当前用户并返回sqlplus中的sys?如果是，该怎么做?

Also, disconnect makes me totally disconnected; is it possible to only "exit" current user and back to sys in sqlplus and if so, how to do that?

推荐答案

Oracle中的DBA_ *视图包含有关数据库中所有对象的信息，而与所有权无关.默认情况下，只有管理帐户有权访问这些视图.出于安全原因这样做.为了使普通"用户能够访问这些视图，必须直接基于每个视图或通过诸如SELECT ANY TABLE(不推荐)之类的系统特权全局授予它们访问权限.最好授予对用户真正需要的实际DBA_视图的访问权限.通常，ALL_视图将为典型用户提供他们所需的所有信息.

The DBA_* views in Oracle contain information about ALL objects in the database regardless of ownership. Only administrative accounts have access to these views by default. This is done for security reasons. In order to have a "normal" user gain access to these views, they must be granted access to them, either directly on a per-view basis, or globally through such system privileges as SELECT ANY TABLE (not recommended). Better to grant access to the actual DBA_ view the user really needs. Generally, the ALL_ views will give a typical user all the information they require.

要退出当前用户会话并以其他用户身份连接，请使用CONNECT命令:

To leave the current user session and connect as another user, use the CONNECT command:

CONNECT sys/pw as sysdba

对象的所有者可以通过

The owner of an object can grant access to any of their objects to another user or role via the GRANT command:

GRANT SELECT ON dba_users TO nonsys;

以用户SYS的身份执行，这将向用户nonsys授予对dba_users视图的选择访问权限.

Performed as the user SYS, this would grant select access to the dba_users view to the user nonsys.

执行授予后，用户nonsys将能够通过SELECT语句从该视图中进行选择:

Once the grant is performed, the user nonsys will be able to select from this view via the SELECT statement:

SELECT * FROM dba_users;

这篇关于以用户身份连接时，在Oracle数据库中收集dba_users信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！