Java Training-oracle Database Learning "2" User rights

1. Connect User: Connect username/password;
   

2. Creating User: Create user username identified by password;

3. 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