用户+授权

用户:

创建用户:

Create user user_name

Identified by password

Default tablespace tablespace_name

Quota 30M on tablespace_name

Password expire

Account unlock;

修改用户密码:

Alter user user_name identified by new_password;

Grant connect to user_name identified by new_password;

修改默认表空间:

Alter user user_name default tablespace tablespace_name;

修改表空间配额:

Alter user user_name quota 20M on tablespace_name;

修改临时表空间:

Alter user user_name temporary tablespace temporary_tablespace_name;

密码失效:

Alter user user_name password expire;

锁定用户:

Alter user user_name account lock;

Alter user user_name account unlock;

删除用户:

Drop user user_name ;

Drop user user_name cascade;

用户配置文件

创建配置文件:

Create profile profile_name limit

Sessions_per_user  n  (个)     //会话数

cpu_per_session   n  (百分之一秒)    //每个会话战CPU的总时间

Connect_time     n  (分钟)    //一个会话连接总时间

Idle_time         n  (分钟)    //用户可以闲置的时间

Password_life_time  n  (天)    //用户密码有效时间

使用配置文件:

Alter user user_name profile profile_name;

Show prarmeter resource_limit; //查看resource_limit 默认值

Alter system set resource_limit=true;

修改配置文件:

Alter profile profile_name limit session_per_user n;

删除配置文件:

Drop profile profile_name cascade;

权限

授权:

Grant create view to user_name;

Grant select on scott.emp to user_name;

Grant role_name to user_name with admin option;

收回权限:

Revoke create view from user_name;

角色

创建角色:

Create role role_name identified by password;

授权给角色:

Grant create session to role_name with admin option;

设置角色失效:

Alter user user_name default role none;

设置角色生效:

Alter user user_name default role all;

禁用角色:

Set role all except role_name;

修改免密码:

Alter role role_name not identified;

删除角色:

Drop role role_name;

原文地址:https://www.cnblogs.com/kaibing/p/7887322.html