1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
| 查看用户拥有的角色及权限:
set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and a.grantee='WXQYHUSR' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from dba_sys_privs,dba_users d where grantee='WXQYHUSR' and grantee=d.username order by username;
select * from role_sys_privs where role='DATAROLE';
set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and a.grantee like 'LAMS%' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from dba_sys_privs,dba_users d where grantee like 'LAMS%' and grantee=d.username order by username;
查看角色有哪些权限:
select * from role_sys_privs where role='APPSROLE';
查看用户拥有访问对象(表，视图，同义词)权限：
set linesize 500
set pagesize 500
col username for a25
col owner for a10
col table_owner for a20
col table_name for a30
col grantor for a20
col privilege for a20
select grantee username,privilege,owner table_owner,table_name,grantor,type from dba_tab_privs where grantee='PASUSR';
查看表权限:
col table_name for a20
col GRANTEE for a15
col PRIVILEGE for a30
col owner for a20
col GRANTOR for a15
select grantee,table_name,object_type type,owner table_owner,grantor,privilege from user_tab_privs ,user_objects where object_name = table_name and table_name in ('WP_JOB');
select owner,table_name,grantor,privilege,grantee from dba_tab_privs where table_name in('WP_JOB');
set linesize 500
set pagesize 500
col username for a25
col owner for a10
col table_owner for a20
col table_name for a30
col grantor for a20
col privilege for a20
select grantee username,privilege,owner table_owner,table_name,grantor from dba_tab_privs where grantee='PASUSR';
同义词:
select * from dba_synonyms;
select * from user_synonyms;
set linesize 500
set pagesize 500
col table_owner for a25
col table_name for a25
col synonym_name for a25
select table_owner,table_name,synonym_name from user_synonyms;
查看用户系统权限:
select * from dba_sys_privs where grantee='ADMIN';
查看用户对象(表，视图，同义词)权限:
select * from dba_tab_privs;
select * from user_tab_privs;
查看用户所拥有的角色权限:
select * from role_sys_privs where role='DATAROLE';
select * from dba_role_privs where granted_role='ECPDATA_RW_ROLE';
查看表空间限额:
-1是代表没有限制,其它值多少就是多少了.
SQL> col tablespace_name for a20
SQL> col username for a20
SQL> select tablespace_name,username from dba_ts_quotas;
TABLESPACE_NAME USERNAME
MVOIDX MVODEVUSR
MVODATA MVODEVUSR
MVODATA MVODATA
MVOIDX MVODATA
SYSAUX APPQOSSYS
MVOIDX MVOUSR
6 rows selected.
SQL> select tablespace_name,username,max_blocks from dba_ts_quotas;
TABLESPACE_NAME USERNAME MAX_BLOCKS
MVOIDX MVODEVUSR -1
MVODATA MVODEVUSR -1
MVODATA MVODATA -1
MVOIDX MVODATA -1
SYSAUX APPQOSSYS -1
MVOIDX MVOUSR -1
6 rows selected.
|
