Need to access table from other user

[Former Member]

Hello Team,

I have one requirement as below.

• I have one user who have Admin rights called DBA user.
• I have one more read only user called ReadOnly

Now, I want to access table created by DBA user via ReadOnly User but I don't want to use syntax like select * from dba.<table_name>

I actually want to use it like select * from <table_name>

We are using Sql Anywhere 16.

When I did R & D found that enterprise SQL Anywhere have capability to give alias to user.

I am not sure what is the ultimate way to achieve above solution.

Thanks in advance.

[chris_keating]

In Version 12 and earlier, you would convert the user DBA to a group and grant membership to the DBA group to user readonly. In v16 and later, the process is like this (a):

CREATE ROLE FOR USER "DBA";
GRANT ROLE "DBA" TO "readonly";
CREATE OR REPLACE ROLE FOR USER "DBA" WITH ADMIN ONLY "SYS_MANAGE_ROLES_ROLE";
GRANT ROLE "SYS_AUTH_DBA_ROLE" TO "DBA" WITH ADMIN OPTION WITH NO SYSTEM PRIVILEGE INHERITANCE;
GRANT ROLE "SYS_AUTH_RESOURCE_ROLE" TO "DBA" WITH NO SYSTEM PRIVILEGE INHERITANCE;

** (a) This was extracted from a v12 database configured as noted above

[VolkerBarth]

A user U1 can only access a database object owned by another user U2 without qualifying the owner explicitly if user U2 is a group of which U1 is a (direct or indirect) member. Confine the following v16 doc topic:

Groups

Note that with v16 and above, groups are built via "user-defined roles". For v12 and below, the world of permissions was simpler, and a group was just a group.:)

In your case, it might be most appropriate to turn the "Admin" user into an user-extended role and grant that role to the "ReadOnly" user.

[Breck_Carter]

> For v12 and below, the world of permissions was simpler, and a group was just a group.:)

ha ha ha

[VolkerBarth]

Probably the same kind of fun as your current "It's always funny..." quote talks of.

[Breck_Carter]

Yeah, that kind.

"Kids today don't know anything about The Watcom Rule!"