ORACLE--常用的数据字典和用户管理

柔情只为你懂 2021-12-05 18:41 359阅读 0赞

参考网址:[http://blog.csdn.net/kdnuggets/article/details/2076996][http_blog.csdn.net_kdnuggets_article_details_2076996]

USER\_XXXX \-> Objects owned by the user.(属于当前用户的对象)  
ALL\_XXXX \-> List of objects that user has access TO(当前用户可以访问到的对象)  
DBA\_XXXX \-> all tables in the database,List of all objects available in database.(数据库中的所有对象，需要DBA的角色的用户查看)

\---------------------------表信息--------------------------------  
ALL\_TABLES:Description of relational tables accessible to the user  
USER\_TABLES:Description of the user''s own relational tables  
DBA\_TABLES:Description of all relational tables in the database

SELECT \* FROM User\_Tables--当前用户的表

![2011122513255651.jpg][]

SELECT \* FROM ALL\_TABLES --当前用户可以访问的所以的表

(在DYJ下面做实验：  
\--建表  
CREATE TABLE A(ID VARCHAR2(32));  
\--授权访问  
GRANT ALL ON ATO SCOTT WITH GRANT OPTION  
\--查询SELECT \* FROM all\_Tables，结果多了一张表，该表的名字为A  
)  
SELECT \* FROM ALL\_TABLES WHERE OWNER = 'DYJ'--当前用户可以访问的所以的表

![2011122513272854.jpg][]

\--查询系统中所有的表(需要在具有DBA角色的用户下)

![2011122513301187.jpg][]

\--查询该用户下所以的表  
SELECT \* FROM DBA\_TABLES D WHERE D.owner = 'SCOTT'

![2011122513310566.jpg][]

\---------------------------用户信息--------------------------------  
USER\_USERS:Information about the current user  
ALL\_USERS:Information about all users of the database  
DBA\_USERS:Information about all users of the DATABASE  
SELECT \* FROM USER\_USERS;--当前用户  
SELECT \* FROM ALL\_USERS;--所有的用户  
SELECT \* FROM DBA\_USERS;--所以的用户(DBA角色的用户查询)

\---------------------------用户或角色系统权限----------------------  
DBA\_SYS\_PRIVS,USER\_SYS\_PRIVS  
DBA\_SYS\_PRIVS:System privileges granted to users and roles(系统权限授给的用户或者角色)

1 SELECT P.GRANTEE      授权的用户或者角色,
    2        P.PRIVILEGE    系统权限,
    3        P.ADMIN_OPTION 是否授予ADMIN的权限
    4   FROM DBA_SYS_PRIVS P

![2011122513340774.jpg][]

USER\_SYS\_PRIVS:System privileges granted to current user(授给当前用户的系统权限)

1 SELECT P.GRANTEE      授权的用户或者角色,
    2        P.PRIVILEGE    系统权限,
    3        P.ADMIN_OPTION 是否授予ADMIN的权限
    4   FROM DBA_SYS_PRIVS P

![2011122513353669.jpg][]

查询SCOTT用户所具有的系统权限

1 SELECT * FROM DBA_SYS_PRIVS E WHERE E.GRANTEE = 'SCOTT'

![2011122513371719.jpg][]

查询RESOURCE角色所具有的系统权限

1 SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE = 'RESOURCE'

![2011122513372961.jpg][]

\---------------------------分配权限----------------------------------  
单个权限:

1 grant system_privilege to username;

多个权限:

1 grant system_privilege_1, system_privileges_2, ..,system_privileges_n to username;

\---------------------------ADMIN/GRANT OPTION------------------------

with admin option  
授权者授予某个用户“系统权限”，同时也允许被授权者将这个权限授予其他用户  
可以为角色赋予“系统权限”  
with grant option  
授权者授予某个用户“对象权限”，同时也允许被授权者将这个权限授予其他用户  
不可以为角色赋予“对象权限”  
用with admin option，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，  
这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限  
create session with admin option,然后A又把create session权限授予B,  
但管理员收回A的create session权限时，B依然拥有create session的权限，  
但管理员可以显式收回B create session的权限，即直接revoke create session from B.  
而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，  
不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，  
如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，  
B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。  
系统权限:需要使用with admin option  
1、GRANT \*\*\*（CREATE VIEW） TO xxx 需要使用with admin option(可以缺省)，不能使用with grant OPTION  
对象权限:需要使用with grant option  
2、GRANT ALL ON 对象名字 TO xxx WITH GRANT OPTION;，不能使用with admin option

\---------------------------------用户具有的对象的权限表----------------

user\_tab\_privs:Grants on objects for which the user is the owner, grantor or grantee  
all\_tab\_privs:Grants on objects for which the user is the grantor, grantee, owner  
dba\_tab\_privs:All grants on objects in the database  
\--当前用户所具有的对象权限  
向SCOTT用户赋予表A的所有的权限  
GRANT ALL ON a TO scott WITH GRANT OPTION  
收回赋予SCOTT的关于A表的所有权限  
REVOKE ALL ON a FROM scott  
SELECT \* FROM user\_tab\_privs--当前用户所具有的自己或者其他的用户的对象(包含表)的权限  
SELECT \* FROM all\_tab\_privs  
SELECT \* FROM dba\_tab\_privs--数据库中所有的用户具有对象(包含表)的权限

\-------------------------------系统中的角色表-------------------------  
DBA\_ROLES:All Roles which exist in the DATABASE(所有角色)  
USER\_ROLE\_PRIVS:Roles granted to current USER(所有分配给当前用户的角色)  
DBA\_ROLE\_PRIVS:Roles granted to users and ROLES(所有分配给用户或角色的角色)(备注:角色可以分配给角色)

\-------------------------------ALL\_表名字----------------------------  
user\_views--Description of the user''s own views  
user\_varrays--Description of varrays contained in the user''s own tables  
All\_Views--Description of views accessible to the user  
all\_varrays--Description of varrays in tables accessible to the user  
ALL\_PROCEDURES--Description of all procedures available to the user  
USER\_PROCEDURES--Description of the users own procedures  
user\_objects--Objects owned by the user  
USER\_SOURCE--Source of stored objects accessible to the user  
all\_objects--Objects accessible to the user

查看函数名字TEST\_001属于哪个用户

1 SELECT *　FROM ALL_OBJECTS A WHERE A.OBJECT_NAME = 'TEST_001'

\----------------------------常用的数据字典的使用-----------------------------  
1、查询用户的表名和状态

1 SELECT U.TABLE_NAME 表名,U.STATUS 状态 FROM USER_TABLES U

![2011122513503915.jpg][]

2、查询所有对象(OBJECT\_NAME-包含过程，函数和包.PROCEDURE\_NAME-过程名字)

1 SELECT t.OBJECT_NAME,t.PROCEDURE_NAME FROM user_procedures t

![2011122513505128.jpg][]

3、查询所有的对象

1 SELECT u.OBJECT_NAME,u.OBJECT_TYPE,u.status FROM user_objects u

![2011122513510547.jpg][]

4、查看过程或者函数定义过程中的错误信息

1 SELECT * FROM USER_ERRORS

![2011122513524569.jpg][]

5、查询建立对象文本(每行显示)

1 SELECT TEXT FROM USER_SOURCE U WHERE U.NAME = 'TEST_EXECUTE';

![2011122513513762.jpg][]

6、查询建立对象文本(CLOB字段显示)

1 SELECT DBMS_METADATA.get_ddl ('PROCEDURE', 'TEST_EXECUTE') from dual;

![2011122513520944.jpg][]

![2011122513521946.jpg][]

\-------------------------------用户的基本操作-------------------------  
1、创建用户:(abc不需要使用单引号，密码只能为数字)

1 CREATE USER TEST_DYJ IDENTIFIED BY ABC

2、删除用户

1 DROP USER TEST_DYJ

3、授权用户可以连接数据库

1 GRANT CREATE SESSION TO TEST_DYJ;

4、解锁和锁定用户

1 alter user test_dyj account UNLOCK/LOCK;

5、授权用户connect和resource角色

GRANT connect,RESOURCE TO TEST_DYJ

6、查询connect和resource角色具有的系统权限

1 SELECT * FROM dba_sys_privs t WHERE t.grantee = 'CONNECT'
    2 SELECT * FROM dba_sys_privs t WHERE t.grantee = 'RESOURCE'

\-------------------------------添加:

user\_objects : 记录了用户的所有对象，包含表、索引、过程、视图等信息，以及创建时间，状态是否有效等信息，是非DBA用户的大本营。想知道自己有哪些对象，往这里查。

user\_source :包含了系统中对象的原码，如存储过程，FUNCTION、PROCEDURE、PACKAGE等信息

cat或Tab ：包含当前用户所有的用户和视图信息和同义词信息，cat还多了一个SYNONYM;

dict :系统中所有的数据字典都存储在这里面  
  
Oracle中的数据字典有静态和动态之分。静态数据字典主要是在用户访问数据字典时不会发生改变的，但动态数据字典是依赖数据库运行的性能的，反映数据库运行的一些内在信息，所以在访问这类数据字典时往往不是一成不变的。以下分别就这两类数据字典来论述。

1 SELECT * FROM TAB;
    2 SELECT * FROM DICT;
    3 SELECT * FROM cat

转载于:https://www.cnblogs.com/caroline/archive/2011/12/25/2300972.html

[http_blog.csdn.net_kdnuggets_article_details_2076996]: http://blog.csdn.net/kdnuggets/article/details/2076996
[2011122513255651.jpg]: /images/20211205/a9acf6dc2cf942299f41c9a6fa15e834.png
[2011122513272854.jpg]: /images/20211205/c6a6e8ab03f74f7a93c16a66e9f479de.png
[2011122513301187.jpg]: /images/20211205/8bf03aedcd384054b1712d2fc823d09d.png
[2011122513310566.jpg]: /images/20211205/5d172b58229f4efebe5349bdf91b948a.png
[2011122513340774.jpg]: /images/20211205/1bdc18f042a34439b6cde50b1c749c3e.png
[2011122513353669.jpg]: /images/20211205/332756911bbf48999b1da0a016fd7004.png
[2011122513371719.jpg]: /images/20211205/f6d866dbf94147d9b9c321fc2a2c7223.png
[2011122513372961.jpg]: /images/20211205/93f17244264e4d6fa05262624429c10b.png
[2011122513503915.jpg]: /images/20211205/f320214c5b024b42a1f9d412b801a5c6.png
[2011122513505128.jpg]: /images/20211205/7ad7ab111b3e4870b19600b4a7178ce5.png
[2011122513510547.jpg]: /images/20211205/30082beec54a486492afeb5b3e9f575c.png
[2011122513524569.jpg]: /images/20211205/8c694c23c7bc4388b53656fc603025a7.png
[2011122513513762.jpg]: /images/20211205/a9a8dd308be24e0986bf8cdc800ea0c2.png
[2011122513520944.jpg]: /images/20211205/02007f5010ff40789e3a242663e172a7.png
[2011122513521946.jpg]: /images/20211205/3ec38ec5842e4f95ba5fcad351069b19.png