Four, users and permissions

Four, users and permissions

  • Master the use of establishing users and profiles
  • Grasp the meaning of system permissions and grant and recycle
  • Grasp the meaning of object permissions and grant and reclaim
  • Grasp the meaning of roles, grant permissions to roles, and assign roles to users

Oracle11g basics-default user

  • Only use a legal user account to access the Oracle database
  • Oracle has several default database users
Insert picture description here

Oracle11g basics-create a new user

  • To connect to the Oracle database, you need to create a user account
  • Each user has a default table space and a temporary table space
  • The CREATE USER command is used to create a new user

The syntax of the CREATE USER command is:

Insert picture description here
Insert picture description here

profile (profile) management user 1

当创建用户时候,如果没有指定profile,那么Oracle把名字叫default的profile赋予给用户。
   指定test1只能最多输入3次密码,
   创建profile文件:create  profile  pro1 limit failed_login_attempts 3 password_lock_time 2;
   其中,FAILED_LOGIN_ATTEMPTS:用于指定联系登陆的最大失败次数. PASSWORD_LOCK_TIME:用于指定帐户被锁定的天数. 
创建属于pro1的用户:
    create user   aa identified by aa profile pro1;
修改用户的profile文件:
    alter user test1 profile pro1;

https://computer name:1158/em/

Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here

profile (profile) management user 2

解除锁定: alter  user  test1  account  unlock;
   终止口令:需要每隔10天修改密码,最多宽限2天:ALTER PROFILE "PRO1" LIMIT PASSWORD_LIFE_TIME 10 PASSWORD_GRACE_TIME 2。
  删除profile:drop profile pro1 cascade; 使得test1的profile重新变成了default 

Oracle11g basics-permissions

  • Permission refers to the right to execute specific commands or access database objects
  • There are two types of permissions, system permissions and object permissions
  • System permissions allow users to perform certain database operations, such as creating a table is a system permission
  • Object permissions allow users to perform specific operations on database objects (such as tables, views, sequences, etc.)

Oracle11g basics-system permissions

   授予用户权限,可以是系统权限或者对象权限。
   GRANT privilege [,privilege…]
   TO  user [, user|role, PUBLIC…] 
    [WITH ADMIN OPTION];
说明:包含了WITH ADMIN OPTION,只能是系统权限。

   收回系统权限REVOKE
例:REVOKE create session FROM test1;

The GRANT command can be used to assign permissions or roles to users

Insert picture description here

Oracle11g basics-object permissions

   对象权限:访问某个用户的特定对象(如表、视图等)
的对象权限(select on scott.student) 
 GRANT object_privilege [columns…]
   ON  object  TO  user [, user|role, PUBLIC…] 
    [WITH GRANT OPTION];

  收回对象权限REVOKE:
   REVOKE privilege     ON object 
   FROM {user|role|PUBLIC};
例:revoke select on scott.student from test2;

Grant user wang permission to operate EMP table objects

Insert picture description here

Grant permissions accurate to the column

Insert picture description here
Insert picture description here
Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Cascade grant, cascade reclaim (all reclaim)

Insert picture description here

Oracle11g basics-role management

角色管理:角色是一组相关权限的组合,可以将权限授予角
色,再把角色授予用户,以简化权限管理。
(1)创建角色CREATE ROLE,应该具有CREATE ROLE系统权限。  
   CREATE ROLE role_name;
(2) 授予角色权限,可以是系统权限或者对象权限。
   GRANT privilege TO ROLE;
例: grant create session ,create any table, drop any table to role1;
(3) 将角色授予用户
    GRANT role TO user;
例: grant role1 to test2;
(4) 从用户收回角色
    REVOKE  role  FROM  user;

Insert picture description here


Insert picture description here

The GRANT command can be used to assign permissions or roles to users

Insert picture description here

Oracle11g basics-change and delete users

The ALTER USER command can be used to change the password

Insert picture description here

DROP USER command is used to delete users

Insert picture description here