Oracle用户与权限学习总结

用户与权限
创建用户前的思考

确定用于此用户数据存储的表空间
确定分配给此用户的表空间使用量
为此用户指定默认永久表空间与临时表空间
创建用户
赋予用户权限
快速创建用户

drop user scott cascade;
create user scott identified by tiger;
grant create table to scott;
grant create procedure to scott;
grant create session to scott;
grant dba to scott;
创建用户

密码是数字+字母组合的，要用引号括起来
创建用户的永久表空间和临时表空间，然后创建一个用户名为test102，密码test102的用户
create tablespace test102 datafile '/db/test102.dbf' size 50m;
create temporary tablespace temp102 tempfile '/db/temp102.dbf' size 50m;
create user test102 identified by test102 default tablespace test102 temporary tablespace temp102;
连接测试
SQL> conn test102/test102
ERROR:
ORA-01045: user TEST102 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
用sysdba用户，给test102授权：会话权限，创建表的权限，在表空间test102上分配给test102用户10M的使用权
grant create session to test102;
grant create table to test102;
alter user test102 quota 10m on test102;
SQL> show user
USER is "TEST102"
SQL> create table tab102(id int);
Table created.
解锁用户

alter user username account unlock;
权限管理

赋予权限
grant create session to test102；
grant execute on dbms_output（一个存储过程）to test102;
回收权限
revoke create table from test102；
修改用户属性

修改用户的空间配额
alter user test102 quota 20m on test102;
select * from dba_ts_quotas;
修改密码
SQL> conn test102/test102
Connected.
SQL> password
Changing password for TEST102
Old password:
New password:
Retype new password:
Password changed
修改用户的默认表空间（永久和临时表空间）
alter user test102 default tablespace test100;
alter user test102 temporary tablespace temp;
select * from dba_users;
用户资源限制profile

为用户指定资源限制，必须：动态地使用alter system或使用初始化参数resource_limit使资源限制生效。该改变对密码资源无效，密码资源总是可用。
SQL> show parameter resource_limit
NAME                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                          boolean     FALSE
SQL>  alter system set resource_limit=true;
System altered.
SQL> show parameter resource_limit
NAME                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                          boolean     TRUE
常用资源限制参数
sessions_per_user 2 限制每个用户的最大连接数（并发数）
idle_time 1 限制用户的最大空闲时间（单位是分钟），如果用户连接会话在最大空闲时间内无任何操作，那么oracle将自动断开此连接。
failed_login_attempts 3 限制登录失败最大次数，在同一个终端如果登录失败次数超过最大值，则账号被oracle自动锁定，必须经过解锁用户，才能正常使用
创建profile命令，必须设置resources_limit参数等于TRUE，profile才能生效
 
实例：
create profile test102 limit sessions_per_user 2 idle_time 1 failed_login_attempts 2;
将profile文件指定到用户
alter user test102 profile test102;
还原默认值
alter user dinya profile default;
修改PROFILE
alter profile test limit idle_time 60;
删除PROFILE
drop profile test;
drop profile test cascade;
注意：
已分配的profile，删除时必须加cascade选项。
必须要有create profile 权限，才能创建profile。
DEFAULT为默认profile，不能删除。
 
删除用户

删除命令，当用户处于连接状态时，不能删除
SQL> drop user test102;
drop user test102
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
使用cascade选项删除用户下的所有对象（表、索引等）
SQL> drop user test102 cascade;
User dropped.
查询用户的连接信息
select * from v$session where username='TEST102';
杀掉连接进程
alter system kill session 'SID,SERIAL#';
alter system kill session '159,192';
执行命令之后，状态为KILLED
当有多个会话时使用下面命令进行批量删除
select 'alter system kill session '||''''||sid||','||serial#||''''||';'from v$session t where t.USERNAME='SCOTT';
 
'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';
--------------------------------------------------------------------------------
alter system kill session '142,915';
alter system kill session '151,129';
alter system kill session '152,331';
执行alter……这些命令，进行批量删除，可以使用spool命令，输出.sql文件，脚本
 
sys、system用户的区别

sys是Oracle数据库中权限最高的帐号，具有create database的权限，而system没有这个权限，sys的角色是sysdba，system的角色是sysoper。
这两个用户共有的权限：startup/shutdown/dba，平时用system来管理数据库就可以。
SCOTT
查scott用户的创建时间、用户状态、使用的默认表空间、临时表空间等信息

select * from user_users
查看scott用户自己拥有什么角色

select * from user_role_privs;
注：“ADM”表示这个用户是否可以把该具有的角色赋予给其他的用户
select * from dba_role_privs（所有数据库用户具有哪些角色，这个视图只有dba角色的权限才可以查询）
查看自己有什么权限

select * from session_privs;
scott用户自己拥有多少的表

SQL>select * from user_tables;
另：select * from all_tables; 其他用户所拥有的表
另：select * from dba_tables；数据库中所有用户的表
sys授予scott用户dba角色

SQL> grant dba to scott;
另：如果这样
SQL> grant dba to scott with admin option;
scott用户就可以把dba的权限授予给其他的用户了。
回收scott用户的dba角色

SQL> revoke dba from scott;