用户+授权
用户:
创建用户:
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;