create user in postgres - basic syntax

I will create

a group role called common_users

a user called ro_user1

in a database called db01

The read-only user ro_user1 should be able perform queries against all tables owned by the schema schema1.

First, create the role common_users by logging onto the postgres (default) database

psql

CREATE ROLE common_users WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS;

GRANT pg_read_all_stats TO common_users;

Then, create the user ro_user1:

create user ro_user1 password 'mysecretpassword';
grant common_users to ro_user1;
grant connect on database db01 to ro_user1;

Log into the database db01 and revoke and grant some privileges:

psql
\connect db01
revoke all on schema schema1 from ro_user1;
grant usage on schema schema1 to ro_user1;
grant select on all tables in schema schema1 to ro_user1;

Confirm the privileges:

\connect postgres
select database_privs('ro_user1');

How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.

Changes in privilege "SELECT ANY DICTIONARY" in Oracle 12c

From version 12.1 and onwards, Oracle has introduced some changes to enhance security when granting the system privilege "SELECT ANY DICTIONARY".

In the New Features guide for version 12.1, the authors explain:

The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS. This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

The access to USER$ has also been excempt when granting the system privilege SELECT ANY TABLE and the role SELECT_CATALOG_ROLE, but I have not yet found the documentation that verifies this.

Overview of role privileges

The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain:

Select  Role || ' contains role:' "role privilege type", Granted_Role "privilege"   From Role_Role_Privs Where Role='ONLINE_USERS'
Union
Select Role || ' contains system priv:', Privilege      From Role_Sys_Privs  Where Role='ONLINE_USERS'
Union
Select Role || ' contains object priv:',  Privilege || ' ON ' || owner || '.' || table_name From Role_Tab_Privs Where Role='ONLINE_USERS'
order by 1;

Output:

role privilege type	privilege
ONLINE_USERS contains object priv:	UPDATE ON SCOTT.EMP
ONLINE_USERS contains role:	RESOURCE
ONLINE_USERS contains system priv:	UPDATE ANY TABLE

How does Oracle decide which roles should be enabled in a session?

Oracle uses the concept of default roles to decide whether or not a role should be enabled when a user establishes a session.

CREATE USER ERIC
IDENTIFIED BY pass1w0rd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

ALTER USER ERIC QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO ERIC; 

Oracle states in its documentation

"When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles."

So we should have two default roles enabled for our user ERIC, confirmed below:

SQL> connect eric/pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE

Let's create a new role:

CREATE ROLE online_users NOT IDENTIFIED;

Role created.

Because the user ERIC was created with ALL roles enabled by default, all subsequent sessions established by ERIC will now have access to the role online_users:

GRANT online_users to ERIC; 

Grant succeeded.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE
ONLINE_USERS

If you want to prevent this behavior, you can alter the user with a limited default setting:

ALTER USER ERIC DEFAULT ROLE CONNECT;

User altered.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT

User ERIC must now explicitly enable the required roles from within his session:

set role connect, online_users;

Role set.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
ONLINE_USERS

These features can be incorporated into a security-oriented application setup, where specific people should only be granted specific privileges through database roles.

Getting ORA-01031: insufficient privileges when data dictionary table is being used in a view

I must admit I have been consulted in these situations before, but since then I had forgotten how it worked and failed to take notes on how to solve it.

So here it is: a user is getting a run-time error ORA-01031: insufficient privileges when accessing his view.
The view is based on his own objects and a lookup to the dynamic performance view V$DATABASE.

Example:

connect scott/tiger

CREATE VIEW MYVIEW AS
SELECT 
FROM MYTABLE MT,
     V$DATABASE DB
WHERE....
AND... ;

If the user has only SELECT ANY TABLE, Oracle will return runtime error ORA-01031 when the view is compiled.

However, if you give user scott the SELECT privilege on the table directly:

GRANT SELECT ON V_$DATABASE TO SCOTT;

then Oracles rules for object creation is honored and the runtime error will disappear.

Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';

Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:

GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;

Can you revoke all object privileges from a role in one go?

Answer: No.

The closest shortcut you get is to revoke all privileges per object from a role.

Example:

REVOKE ALL ON SCOTT.T1 FROM TEST_ROLE;