Oracle安全管理

Oracle 10G 数据库系统学习笔记《安全管理》2008-09-26 20:20创建TEST用户

利用企业管理工具创建TEST用户的SQL语法
CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "******" ACCOUNT UNLOCK
GRANT "CONNECT" TO "TEST"

利用SQL*Plus创建TEST用户
SQL> conn / as sysdba
SQL> CREATE USER test IDENTIFIED BY test1234
DEFAULT TABLESPACE ts1
GRANT CONNECT TO test;

授予test用户查询的权限
SQL> conn / as sysdba
SQL> grant select on scott.dept to test;
SQL> conn test/test1234
SQL> select * from scott.dept;

更改test用户的默认表空间为ts1
SQL> conn / as sysdba
SQL> alter user test default tablespace ts1;

修改test用户的密码
SQL> conn / as sysdba
SQL> alter user test identified by test1234;

锁定test用户帐号
SQL> conn / as sysdba
SQL> alter user test account lock;

.

对象授权（权限下放）
SQL> conn / as sysdba
SQL> GRANT SELECT ON scott.dept TO test WITH GRANT OPTION;
授予test2用户查询scott.dept表的权限
SQL> conn test/test1234
SQL> GRANT SELECT ON scott.dept TO test2;

将scott.dept表的所有权限授予test用户
SQL> conn / as sysdba
SQL> GRANT ALL ON scott.dept TO test [WITH GRANT OPTION];

授予test用户执行ts1.trigger1触发器的权限
SQL> conn / as sysdba
SQL> GRANT EXECUTE ON ts1.trigger1 TO test;

.

系统授权，授予test用户创建用户的权限
SQL> conn / as sysdba
SQL> GRANT CREATE USER TO test;
创建用户test2
SQL> conn test/test134
SQL> CREATE USER test2 IDENTIFIED BY test2;

授权test删除用户的权限
SQL> conn / as sysdba
SQL> GRANT DROP USER TO test;
删除用户test2
SQL> conn test/test1234
SQL> DROP USER test2;

级联系统授权方式
SQL> conn / as sysdba
SQL> GRANT CREATE USER TO test WITH ADMIN OPTION;
SQL> conn test/test134
SQL> GRANT CREATE USER TO test2;

撤销用户权限
SQL> conn / as sysdba
SQL> REVOKE SELECT ON scott.dept FROM test;

.

创建角色
SQL> conn / as sysdba
SQL> CREATE ROLE myrole;
给角色授权
SQL> GRANT SELECT ON scott.emp TO myrole;
SQL> GRANT SELECT ON scott.dept TO myrole;
给用户分配角色
SQL> GRANT myrole TO test;
SQL> conn test/test1234
SQL> SELECT * FROM scott.emp;