Disable & Enable Logon and Revoke & Grant User Logon in Teradata

During Database maintenance DBA might need to block databases users to login to database and based on different scenario DBA need to decide how to block user to login to database. Here I discussed some scenario:

Scenario 1: Disable logon for every user except DBC

Scenario 2: Disable logon for every user except some database users (Not only DBC)

Scenario 3: Disable logon for any specific user

Scenario 1: Disable logon for every user except DBC

In some maintenance DBA want to make sure no user can login to the database except DBA and DBA use DBC user to log into database. In that case DBA can stop all the users except DBC from Teradata Gateway Level. The gateway software is the interface between the network and Teradata Database. It runs on the Teradata Database server as a separate operating system task. Client sessions that communicate through the gateway to Teradata Database are installed and running on workstations. If you disable logon from Gateway, then except DBC user it’s not possible to allow login for any other user.

In the following way all user logon can be disable log into database and enable only dbc user to login.

# psh pdestate -a

<--------------------- localhost -------------------------------->

PDE state is RUN/STARTED.

DBS state is 5: Logons are enabled - The system is quiescent

# cnsterm 6

Input Supervisor Command:

> disable logons

disable logons

19/01/31 10:52:25 Logons disabled.

Input Supervisor Command:

> enable dbc logons

enable dbc logons

19/01/31 10:53:39 DBC Logons enabled.

Input Supervisor Command:

> ^C

#

# psh pdestate -a

<--------------------- localhost -------------------------------->

PDE state is RUN/STARTED.

DBS state is 11: Only user DBC Logons are enabled - The system is quiescent

In the following way you can allow all the user to logon again from gateway level

# cnsterm 6

Input Supervisor Command:

> enable logons

enable logons

19/01/31 11:00:08 Logons enabled.

Input Supervisor Command:

> ^C

# psh pdestate -a

<--------------------- localhost -------------------------------->

PDE state is RUN/STARTED.

DBS state is 5: Logons are enabled - The system is quiescent

Scenario 2: Disable logon for every user except some database users (Not only DBC)

There can be some cases where during maintenance DBA need to allow some users to logon e.g some application user, viewpoint users, backup users, system user etc. In that case the procedure uses in scenario 1 will not work because if we disable logon from gateway level then it’s not possible to allow any user to logon except DBC user. In that case we have to disable logon in database level instead gateway level, so DBA can allow some users to logon. The dictionary table DBC.Logonruletbl use to control user logon in database level.

In the following way we can revoke logon to all user and allow some user to login to the database. In that example I allow login user mention in where condition and revoke login for rest of the users. In that example I allow some system user and one application user “application1” to login to the database and block rest of the users.

select 'revoke logon on all from '||trim(databasenamei)||';' (title '')

from dbc.dbase

where databasenamei not in

('dbc','crashdumps','all','default','dbcmanager','sysdba','systemfe','co

nsole','public','application1')

and databasenamei not like 'arcuser%'

and databasename not like 'bardba%'

and rowtype = 'u'

order by databasenamei;

Example Output:

Now execute the output. It will update the rule in dictionary table DBC.Logonruletbl.

After the maintenance the following way we can revert back the changes and grant the users again to login to the database.

select 'grant logon on all to '||trim(databasenamei)||';' (title '')

from dbc.dbase

where databasenamei not in

('dbc','crashdumps','all','default','dbcmanager','sysdba','systemfe','co

nsole','public','application1')

and databasenamei not like 'arcuser%'

and databasename not like 'bardba%'

and rowtype = 'u'

order by databasenamei;

Example Output:

Now execute the output and it will update the rule in dictionary table DBC.Logonruletbl

Before you revoke logon I recommend to copy DBC.Logonruletbl table so after the maintenance if you mess up with logon rules you can just copy back the date in DBC.Logonruletbl table from copied table.

Scenario 3: Disable logon for any specific user

In the example I have shown how to block any specific user to login into database. Let’s say we want to block an application user to logon the following way we can do it. In that example we are blocking user application1.

select * from DBC.Logonruletbl;

revoke logon on all from application1;

*** Logon has been revoked.

Now the changes will be updated in DBC.Logonruletbl table, lets check.

select * from DBC.Logonruletbl;

Now if you try to logon from that user it will fail:

# bteq

Teradata BTEQ 15.10.00.03 for LINUX. PID: 2841

Copyright 1984-2015, Teradata Corporation. ALL RIGHTS RESERVED.

Enter your logon or BTEQ command:

.logon application1

.logon application1

Password:

*** Failure 8017 The UserId, Password or Account is invalid.

*** Error: Logon failed!

The following way we can grant user again to log on and it will update the information in DBC.Logonruletbl table.

grant logon on all to application1;

*** Logon has been granted.