Monday, September 17, 2018

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.   

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"
SQL>

No comments:

Post a Comment