Oracle数据库管理需要对应的权限,最好是DBA权限
用户管理:
01 | --创建用户给其一个密码(必须给其一个密码) |
02 | create user king IDENTIFIED by king; |
04 | grant connect to king; |
05 | grant resource to king; |
07 | alter user scott account unlock ; |
09 | alter user scott account lock; |
11 | alter user scott IDENTIFIED by tarena123; |
别名管理:
2 | --定义一个公有的别名 scott.emp ----> emp |
3 | create public synonym emp for scott.emp; |
2 | drop public synonym table_name; |
2 | select * from dba_synonyms |
权限管理:
对表操作的权限:
1 | grant select on emp to jsd1404; |
2 | grant insert on emp to jsd1404; |
3 | grant delete on emp to jsd1404; |
4 | grant update on emp to jsd1404; |
5 | grant alter on emp to jsd1404; |
6 | grant update on emp to jsd1404 with grant option; 授权更新权限转移给xujin用户,许进用户可以继续授权; |
收回权限:
1 | revoke select on emp from jsd1404; |
2 | revoke insert on emp from jsd1404; |
3 | revoke update on emp from jsd1404; |
4 | revoke delete on emp from jsd1404; |
5 | revoke alter on emp from jsd1404; |
对用户操作的权限:
1 | grant connect to king;--给用户授予连接的权限 |
2 | grant resource to king;--给用户king授予 所有资源的权限 |
对存储过程的权限:
1 | grant create procedure to jsd1404;--授予创建存储过程的权限 |
2 | grant execute procedure_name to jsd1404;--授予执行某个存储过程的权限 |
对表空间操作的权限:
1 | grant create tablespace to jsd1404; --授予可以创建tablespace 的权限 |
2 | grant alter tablespace to jsd1404;--授予可以修改tablespace 的权限 |
其他:
1 | select * from dba_users;-- 查询数据库中的所有用户 |
2 | select table_name,privilege from dba_tab_privs where grantee= 'jsd1404' ;-- 查询一个用户拥有的对象权限 |
3 | select * from dba_sys_privs where grantee= 'jsd1404' ;-- 查询一个用户拥有的系统权限 |
4 | select * from session_privs; --当钱会话有效的系统权限 |
1 | grant update on table1 to jsd1404 with grant option; 授权更新权限转移给xujin用户,许进用户可以继续授权; |