1.查看当前用户:select user from dual;
2.授权selectf:grant select on scott.dept to test;
3.修改默认表空间:alter user test default tablespace tt;
4.锁定账号:alter user test account lock;
5.解除锁定账号:alter user test account unlock;
6.创建用户:
数据库认证: create user qqq identified by qqq default tablespace users;
create user aaron identified by score
default tablespace data
temporary tablespace tep
quote 15M on data
quote 16M on users
password expire;
操作系统认证:
create user ops$bbker identified externally default tablespace example;
7.允许用户给其它用户授权: grant select on scott.dept to test with grant option;
8.授权(insert,select,delete,update):grant all on scott.dept to test with grant option;
9.给用户过程,函数,包的权限:grant execute on scott.proce01 to test1;
10.修改用户配额
alter user 用户名 quota o on users;
11.删除用户
drop user 用户名 [casecade---删除用户下所用数据;
12.当用户拥有anytable权限时,赋予查询数字字典的权限。
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- --------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
二、系统授权
1.给用户创建用户权限:grant create user to test;conn test/test;create user tt identified by tt;
2.删除用户
SQL> conn / as sys
已连接。
SQL> drop user tt;
3.回收权限:revoke select on scott.dept from test;
三、角色授权
1.创建角色:create role myrole;
给角色授权f:grant select on scott.deptment to myrole; grant select on scott.dept to myrole;
给用户受角色:grant myrole to test;
四、概要文件
主要控制用户
五.创建profile
create profile 名字
failed_login_attemps 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_verify_function verfiy_function
password_grace_time 5;
六.修改profile
alter profile default limit
failed_login_attemps 3
password_life_time 60
password_grace_time 10;
七.删除profile
drop profile 名字
drop profile 名字 cascade
八.profile对source的控制
create profile 名字 limit
session_per_user 3
cpu_per_session 10000
idle_time 60
connect_time 480;
九.相关视图
DBA_users
DBA_profiles
v$session;
DBA_TS_QUOTAS
session_prives
DBA_SYS_PRIVS
SESSION_PRIVS
DBA_TAB_PRIVS
DBA_COL_PRIVS
user_sys_privs
role_role_privs
role_sys_privs
role_tab_privs
DBA_ROLES
DBA_ROLES_PRIVS
SESSION_ROLES