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.6k1647
accept rate: 27%

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
30.0k294448654
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: 347 times

last updated: 31 Oct '15, 16:12