Oracle 开发人员权限控制
需求介绍
测试数据库的权限收紧,只有DBA有修改删除表结构的权限,开发人员不能有,后者只能有针对表数据的权限。
创建表空间语句
-- DATA FILES SELECT * FROM DBA_DATA_FILES; CREATE TABLESPACE BINGJJFLY LOGGING DATAFILE '/home/******/BINGJJFLY.dbf' SIZE 500M AUTOEXTEND ON NEXT 500M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL; -- CONNECT USER CREATE USER BINGJJFLY IDENTIFIED BY ****** DEFAULT TABLESPACE BINGJJFLY; GRANT CONNECT TO BINGJJFLY;
GRANT CREATE ANY SYNONYM TO BINGJJFLY; -- DBA USER CREATE USER BINGJJFLY_DBA IDENTIFIED BY ****** DEFAULT TABLESPACE BINGJJFLY; GRANT DBA TO BINGJJFLY_DBA;
DBA用户执行DDL语句及授权
CREATE TABLE TEST1 (ID INTEGER NOT NULL, NAME VARCHAR2(30 CHAR)); ALTER TABLE TEST1 ADD ADD_TIME TIMESTAMP; GRANT SELECT ON TEST1 TO BINGJJFLY; GRANT UPDATE ON TEST1 TO BINGJJFLY; GRANT DELETE ON TEST1 TO BINGJJFLY; GRANT INSERT ON TEST1 TO BINGJJFLY; DROP TABLE TEST1;
开发组账户创建同义词
CREATE OR REPLACE SYNONYM TEST1 FOR BINGJJFLY_DBA.TEST1; SELECT * FROM TEST1; UPDATE TEST1 SET ID = '1';