How to use a role in IQ16 like a synonym.
1. Synonym in IQ?
Are there any way to access to an object without knowing the owner of object in IQ16 like a synonym in Oracle?
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
You generally use synonyms when you are granting access to an object from another schema and you don’t want the users to have to worry about knowing which schema owns the object.
2. How to
Here are steps to access to an object without owner name of object.
1) create users and grant a resource role to user01.
– create user user01 identified by sybase;
– create user user02 identified by sybase;
– grant role SYS_AUTH_RESOURCE_ROLE to user01;
). Create two roles.
– CREATE OR REPLACE ROLE FOR USER user01;
– CREATE ROLE USER_TEST_GRP;
3) Grant user01 role to USER_TEST_GRP role.
– grant role user01 to USER_TEST_GRP;
4) Create a table of user01.
– create table user01.test_tb (c1 int);
insert user01.test_tb values(1);
5) Grant the select permission to USER_TEST_GRP role.
– grant select on user01.test_tb to USER_TEST_GRP;
6) Grant the role “USER_TEST_GRP” to user “user02” which wants to access a specific object without owner name.
– grant role USER_TEST_GRP to user02;
7) Connect to IQ as user02 and access the table “user01.test_tb” without owner name.
– select * from test_tb;
3. Change of group/membership
In pre-16.0, groups were a collection of one or more users whose authorities and permissions were determined at the group level.
A user was granted group status, and then other users were granted membership in that group.
In 16.0, the group paradigm is achieved using user-extended roles.
If you have a user with a set of privileges that you want to grant to other users,
you extend the user to become a user-extended role, and then grant that role to other users.
How to do this for IQ 15.4?
You can do using group/membership in IQ15.4.
Here are example.
1) User & Group Creation
grant connect to jerry identified by jerry;
grant group, resource to jerry;
grant connect to tom identified by tom;
grant group to tom;
grant membership in group jerry to tom;
grant connect to apple identified by apple;
grant membership in group tom to apple;
2) grant select to user (Connect to IQ with jerry )
create table jerry.jerry_tbl(a int, b int) ;
grant select on jerry_tbl to tom;
3) try to connect with apple.
- query test
Select * from jerry_tbl ;