1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
| SQL> create user deplacement identified by d
2 /
User created.
Elapsed: 00:00:00.06
SQL> grant connect, resource to deplacement
2 /
Grant succeeded.
Elapsed: 00:00:00.03
SQL> grant create view to deplacement
2 /
Grant succeeded.
Elapsed: 00:00:00.02
SQL> GRANT execute ON dbms_lock TO deplacement
2 /
Grant succeeded.
Elapsed: 00:00:00.02
SQL> create user DEPLACEMENTS_MAJ identified by d
2 /
User created.
Elapsed: 00:00:00.05
SQL> grant connect to DEPLACEMENTS_MAJ
2 /
Grant succeeded.
Elapsed: 00:00:00.03
SQL> create role DEPLACEMENTS_CONSULT
2 /
Role created.
Elapsed: 00:00:00.02
SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where grantee='DEPLACEMENTS_CONSULT'
2 /
no rows selected
Elapsed: 00:00:00.02
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn deplacement/d
Connected.
SQL> create or replace trigger do_grant
2 after CREATE on schema
3 declare
4 l_str varchar2(255);
5 l_str2 varchar2(255);
6 l_job number;
7 l_job2 number;
8 begin
9 if ( ora_dict_obj_type = 'TABLE' )
10 then
11 l_str := 'execute immediate "grant select on ' ||
12 ora_dict_obj_name ||
13 ' to DEPLACEMENTS_CONSULT";';
14 l_str2 := 'execute immediate "grant select, insert, update, delete on ' ||
15 ora_dict_obj_name ||
16 ' to DEPLACEMENTS_MAJ";';
17 dbms_job.submit( l_job, replace(l_str,'"','''') );
18 dbms_job.submit( l_job2, replace(l_str2,'"','''') );
19 elsif ( ora_dict_obj_type = 'VIEW' )
20 then
21 l_str := 'execute immediate "grant select on ' ||
22 ora_dict_obj_name ||
23 ' to DEPLACEMENTS_CONSULT";';
24 l_str2 := 'execute immediate "grant select on ' ||
25 ora_dict_obj_name ||
26 ' to DEPLACEMENTS_MAJ";';
27 dbms_job.submit( l_job, replace(l_str,'"','''') );
28 dbms_job.submit( l_job2, replace(l_str2,'"','''') );
29 end if;
30 end;
31 /
Trigger created.
Elapsed: 00:00:00.05
SQL> create table t (x number)
2 /
Table created.
Elapsed: 00:00:00.06
SQL> create view v as select x from t
2 /
View created.
Elapsed: 00:00:00.13
SQL> begin dbms_lock.sleep(120); end;
2 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:00.03
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn DEPLACEMENTS_MAJ/d
Connected.
SQL> select * from deplacement.t
2 /
no rows selected
Elapsed: 00:00:00.01
SQL> insert into deplacement.t values (1)
2 /
1 row created.
Elapsed: 00:00:00.03
SQL> commit
2 /
Commit complete.
Elapsed: 00:00:00.01
SQL> select * from deplacement.v
2 /
X
----------
1
Elapsed: 00:00:00.01
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn skuatamad/mdp as sysdba
Connected.
SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where grantee='DEPLACEMENTS_CONSULT'
2 /
GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
DEPLACEMENTS_CONSULT DEPLACEMENT T SELECT
DEPLACEMENTS_CONSULT DEPLACEMENT V SELECT
Elapsed: 00:00:00.03
SQL> |
Partager