The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 30 Oct '15, 06:52

Aanal%20Patel's gravatar image

Aanal Patel
16224
accept rate: 0%


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

permanent link

answered 31 Oct '15, 08:17

Chris%20Keating's gravatar image

Chris Keating
2.4k1444
accept rate: 22%

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.

permanent link

answered 30 Oct '15, 07:09

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

edited 30 Oct '15, 07:14

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

ha ha ha

(30 Oct '15, 07:39) Breck Carter
Replies hidden

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

(30 Oct '15, 07:58) Volker Barth

Yeah, that kind.

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

(31 Oct '15, 16:08) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17

question asked: 30 Oct '15, 06:52

question was seen: 301 times

last updated: 31 Oct '15, 16:12