• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • SQL Anywhere 16+ Default DB Owner for alternate user

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Anywhere 16+ Default DB Owner for alternate user

  1. How-to
0
Votes
Undo
  1. Jeff Wayt
    Jeff Wayt
  2. PowerBuilder
  3. Friday, 23 February 2024 17:38 PM UTC

If App_user is created to connect to SQL Anywhere that is other than DBA, granted Select on the tables, how can the table owner default to DBA without explicitly using DBA.my_table? We don't want to use the DBA account for the application.

I've tried creating a GROUP, GRANTing it DBA, then granting App_GROUP to App_user. I've tried setting the PowerBuilder connection profile with table criteria owner to DBA. I tried the parm SQLCA.DBParm="SQLQualifiers=1".

There are a lot of datawindows and embedded SQL statements developed under the DBA account, so they are missing the DBA owner.

Any suggestions?

  1. ODBC
  2. SQL Anywhere
Connecting PB2022R3 to GitLab
Custom Colors, Where stored in the Registry?
Responses (3)
Ronnie Po
Ronnie Po Accepted Answer Pending Moderation
  1. Friday, 23 February 2024 17:49 PM UTC
  2. PowerBuilder
  3. # 1

Hey Jeff, how about:

(while connected as DBA)

GRANT membership in App_Group to App_user;

GRANT SELECT, INSERT, UPDATE, DELETE on my_table to App_Group;

As long as App_user does not also own a table named my_table (i.e. as long as App_user.my_table does not exist), then the above should allow App_user to reference my_table without the DBA prefix.

 

Comment
There are no comments made yet.
Tobias Roth
Tobias Roth Accepted Answer Pending Moderation
  1. Friday, 23 February 2024 20:03 PM UTC
  2. PowerBuilder
  3. # 2

Hi Jeff,

We had exactly the same problem in our DB design.
Initially, all tables were created under the owner "admin" with the login user "admin" (DBA).
The main problem was that all DW SQLs were generated without an owner in the past, because the owner was also the logged-in user.

Our solution was as follows:

  • Development environments:
    • The user "admin" is a user-extended role with password and is used as a development user inside the IDE so that PowerBuilder has no problems with old DWs where the owner has not been inserted in the SQL.
  • Productive environments:
    • The user "admin" no longer has a password and is only a user-extended role.
    • Every other user is assigned the user-extended role: grant role "admin" to "username1";
      • This means that it is no longer necessary to specify the owner.

This was the solution for us when we introduced roles and privileges under SQL Anywhere 17, I hope this also helps you with SQL Anywhere 16.

Regards Tobi

 

 
Comment
There are no comments made yet.
Jeff Wayt
Jeff Wayt Accepted Answer Pending Moderation
  1. Friday, 23 February 2024 20:10 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Ronnie!

Thanks for the reply. I had already granted select for all the tables to APP_GROUP. Also: GRANT DBA TO APP_GROUP;

GRANT membership in group APP_GROUP to App_user;

App_user is already a member of APP_GROUP.

[Connect as App_user]

SELECT * FROM my_table;

Table 'my_table' not found.

SELECT * FROM DBA.my_table;

 1 row.


This is my problem: How to implictly say DBA owner for APP_GROUP. I figured SQLCA.DBParm="SQLQualifiers=1" would do it.

Comment
Tobias Roth
  1. Tobias Roth
  2. Friday, 23 February 2024 20:23 PM UTC
This seems to me to be exactly the same problem that happened to us and that I described in my last post :)
  1. Helpful
Ronnie Po
  1. Ronnie Po
  2. Friday, 23 February 2024 21:30 PM UTC
Interesting. I've never had to do any special handling (other than granting permissions) when working with SQL Anywhere databases where all tables and procedures were owned by DBA (or any other particular user).



For what it's worth, below is the entire [SQL Anywhere] section of my pbodb.ini file:



[SQL Anywhere]

PBSyntax='WATCOM50_SYNTAX'

PBDateTime='STANDARD_DATETIME'

PBFunctions='ASA_FUNCTIONS'

PBDefaultValues='autoincrement,current date,current publisher,current time,current timestamp,current user,last user,sqlcode,sqlstate,timestamp,null,user'

PBDefaultCreate='YES'

PBDefaultAlter='YES'

PBDefaultExpressions='YES'

DelimitIdentifier='YES'

PBDateTimeInvalidInSearch='NO'

PBTimeInvalidInSearch='YES'

PBQualifierIsOwner='NO'

PBSpecialDataTypes='WATCOM_SPECIALDATATYPES'

IdentifierQuoteChar='"'

PBSystemOwner='sys,sa_debug,rs_systabgroup'

PBUseProcOwner='YES'

SQLSrvrTSName='YES'

SQLSrvrTSQuote='YES'

SQLSrvrTSDelimit='YES'

ForeignKeyDeleteRule='Disallow if Dependent Rows Exist (RESTRICT),Delete any Dependent Rows (CASCADE),Set Dependent Columns to NULL (SET NULL)'

TableListType='GLOBAL TEMPORARY'

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.