Connect User: Connect username/password;
Creating User: Create user username identified by password;
Authorization: Grant permission to user name;
Note: System permissions can only be granted by the DBA user, and an ordinary user may have the same user rights as the system, but never the same permissions as the SYS user, and the permissions of the system user can be reclaimed.
To query the permissions that a user has:
1.查看所有用户:
select
*
from
dba_users;
select
*
from
all_users;
select
*
from
user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select
*
from
dba_sys_privs;
select
*
from
user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>
select
*
from
role_sys_privs;
4.查看用户对象权限:
select
*
from
dba_tab_privs;
select
*
from
all_tab_privs;
select
*
from
user_tab_privs;
5.查看所有角色:
select
*
from
dba_roles;
6.查看用户或角色所拥有的角色:
select
*
from
dba_role_privs;
select
*
from
user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select
*
from
V$PWFILE_USERS
比如我要查看用户 wzsb的拥有的权限:
SQL>
select
*
from
dba_sys_privs
where
grantee=
‘WZSB‘
;
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
WZSB
CREATE
TRIGGER
NO
WZSB UNLIMITED TABLESPACE
NO
比如我要查看用户 wzsb的拥有的角色:
SQL>
select
*
from
dba_role_privs
where
grantee=
‘WZSB‘
;
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
WZSB DBA
NO
YES
查看一个用户所有的权限及角色
select
privilege
from
dba_sys_privs
where
grantee=
‘WZSB‘
union
select
privilege
from
dba_sys_privs
where
grantee
in
(
select
granted_role
from
dba_role_privs
where
grantee=
‘WZSB‘
);
3.1 Oracle Permissions classification:
System permissions: The system specifies the user's permission to use the database. (System permissions are for users).
Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).
3.2 System permissions:
DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.
For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.
3.3 Entity permissions:
Grant orders for Entity permissions
The syntax is as follows: GRANT Entity permission name | All to user | roles | Public, where all represents all entity permissions for the entity.
such as: Sql>grant SELECT on books_quthors to USER1;
Authorization information to query entity permissions for a table: Sql>select * from User_tab_prives
Reclaim entity permissions, using revoke, with the following syntax:
REVOKE Entity Permission name | All on entity name from user name | role name | public.
Permission pass:
Grant SELECT on Xiaoming.temp to Xiaohong;//Login xiaoming, the query permission of Xiaoming's temp table is granted to Xiaohong;
This article is from the "Ming" blog, make sure to keep this source http://8967938.blog.51cto.com/8957938/1678274
Java Training-oracle Database Learning "2" User rights