1、显示当前用户名
select user from dual;
show user
2、显示当然用户有哪些表
select * from tab;
3、显示当所有用户的表
select * from user_tables;
4、显示当有用户可以访问表
select * from all_tables;
5、显示用户为SCOTT的表
select * from dba_tables where owner='SCOTT';
6、显示所有用户名和帐户的状态
select username,account_status from dba_users;
7、显示所有用户信息
select * from dba_users;
8、将SCOTT帐号解锁(加锁)
alter user scott account unlock(lock);
9、当前用户的缺省表空间
SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);
10、显示当前数据库的用户
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;
11、建立一个新用户
create user edpthw identified by "123456"
default tablespace users
temporary tablespace temp
quota unlimited on users
quota 10m on mytbs2;
12、给用户授权
grant connect,resource to edpthw;
13、分别授权
GRANT CREATE ANY TABLE TO "hmtong"
GRANT CREATE TABLE TO "hmtong"
GRANT DROP ANY TABLE TO "hmtong"
GRANT SELECT ANY TABLE TO "hmtong"
14、对象授权
GRANT SELECT ON "hmtong"."A" TO "edpthw"
15、折消授权
REVOKE UNLIMITED TABLESPACE FROM "hmtong"
16、折消对象授权
REVOKE SELECT ON "hmtong"."A" FROM "edpthw"
17、用户改密码
alter user edpthw identified by edpthw;
18、授权sysdba给指定用户
grant sysdba to hmtong;
19、取消指定用户的sysdba权限
revoke sysdba from hmtong;
20、修改系统的授权的属性
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; 取消
21、授权连接给指定用户
GRANT "CONNECT" TO "hmtong"; GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;
22、查询Pwfile中存放的用户信息
select * from v$pwfile_users;
