Schema only user accounts in Oracle
Prior to Oracle 18c , there is no difference between user and schema . It has been used interchangeably .
SQL Server has introduced the concept of “SCHEMA” only accounts quite a while ago. Now , Oracle 18c ( aka 12c R2) has this new feature .
We can create a schema only account with the clause “ no authentication”. Once this schema gets created , the objects can be accessed by either proxy users , other users .
Prior to this , typically , I create a user with no “create session” privilege ; store only the persistent information in that user . Then there will be another user that does the data processing and the logic is exposed as an API.
Get Zahir Mohideen’s stories in your inbox
Join Medium for free to get updates from this writer.
With 18c and “ no authentication” clause , it is easier from management/administration perspective.
Example shown below
SQL> create user sales no authentication ;
User created.
SQL> alter user sales quota unlimited on users;
User altered.
SQL> alter user sales default tablespace users;
User altered.
SQL> create table sales.employees ( empname char(50)) ;
Table created.
SQL> create user webappuser identified by Pa55w0rd ;
User created.
SQL> grant select , insert , update , delete on sales.employees to webappuser;
Grant succeeded.
SQL> grant create session to webappuser ;
Grant succeeded.
SQL> alter user webappuser default tablespace users;
User altered.
SQL> alter user webappuser quota unlimited on users;
User altered.Now , login as webappuser .
SQL> insert into sales.employees values (‘Zahir’);
1 row created.
SQL> commit;
Commit complete.
SQL> show user
USER is "WEBAPPUSER"Originally published at https://zahirmohideen.blogspot.com/2018/09/schema-only-user-accounts-in-oracle.html
