oracle数据库的sql语句、数据库管理

conn system/12345678

--查询所有用户状态

select username,account_status from dba_users;

--查询数据文件

select tablespace_name, file_name from dba_data_files;

--查询所有权限

select * from system_privilege_map;

--创建用户

create user user1 identified by 123;

--授予连接 创建数据库 权限

grant connect to user1;

grant resource to user1;

--授予权限

grant create session, create table, create view to user1;

--授予权限 传递性

grant create session, create table, create view to user1 with admin option;

--回收权限

revoke create table, create view from user1;

conn user1/123

create table aaa(id int);

--连接数据库的权限

grant create session to user1;

grant unlimited tablespace to user1;//授予user1用户使用表空间的权限

--创建表的权限

grant create table to user1;

--删除表的权限

grant drop any table to user1;

--插入表的权限

grant insert any table to user1;

--修改表的权限

grant update any table to user1;

grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

grant select on tablename to user1;//授予zhangsan用户查看指定表的权限

grant drop on tablename to user1;//授予删除表的权限

grant insert on tablename to user1;//授予插入的权限

grant update on tablename to user1;//授予修改表的权限

grant insert(id) on tablename to user1;

grant update(id) on tablename to user1;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

grant alert all table to user1;//授予zhangsan用户alert任意表的权限