DBA Hub

📋Steps in this guide1/3

Schema Owners and Application Users

Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.

oracle miscconfigurationintermediate
by OracleDba
19 views
1

CURRENT_SCHEMA Approach

This method uses the session attribute to automatically point application users to the correct schema. First, we create the schema owner and an application user. Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects. Next, we create some roles to allow read-write and read-only access. We want to give our application user read-write access to the schema objects, so we grant the relevant role. We need to make sure the application user has its default schema pointing to the schema owner, so we create an AFTER LOGON trigger to do this for us. Now we are ready to create an object in the schema owner. Notice how the privileges are granted to the relevant roles. Without this, the objects would not be visible to the application user. We now have a functioning schema owner and application user. This method is ideal where the application user is simply an alternative entry point to the main schema, requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms. I don't find it very useful for developers who need to make copies or modify schema objects during development.

Code/Command (click line numbers to comment):

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
CONN sys/password AS SYSDBA

-- Remove existing users and roles with the same names.
DROP USER schema_owner CASCADE;
DROP USER app_user CASCADE;
DROP ROLE schema_rw_role;
DROP ROLE schema_ro_role;

-- Schema owner.
CREATE USER schema_owner IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
  
GRANT CONNECT, CREATE TABLE TO schema_owner;

-- Application user.
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT TO app_user;

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

GRANT schema_rw_role TO app_user;

CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;
/

CONN schema_owner/password

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

SQL>
2

Synonym Approach

This method relies on synonyms owned by the application user to point to the correct location of the schema objects. First, we create the users in a similar way to the previous example. Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms. Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user. Now we are ready to create an object in the schema owner in the same way we did in the previous example. If we now connect to the application user we are not able to see the object without qualifying it with a schema name. We can either proceed in this fashion, or use a synonym to point to the correct object. I find this method rather cumbersome due to the sheer number of synonyms required, especially when there are a large number of application users. Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance. I only use this method when I have developers who need to create their own schema objects for testing.

Code/Command (click line numbers to comment):

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
CONN sys/password AS SYSDBA

-- Remove existing users and roles with the same names.
DROP USER schema_owner CASCADE;
DROP USER app_user CASCADE;
DROP ROLE schema_rw_role;
DROP ROLE schema_ro_role;

-- Schema owner.
CREATE USER schema_owner IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
  
GRANT CONNECT, CREATE TABLE TO schema_owner;

-- Application user.
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT, CREATE SYNONYM TO app_user;

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

GRANT schema_rw_role TO app_user;

CONN schema_owner/password

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
ERROR:
ORA-04043: object test_tab does not exist


SQL> DESC schema_owner.test_tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;

Synonym created.

SQL> DESC test_tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

SQL>
3

Physical Organisation

In the following article I discuss my preferred physical organisation. The techniques described in this article can be used during its implementation. - My Utopian Development Environment Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!