Oracle命令(二):Oracle用户

2016-12-29 19:32:23来源:CSDN作者:Magic__Stone人点击

1、查看所有用户
  select * from dba_users;
  select * from all_users;
  select * from user_users;
  select distinct owner from all_objects;

2、查看所有用户系统权限
  select * from dba_sys_privs;
  select * from user_sys_privs;

3、查看用户对象权限(少用)
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;

4、查看所有用户的表(少用)
  select * from user_tables;

5、显示当前用户名 
  show user;
  select user from dual;

6、显示当前用户有哪些表 
  select * from tab;

7、显示当前用户可以访问表 
  select * from all_tables;

8、显示当前用户所具有的权限
        select *from session_privs;

9、显示用户为scott的表 
  select * from dba_tables where owner='scott';

10、显示所有用户名和帐户的状态 
  select username,account_status from dba_users;

11、将scott帐号解锁(加锁) 
  alter user scott account unlock(lock);

12、当前用户的缺省表空间 
  select default_tablespace from dba_users where username=(select user from dual);

13、显示当前数据库的用户 
  select username,account_status,default_tablespace,temporary_tablespace from dba_users;

14、创建一个表空间
  create tablespace space_name datafile 'F:/space_name/my_data.dbf' size 200M;(文件名的后缀可以随便)

15、将表空间分配给用户
        alter user user_name default tablespace space_name;

16、建立一个新用户 
  create user user_name identified by pass_word 
  default tablespace users 
  temporary tablespace temp 
  quota unlimited on users 
  quota 10m on users;

17、给用户授权 
  grant connect,resource,dba to user_name;

18、分别授权 
        grant create session to user_name;  
        grant create tablespace to  user_name;  
        grant create (any) view to user_name;
        grant create (any) sequence to user_name;  
        grant create (any) table to user_name;  
        grant delete any table to user_name;  
        grant insert any table to user_name;  
        grant select any table to user_name;
        grant drop any table to user_name;  
        grant update any table to user_name;  
        grant execute any procedure to user_name;  
        grant unlimited tablespace to user_name;

19、对象授权 
  grant select on table_name to user_name;
  grant drop on tabl_ename to user_name;
  grant insert (id) on table_name to user_name;
  grant update (id) on table_name to user_name;

20、折消授权(语法同grant) 
  revoke unlimited tablespace from user_name;

21、折消对象授权(语法同grant) 
  revoke select on table_name from user_name;

22、用户改密码 
  alter user user_name identified by pass_word;
  password;按步骤输入。

23、授权sysdba给指定用户 
  grant sysdba to user_name;

24、取消指定用户的sysdba权限 
  revoke sysdba from user_name;

25、修改系统的授权的属性 
  alter system set remote_login_passwordfile=exclusive;

  能sysdba登录,能授权 
  alter system set remote_login_passwordfile=shared scope=spfile; 只能sysdba登录,不能授权 
  alter system set remote_login_passwordfile=none; 取消

26、查询pwfile中存放的用户信息 
  select * from v$pwfile_users;

27、操作表的用户的表
        select * from user_name.table_name;

28、权限传递(即用户A将权限授予B)
        grant alert table on table_name to user_name with admin option;(或with grant option)

29、创建角色(角色即权限的集合,可以把一个角色授予给用户)
        create role my_role

30、授予角色权限,授予用户角色
        grant create session to my_role;
        grant my_role to user_name;

31、查看所有角色
  select * from dba_roles;

32、查看用户所拥有的角色 
  select * from dba_role_privs;
  select * from user_role_privs;

33、删除角色
       drop role my_role;

34、删除表中全部数据(此方法消耗tablespace较少)
        truncate table table_name;

35、删除用户(用户拥有对象时) 
  drop user user_name (cascade);

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台