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
|
--/****************************************************************\
--** filename * sample.sql
--** description * Table, index, role and user creation samples.
--** Copyright (C) Solid Information Technology Ltd 1997
--\****************************************************************/
-- First create table TEST_TABLE
CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL,
TEXT VARCHAR,
TODAY DATE,
VALUE FLOAT,
PRIMARY KEY(ID)
);
-- Statements are ended with a semicolon.
-- Create another table named PROD_TABLE
CREATE TABLE PROD_TABLE (
ID INTEGER NOT NULL UNIQUE,
TEXT VARCHAR,
TODAY DATE,
VALUE FLOAT,
PRIMARY KEY (ID)
);
-- Create few users
CREATE USER CALVIN IDENTIFIED BY HOBBES;
CREATE USER DIANE IDENTIFIED BY PASSWO1;
CREATE USER JILL IDENTIFIED BY PASSWO2;
CREATE USER MARK IDENTIFIED BY PASSWO3;
CREATE USER JAKE IDENTIFIED BY PASSWO4;
-- Change Jakes password to ELWOOD
ALTER USER JAKE IDENTIFIED BY ELWOOD;
-- Delete user Calvin
DROP USER CALVIN;
-- Create TESTERS and PRODUCTION roles
CREATE ROLE TESTERS;
CREATE ROLE PRODUCTION;
-- Grant rights for these roles
GRANT INSERT, SELECT ON TEST_TABLE TO TESTERS;
GRANT INSERT, SELECT, DELETE ON PROD_TABLE TO PRODUCTION;
-- Grant roles to users.
-- Diane and Mark are testers while the others are production users.
GRANT TESTERS TO DIANE;
GRANT TESTERS TO MARK;
GRANT PRODUCTION TO JILL;
GRANT PRODUCTION TO JAKE;
-- Grant Diane right to delete from test_table
GRANT DELETE ON TEST_TABLE TO JILL;
-- Grant Mark Production role
GRANT PRODUCTION TO MARK;
-- Revoke the delete right from Jill on PROD_TABLE
REVOKE DELETE ON PROD_TABLE FROM JILL;
-- Revoke the PRODUCTION role from JAKE
REVOKE PRODUCTION FROM JAKE;
-- Revoke the delete right on table TEST_TABLE from TESTERS role.
REVOKE DELETE ON TEST_TABLE FROM TESTERS;
-- Grant Jill the system administrator role. Jill will have all
-- rights to all tables (except the system tables) from now on.
GRANT SYS_ADMIN_ROLE TO JILL;
-- Drop the GUEST_USERS role. When this role is dropped all users
-- granted this role have their rights removed.
DROP ROLE TESTERS;
-- Make the changes to tables, users and roles
-- permanent by committing the work we have done.
-- If the autocommit set to ON, this has no effect.
COMMIT WORK;
-- Now we modify the tables and create few indexes.
-- Add another column to table TEST_TABLE
ALTER TABLE TEST_TABLE ADD COLUMN C CHAR(1);
COMMIT WORK;
-- Delete the added column
ALTER TABLE TEST_TABLE DROP COLUMN C;
-- Create a non-unique index on column ID to TEST_TABLE
CREATE INDEX X_TEST ON TEST_TABLE (ID);
-- Create a unique index on column VALUE to TEST_TABLE
CREATE UNIQUE INDEX UX_TEST ON TEST_TABLE (VALUE);
-- Drop the non-unique index
DROP INDEX X_TEST;
-- And again commit the work we have done.
COMMIT WORK;
|
